0

After migration from Ignite 2.7.6 to Ignite 2.13 (same is on v2.14) I noticed that the query below executes very slowly. A big difference in execution time can be seen already in tables with several thousands of records. If table have more than 100,000 records, the query will never finish.

All logs and DDL file with execution plan can be found on https://issues.apache.org/jira/browse/IGNITE-17900

select T0.* , T1.HIDE
from TABLE1 as T0
left JOIN
( select key1, key2, count AS HIDE  
    from TABLE1
    GROUP BY key1, key2
) as T1
ON T0.key1 = T1.key1 AND T0.key2 = T1.key2;

Ignite v2.13.0 and v2.14.0
execution time  8 seconds with 2100 records
execution time 22 seconds with 4400 records
 

Ignite v 2.7.6 
execution time  3ms with 2100 records
execution time  4ms with 4400 records

I tried adding indexes to the key1 and key2 columns, but the result is always the same.

I did the first testing with H2 SQL engine. With Calcite SQL engine on Ignite 2.14.0 different SQL plan is applied.

Ignite v 2.14.0 with Calcite SQL engine
execution time  55 ms with 4700 records
Olaf Kock
  • 46,930
  • 8
  • 59
  • 90
Dren Butković
  • 21
  • 2
  • Looks like a regression. Thanks for the ticket. Can you please also clarify which SQL engine you are using H2 vs Calcite? Have your tried to use Calcite engine? https://ignite.apache.org/docs/latest/SQL/sql-calcite – solveMe Oct 18 '22 at 15:23
  • I did the first testing with H2 SQL engine. Now I tried to test with Calcite SQL engine on Ignite 2.14.0 and it can be seen that a different SQL plan is applied. Execution speed is now much faster. The query is executed for 4000 records in about 55 ms. – Dren Butković Oct 19 '22 at 07:26

0 Answers0