Issue Details (XML | Word | Printable)

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

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

Regression: ORDER BY clause on compound index may disable usage of other indices

Created: 17/Nov/15 09:58 AM   Updated: 19/Nov/15 01:40 PM
Component/s: Engine
Affects Version/s: 3.0 RC1
Fix Version/s: 3.0 RC2

QA Status: Done successfully


 Description  « Hide
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)



 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Karol Bieniaszewski added a comment - 19/Nov/15 11:09 AM
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
 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))

Dmitry Yemanov added a comment - 19/Nov/15 12:19 PM
Please provide execution statistics (seq/idx reads and page fetches) for the join case.

Karol Bieniaszewski added a comment - 19/Nov/15 01:15 PM
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

Dmitry Yemanov added a comment - 19/Nov/15 01:40 PM
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.