Apache Ignite SQL query performance degrades when indexes columns overlap H2 cache

Question

I have a cache (ITEMS) in Ignite which has 10 columns, and millions of records in it.

Key is the ID(have 3 fields of 10 cos) and value is the Java object having these 10 fields which are the columns.

columns: col0, col1, col2... col9 -> col0 is the set of the records in the cache.

for e.g. if the cache has 10 million records then col0 has 10 unique values(DS1, DS2, DS3..DS10) and each of them has 1 million records

I have 3 indexes on the cache:

1. INDEX_1. ID
2. INDEX_2. col0,col1,col2,col3
3. INDEX_3. col0,col1,col2,col3,col4 (it has overlapping columns wih 2 + 1 extra column)

I have a query which looks like: SELECT ID from ITEMS I USE INDEX(INDEX_3) WHERE col0=? and col1>? and col2<0 and col3>? and col4=0 order by col0, col3 ASC LIMIT 10000;

The issue is if I keep all the 3 indexes, the query takes longer time to execute. ~3-4 seconds

-> If I just remove INDEX_1 I get a good query performance ~200ms (cannot remove INDEX_1 as this is the primary index and used in other queries)

-> Also If I just remove INDEX_2 I get a good query performance ~200ms ( INDEX_2 is used in other query flow and cannot remove as well)

Unable to understand this behavior of the H2 cache as I was under impression that having more indexes affects the write performance of the cache, how it is affecting the read performance? can anyone help me understand this? or guild me to set up an appropriate cache config. Thank you in advance.

Answer 1

I don't know why you're seeing that behaviour, but I can say that you don't need INDEX_2. Ignite uses "leading edge" indexes, which means a lookup for columns 0, 1, 2, and 3 can use INDEX_3.