[jira] [Created] (IGNITE-13316) The left join table's index is not used in Ignite SQL execution

[Anton Vinogradov (Jira)]

Xie Bo created IGNITE-13316:
-------------------------------

             Summary: 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: springdata
    Affects Versions: 2.8.1
            Reporter: Xie Bo


 

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*s)

________________________________________

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)?



--
This message was sent by Atlassian Jira
(v8.3.4#803005)