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
Comments
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovsummary: Regression - composite index order cause not using referencing index => Regression: ORDER BY clause on compound index may disable usage of other indices |
Modified by: @dyemanovstatus: Open [ 1 ] => Resolved [ 5 ] resolution: Fixed [ 1 ] Fix Version: 3.0 RC2 [ 10048 ] |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Resolved [ 5 ] QA Status: No test => Done successfully |
Modified by: @pavel-zotovstatus: Resolved [ 5 ] => Closed [ 6 ] |
Commented by: @livius2 The problem still exists but for different query SELECT FB3 FB2.5 but if you change INNER JOIN to LEFT JOIN plan is ok |
Commented by: @dyemanov Please provide execution statistics (seq/idx reads and page fetches) for the join case. |
Commented by: @livius2 FB2.5 Current memory = 1111998352 FB3.0 Current memory = 1120947168 for real query time is dramatically different |
Commented by: @dyemanov With data from this ticket, I see: PLAN SORT (JOIN (K INDEX (RDB$PRIMARY6), ZF INDEX (FK_ZF__K))) PLAN JOIN (ZF ORDER IXA_FK__ID__KONT_ID INDEX (FK_ZF__K), K INDEX (RDB$PRIMARY6)) So it looks the engine chooses the better plan. I'm afraid a more realistic test case is needed. |
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)
The text was updated successfully, but these errors were encountered: