Choose the best matching index for navigation
---------------------------------------------
Key: CORE-4285
URL: http://tracker.firebirdsql.org/browse/CORE-4285
Project: Firebird Core
Issue Type: Improvement
Components: Engine
Reporter: Dmitry Yemanov
Currently, when ORDER plan is in game, the optimizer chooses the first index
candidate that matches the ORDER BY / GROUP BY clause. This is not the best
approach when multiple index choices are available, see examples below.
create table test (col1 int, col2 int, col3 int);
create index itestcol1 on test (col1);
create index itestcol12 on test (col1, col2);
create index itestcol21 on test (col2, col1);
create index itestcol123 on test (col1, col2, col3);
create index itestcol132 on test (col1, col3, col2);
set planonly;
select 1 from test order by col1;
-- expected: PLAN (TEST ORDER ITESTCOL1)
select 1 from test where col1 = 0 order by col1;
-- expected: PLAN (TEST ORDER ITESTCOL1)
select 1 from test order by col1, col2;
-- expected: PLAN (TEST ORDER ITESTCOL12)
select 1 from test where col1 = 0 order by col1, col2;
-- expected: PLAN (TEST ORDER ITESTCOL12)
select 1 from test where col1 = 0 and col2 = 0 order by col1, col2;
-- expected: PLAN (TEST ORDER ITESTCOL12)
select 1 from test order by col1, col2, col3;
-- expected: PLAN (TEST ORDER ITESTCOL123)
select 1 from test where col1 = 0 order by col1, col2, col3;
-- expected: PLAN (TEST ORDER ITESTCOL123)
select 1 from test where col1 = 0 and col2 = 0 order by col1, col2, col3;
-- expected: PLAN (TEST ORDER ITESTCOL123)
select 1 from test where col1 = 0 and col2 = 0 order by col1;
-- expected: PLAN (TEST ORDER ITESTCOL12)
select 1 from test where col1 = 0 and col2 = 0 and col3 = 0 order by col1;
-- expected: PLAN (TEST ORDER ITESTCOL123) or PLAN (TEST ORDER ITESTCOL132)
select 1 from test where col1 = 0 and col3 = 0 order by col1;
-- expected: PLAN (TEST ORDER ITESTCOL132)
select 1 from test where col1 = 0 and col3 = 0 order by col1, col2;
-- expected: PLAN (TEST ORDER ITESTCOL12)
select 1 from test where col1 = 0 and col3 = 0 order by col1, col2, col3;
-- expected: PLAN (TEST ORDER ITESTCOL123)
select 1 from test where col1 = 0 and col3 = 0 order by col1, col3;
-- expected: PLAN (TEST ORDER ITESTCOL132)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Rapidly troubleshoot problems before they affect your business. Most IT
organizations don't have a clear picture of how application performance
affects their revenue. With AppDynamics, you get 100% visibility into your
Java,.NET, & PHP application. Start your 15-day FREE TRIAL of AppDynamics Pro!
http://pubads.g.doubleclick.net/gampad/clk?id=84349351&iu=/4140/ostg.clktrk
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
