Performance of SQL query by partial primary key

[Ray <ra...@cisco.com>]

I have some trouble understanding how Ignite maintain primary key.
This document
https://apacheignite.readme.io/docs/memory-architecture#section-b-trees-and-index-pages
says "The cache keys are also stored in B+ trees and are ordered by their
hash code values."

So if I create a table using this command, create table t(a varchar, b
varchar, c varchar, primary key(a,b));
The primary key for this table is a custom object consists of a and b.

So my question is when I try to query this table using this sql "select c
from t where a= 'some value' " , will there be a performance issue when
table t contains billions of records?


Quote from
"https://apacheignite.readme.io/docs/memory-architecture#section-b-trees-and-index-pages"

"For instance, when myCache.get(keyA) operation is executed, it will trigger
the following execution flow:

1. Ignite will look for a memory region to which myCache belongs to.
2. The meta page pointing to the hash index B+ tree of myCache will be
located.
3. Based on the keyA hash code, the index page the key belongs to will be
located in the B+ tree.
4. If the corresponding index page is not found in the memory or on disk,
then Ignite concludes that the key does not exist and will return null.
5. If the index page exists, then it will contain all the information needed
to find the data page of the cache entry keyA refers to.
6. Ignite will locate the data page for keyA and will return the value to
the user."


When I use the above query to get records, the where statement only contains
partial primary key.
So there's no way for calculate the key hash code in step 3, right?
I wonder how Ignite handles this case, does Ignite perform a whole table
scan?




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Performance of SQL query by partial primary key

[Юрий <ju...@gmail.com>]

Hi Ray,

You are right, currently complex PK can't be used as index and performance
for your query are bad due to full scan.
After IGNITE-8386 <https://issues.apache.org/jira/browse/IGNITE-8386> will
be merged the same query start use PK index.

вт, 18 сент. 2018 г. в 8:16, Ray <ra...@cisco.com>:

> To answer my own question here, basically the index created on PK now is
> useless according to this ticket.
> https://issues.apache.org/jira/browse/IGNITE-8386
>
> Ignite will perform a whole table scan when trying to execute the SQL query
> I posted above unless an index identical to PK is created manually.
>
> Please correct me if understand it wrong.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>


-- 
Живи с улыбкой! :D

Re: Performance of SQL query by partial primary key

[Ray <ra...@cisco.com>]

To answer my own question here, basically the index created on PK now is
useless according to this ticket.
https://issues.apache.org/jira/browse/IGNITE-8386

Ignite will perform a whole table scan when trying to execute the SQL query
I posted above unless an index identical to PK is created manually.

Please correct me if understand it wrong.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/