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-issue-importer opened this issue Jun 12, 2018 · 6 comments
Closed

Comments

@firebird-issue-importer

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

Modified by: @dyemanov

Link: This issue relate to CORE5965 [ CORE5965 ]

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Nov 20, 2018

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-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 9, 2018

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 9, 2018

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

@firebird-issue-importer firebird-issue-importer commented Dec 31, 2019

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
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

None yet
2 participants