Choose the best matching index for navigation [CORE4285] #4608
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 Alpha 2 [ 10560 ] |
Modified by: @dyemanovdescription: 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); select 1 from test order by col1; select 1 from test where col1 = 0 order by col1; select 1 from test order by col1, col2; select 1 from test where col1 = 0 order by col1, col2; select 1 from test where col1 = 0 and col2 = 0 order by col1, col2; select 1 from test order by col1, col2, col3; select 1 from test where col1 = 0 order by col1, col2, col3; select 1 from test where col1 = 0 and col2 = 0 order by col1, col2, col3; select 1 from test where col1 = 0 and col2 = 0 order by col1; select 1 from test where col1 = 0 and col2 = 0 and col3 = 0 order by col1; select 1 from test where col1 = 0 and col3 = 0 order by col1; select 1 from test where col1 = 0 and col3 = 0 order by col1, col2; select 1 from test where col1 = 0 and col3 = 0 order by col1, col2, col3; select 1 from test where col1 = 0 and col3 = 0 order by col1, col3; => 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); select 1 from test order by col1; select 1 from test where col1 = 0 order by col1; select 1 from test order by col1, col2; select 1 from test where col1 = 0 order by col1, col2; select 1 from test where col1 = 0 and col2 = 0 order by col1, col2; select 1 from test order by col1, col2, col3; select 1 from test where col1 = 0 order by col1, col2, col3; select 1 from test where col1 = 0 and col2 = 0 order by col1, col2, col3; select 1 from test where col1 = 0 and col2 = 0 order by col1; select 1 from test where col1 = 0 and col2 = 0 and col3 = 0 order by col1; select 1 from test where col1 = 0 and col3 = 0 order by col1; select 1 from test where col1 = 0 and col3 = 0 order by col1, col2; select 1 from test where col1 = 0 and col3 = 0 order by col1, col2, col3; select 1 from test where col1 = 0 and col3 = 0 order by col1, col3; |
Modified by: @pmakowski |
Modified by: @pmakowski |
Modified by: @pmakowski |
Modified by: @pmakowskistatus: Resolved [ 5 ] => Closed [ 6 ] |
Submitted by: @dyemanov
Relate to QA466
Votes: 1
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)
-- NOTE: PLAN (TEST ORDER ITESTCOL21) becomes also possible after resolving CORE1846
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)
-- NOTE: PLAN (TEST ORDER ITESTCOL132) becomes also possible after resolving CORE1846
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)
Commits: 74fbaf5 FirebirdSQL/fbt-repository@77c08a3 FirebirdSQL/fbt-repository@b2d0198
The text was updated successfully, but these errors were encountered: