[
https://issues.apache.org/jira/browse/IGNITE-13316?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Xie Bo updated IGNITE-13316:
----------------------------
Description:
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)?
was:
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? Any way to force the second SQL
to use the index (on 'FIXED_VERSION' column in 'issue_fixed_version' table)?
> 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)
