You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@ignite.apache.org by "Dren (Jira)" <ji...@apache.org> on 2022/10/14 06:41:00 UTC
[jira] [Updated] (IGNITE-17900) Very slow SQL execution with LEFT JOIN and subquery after upgrade from 2.7.6
[ https://issues.apache.org/jira/browse/IGNITE-17900?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Dren updated IGNITE-17900:
--------------------------
Description:
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.
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
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.
was:
After migration form 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.
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 seconds 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.
> 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.
> 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
>
> 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)