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

Regression: ORDER BY clause on compound index may disable usage of other indices [CORE5020] #5308

Closed
firebird-automations opened this issue Nov 17, 2015 · 9 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @livius2

composite index order - ORDER BY http://ZF.ID, KONT_ID (IXA_FK__ID__KONT_ID)
cause not using referencing index (FK_ZF__K)

CREATE TABLE ZF
(
ID INTEGER NOT NULL PRIMARY KEY,
KONT_ID INTEGER NOT NULL
);

CREATE TABLE U
(
ID INTEGER NOT NULL PRIMARY KEY,
KONT_ID INTEGER NOT NULL
);

CREATE TABLE K
(
ID INTEGER NOT NULL PRIMARY KEY
);

commit;

INSERT INTO ZF (ID, KONT_ID) VALUES ('1', '1');
INSERT INTO ZF (ID, KONT_ID) VALUES ('2', '7');
INSERT INTO ZF (ID, KONT_ID) VALUES ('3', '3');
INSERT INTO ZF (ID, KONT_ID) VALUES ('4', '5');
INSERT INTO ZF (ID, KONT_ID) VALUES ('5', '5');
INSERT INTO ZF (ID, KONT_ID) VALUES ('6', '1');
INSERT INTO ZF (ID, KONT_ID) VALUES ('7', '4');
INSERT INTO ZF (ID, KONT_ID) VALUES ('8', '2');
INSERT INTO ZF (ID, KONT_ID) VALUES ('9', '9');
INSERT INTO ZF (ID, KONT_ID) VALUES ('10', '1');

INSERT INTO K (ID) VALUES ('1');
INSERT INTO K (ID) VALUES ('2');
INSERT INTO K (ID) VALUES ('3');
INSERT INTO K (ID) VALUES ('4');
INSERT INTO K (ID) VALUES ('5');
INSERT INTO K (ID) VALUES ('6');
INSERT INTO K (ID) VALUES ('7');
INSERT INTO K (ID) VALUES ('8');
INSERT INTO K (ID) VALUES ('9');
INSERT INTO K (ID) VALUES ('10');

INSERT INTO U (ID, KONT_ID) VALUES ('1', '4');
INSERT INTO U (ID, KONT_ID) VALUES ('2', '6');
INSERT INTO U (ID, KONT_ID) VALUES ('3', '3');
INSERT INTO U (ID, KONT_ID) VALUES ('4', '2');
INSERT INTO U (ID, KONT_ID) VALUES ('5', '5');
INSERT INTO U (ID, KONT_ID) VALUES ('6', '2');
INSERT INTO U (ID, KONT_ID) VALUES ('7', '9');
INSERT INTO U (ID, KONT_ID) VALUES ('8', '2');
INSERT INTO U (ID, KONT_ID) VALUES ('9', '10');
INSERT INTO U (ID, KONT_ID) VALUES ('10', '1');

commit;

ALTER TABLE ZF
ADD CONSTRAINT FK_ZF__K FOREIGN KEY(KONT_ID) REFERENCES K(ID) ON UPDATE CASCADE ON DELETE NO ACTION;

CREATE ASCENDING INDEX IXA_FK__ID__KONT_ID ON ZF(ID, KONT_ID);

commit;

SELECT
ZF.*
FROM
ZF
WHERE
ZF.KONT_ID=5
ORDER BY
http://ZF.ID, KONT_ID

---------------------------------------------
WI-V2.5.3.26738 Firebird 2.5

PLAN (ZF ORDER IXA_FK__ID__KONT_ID INDEX (FK_ZF__K))

----------------------------------------------
WI-V3.0.0.32179 Firebird 3.0 Release Candidate 1

PLAN (ZF ORDER IXA_FK__ID__KONT_ID)

Select Expression
-> Filter
-> Table "ZF" Access By ID
-> Index "IXA_FK__ID__KONT_ID" Full Scan

#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠#⁠
if we change this query to

SELECT
ZF.*
FROM
ZF
WHERE
ZF.KONT_ID=5
ORDER BY
http://ZF.ID <-------------- sort only by ID

plan is the same on both FB2.5 and FB3.0 RC1
PLAN (ZF ORDER RDB$PRIMARY5 INDEX (FK_ZF__K))

Select Expression
-> Filter
-> Table "ZF" Access By ID
-> Index "RDB$PRIMARY5" Full Scan
-> Bitmap
-> Index "FK_ZF__K" Range Scan (full match)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Regression - composite index order cause not using referencing index => Regression: ORDER BY clause on compound index may disable usage of other indices

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 RC2 [ 10048 ]

@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

Commented by: @livius2

The problem still exists but for different query
If you add join then index is not used but should be

SELECT
ZF.*
FROM
ZF INNER JOIN K ON K.ID=ZF.KONT_ID
WHERE
ZF.KONT_ID=5
ORDER BY
http://ZF.ID, ZF.KONT_ID

FB3
PLAN SORT (JOIN (K INDEX (RDB$PRIMARY7), ZF INDEX (FK_ZF__K)))

FB2.5
PLAN JOIN (ZF ORDER IXA_FK__ID__KONT_ID_3 INDEX (FK_ZF__K), K INDEX (RDB$PRIMARY7))

but if you change INNER JOIN to LEFT JOIN plan is ok
PLAN JOIN (ZF ORDER IXA_FK__ID__KONT_ID INDEX (FK_ZF__K), K INDEX (RDB$PRIMARY7))

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Please provide execution statistics (seq/idx reads and page fetches) for the join case.

@firebird-automations
Copy link
Collaborator Author

Commented by: @livius2

FB2.5

Current memory = 1111998352
Delta memory = -48
Max memory = 1191697296
Elapsed time= 0.01 sec
Buffers = 65536
Reads = 0
Writes 0
Fetches = 24

FB3.0

Current memory = 1120947168
Delta memory = 352048
Max memory = 1200743640
Elapsed time= 0.012 sec
Buffers = 65536
Reads = 0
Writes = 0
Fetches = 436

for real query time is dramatically different

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

With data from this ticket, I see:

PLAN SORT (JOIN (K INDEX (RDB$PRIMARY6), ZF INDEX (FK_ZF__K)))
Elapsed time= 0.001 sec
Fetches = 28

PLAN JOIN (ZF ORDER IXA_FK__ID__KONT_ID INDEX (FK_ZF__K), K INDEX (RDB$PRIMARY6))
Elapsed time= 0.001 sec
Fetches = 44

So it looks the engine chooses the better plan. I'm afraid a more realistic test case is needed.

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

No branches or pull requests

2 participants