ptupitsyn
commented
3 months ago IN expression is known to be not very efficient in Ignite.
The workaround is to use a temporary table join:
SELECT f1, f2, f3 FROM table_x
JOIN table(f1 bigint = (?)) temp_table on table_x.f1 = temp_table.f1
WHERE f2 = 1 AND ydate = 20230616https://stackoverflow.com/questions/77601695/how-to-query-using-in-operator-in-ignite-cache
this is query very slow,aboult 1s： SELECT f1,f2,f3 FROM table_x WHERE f1IN (107,108) AND f2= 1 AND ydate = 20230616
this is query very fast,about 20ms： SELECT f1,f2,f3 FROM table_x WHERE f1=107 AND f2= 1 AND ydate = 20230616 UNION ALL SELECT f1,f2,f3 FROM table_x WHERE f1=108 AND f2 = 1 AND ydate = 20230616
this is why?
CREATE INDEX table_x_IDX ON PUBLIC.table_x (f1,f2 DESC,f3,f4);