[
https://issues.apache.org/jira/browse/IGNITE-13316?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17169732#comment-17169732
]
Xie Bo commented on IGNITE-13316:
---------------------------------
Hi [~ilyak],
Thanks very much for your advice!
I have added an index 'multi_fixed_index' {color:#172b4d}on issue_fixed_version
(id, fixed_version){color}, but the performance is still not good for SQL
(SELECT * FROM issue LEFT JOIN issue_fixed_version ver ON issue.id=ver.id WHERE
ver.FIXED_VERSION = '4.15').
Below is the SQL's explain plan. The new created {color:#172b4d}index
'multi_fixed_index'{color} is used. Ignite still scans the issue table and does
not convert the left join to inner join to avoid the issue table scan. *Can
Ignite optimize left join to inner join* ({color:#172b4d}if the
{color}{{WHERE}}{color:#172b4d} condition is always false for the generated
{color}{{NULL}}{color:#172b4d} row{color}) in order to improve SQL query
performance?
-------
"SELECT
ISSUE__Z0.ASSIGNED_DATE AS __C0_0,
...
ISSUE__Z0.ID AS __C0_14,
...
VER__Z1.ID AS __C0_67,
VER__Z1._IDX AS __C0_68,
VER__Z1.FIXED_VERSION AS __C0_69
FROM PUBLIC.ISSUE ISSUE__Z0
/* PUBLIC.ISSUE.*__SCAN_* */
LEFT OUTER JOIN PUBLIC.ISSUE_FIXED_VERSION VER__Z1
/* batched:unicast PUBLIC.*MULTI_FIXED_INDEX*: ID = ISSUE__Z0.ID */
ON ISSUE__Z0.ID = VER__Z1.ID
WHERE VER__Z1.FIXED_VERSION = '4.15'"
-------
> The left join table's index is not used in Ignite SQL execution
> ---------------------------------------------------------------
>
> Key: IGNITE-13316
> URL: https://issues.apache.org/jira/browse/IGNITE-13316
> Project: Ignite
> Issue Type: Improvement
> Components: sql
> Affects Versions: 2.8.1
> Reporter: Xie Bo
> Priority: Major
>
>
> SELECT * FROM issue LEFT JOIN issue_fixed_version ver ON issue.id=ver.id
> WHERE *issue.id in (select distinct id from issue_fixed_version where
> fixed_version = '4.15')*;
> 12 rows selected (*0.062 seconds*)
> ________________________________________
> SELECT * FROM issue LEFT JOIN issue_fixed_version ver ON issue.id=ver.id
> WHERE *ver.FIXED_VERSION = '4.15'*;
> 12 rows selected (*2.015 second*)
> ________________________________________
> Note: 'issue' is a table with over a million rows and 'issue_fixed_version'
> is another table with much less rows. There is an index on 'FIXED_VERSION'
> column in 'issue_fixed_version' table.
> The above 2 SQLs do the same thing but have different performance. The first
> SQL's explain plan shows that the index (on 'FIXED_VERSION' column in
> 'issue_fixed_version' table) is used and then its performance is good; the
> second SQL's explain plan shows that the index (on 'FIXED_VERSION' column in
> 'issue_fixed_version' table) is not used, why? Is there any configuration
> option to force the second SQL to use the index (on 'FIXED_VERSION' column in
> 'issue_fixed_version' table)?
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
