[jira] [Commented] (IGNITE-17900) Very slow SQL execution with LEFT JOIN and subquery after upgrade from 2.7.6

["Dren (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/IGNITE-17900?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17620060#comment-17620060 ] 

Dren commented on IGNITE-17900:
-------------------------------

I did the first testing with H2 SQL engine.
With Calcite SQL engine on Ignite 2.14.0  different SQL plan is applied. 
Execution speed is much faster and same query is executed in 55 ms on table TABLE1 with 4700 records.

> Very slow SQL execution with LEFT JOIN and subquery after upgrade from 2.7.6
> ----------------------------------------------------------------------------
>
>                 Key: IGNITE-17900
>                 URL: https://issues.apache.org/jira/browse/IGNITE-17900
>             Project: Ignite
>          Issue Type: Bug
>          Components: sql
>    Affects Versions: 2.13, 2.14
>         Environment: One node test instalation, 6 vCPU 8GB RAM.
> Ignite 2.14.0
> jdk-13.0.2
>  
>  
>            Reporter: Dren
>            Priority: Major
>         Attachments: CREATE_TABLE1.sql, explain_plan.txt, ignite_log.txt, insert_data.sql
>
>
> After migration from Ignite 2.7.6 to Ignite 2.13 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.
> {code:java}
> // SQL
> 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; {code}
>  
> – 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
>  
> All DDL and test data can be found in attachment.
> I tried adding indexes to the key1 and key2 columns, but the result is always the same.



--
This message was sent by Atlassian Jira
(v8.20.10#820010)