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)