Issue Details (XML | Word | Printable)

Key: CORE-5845
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Karol Bieniaszewski
Votes: 0
Watchers: 3
Operations

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

ORDER BY on index can cause suboptimal index choices

Created: 12/Jun/18 06:56 AM   Updated: 09/Dec/18 02:09 PM
Component/s: Engine
Affects Version/s: 3.0.3, 3.0.4
Fix Version/s: 4.0 Beta 1, 3.0.5

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
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 CORE-5795 but this can be different case


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