Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

ORDER BY on index can cause suboptimal index choices [CORE5845] #6106

Closed
firebird-automations opened this issue Jun 12, 2018 · 6 comments
Closed

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

Relate to CORE5965
Duplicates CORE5795

CREATE TABLE TEST
(
ID1 INTEGER,
ID2 INTEGER,
ID3 INTEGER,
X NUMERIC(18,2),
CONSTRAINT PK_TEST PRIMARY KEY(ID1, ID2, ID3)
);

CREATE INDEX IXA_TEST__X ON TEST(X);
CREATE INDEX IXA_TEST__ID1_X ON TEST(ID1, X);

--------------------------------------------------------------------------------

SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0

PLAN (T INDEX (IXA_TEST__ID1_X))

index IXA_TEST__ID1_X is used
--------------------------------------------------------------------------------

SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1, T.ID2, T.ID3

PLAN (T ORDER PK_TEST INDEX (IXA_TEST__X))

index IXA_TEST__X - suboptimal
--------------------------------------------------------------------------------

as you can see adding ORDER BY which consume some index (PK_TEST)
cause suboptimal choice of index (IXA_TEST__X)

--------------------------------------------------------------------------------

if query is changed to order by not by index
SELECT * FROM TEST T WHERE T.ID1=1 AND T.X>0 ORDER BY T.ID1+0, T.ID2, T.ID3

PLAN SORT (T INDEX (IXA_TEST__ID1_X))

It can be releated to CORE5795 but this can be different case

Commits: 077a2a3 3103f72

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5965 [ CORE5965 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 1 [ 10750 ]

Fix Version: 3.0.5 [ 10885 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: No test => Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue duplicates CORE5795 [ CORE5795 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment