[jira] [Updated] (IGNITE-13316) The left join table's index is not used in Ignite SQL execution
[ 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)
[jira] [Updated] (IGNITE-13316) The left join table's index is not used in Ignite SQL execution
[ 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? Any way 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*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)? > 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? 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)
[jira] [Updated] (IGNITE-13316) The left join table's index is not used in Ignite SQL execution
[ https://issues.apache.org/jira/browse/IGNITE-13316?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Xie Bo updated IGNITE-13316: Component/s: (was: springdata) sql > 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*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)
