Issue Details (XML | Word | Printable)

Key: CORE-4285
Type: Improvement Improvement
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Dmitry Yemanov
Votes: 1
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Choose the best matching index for navigation

Created: 28/Nov/13 04:08 AM   Updated: 10/Jul/15 01:20 PM
Component/s: Engine
Affects Version/s: None
Fix Version/s: 3.0 Alpha 2

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
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 CORE-1846

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 CORE-1846

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)

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.