Regression - composite index order cause not using referencing index
--------------------------------------------------------------------
Key: CORE-5020
URL: http://tracker.firebirdsql.org/browse/CORE-5020
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0 RC 1
Reporter: Karol Bieniaszewski
composite index order - ORDER BY 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
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
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)
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
