Very slow ordered plan retrieval vs sort

[livius2]

Below script for database:

CREATE TABLE HDR
(ID BIGINT NOT NULL PRIMARY KEY
, HDR_NAME VARCHAR(30)
);

CREATE GENERATOR GEN_HDR__ID;

CREATE  TABLE POZ1
(ID BIGINT NOT NULL PRIMARY KEY
, ID_HDR BIGINT NOT NULL
, POZ_NAME VARCHAR(30)
, DT TIMESTAMP
);

ALTER TABLE POZ1 ADD CONSTRAINT FK_POZ1__HDR FOREIGN KEY(ID_HDR) REFERENCES HDR(ID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE GENERATOR GEN_POZ1__ID;

CREATE  TABLE POZ2
(ID BIGINT NOT NULL PRIMARY KEY
, ID_HDR BIGINT NOT NULL
, POZ_NAME VARCHAR(30)
, DT TIMESTAMP
);

ALTER TABLE POZ2 ADD CONSTRAINT FK_POZ2__HDR FOREIGN KEY(ID_HDR) REFERENCES HDR(ID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE GENERATOR GEN_POZ2__ID;

SET TERM ^ ;
CREATE PROCEDURE FILL_DATA
AS
DECLARE VARIABLE H INTEGER;
DECLARE VARIABLE P INTEGER;
DECLARE VARIABLE HDR_ID INTEGER;
DECLARE VARIABLE P_MAX INTEGER;
DECLARE VARIABLE P_CURR INTEGER;
BEGIN
  H = 1;
  P_MAX = 7;
  P_CURR = 0;
  
  WHILE (H<10000) DO
    BEGIN
        HDR_ID = GEN_ID(GEN_HDR__ID, 1);
        INSERT INTO HDR(ID, HDR_NAME) VALUES(:HDR_ID, 'HDR_' || :HDR_ID);
        P = 1;
      
        WHILE (P<P_CURR) DO
            BEGIN
                INSERT INTO POZ1(ID, ID_HDR, DT) VALUES(GEN_ID(GEN_POZ1__ID, 1), :HDR_ID, DATEADD(:P MINUTE TO LOCALTIMESTAMP));
                INSERT INTO POZ2(ID, ID_HDR, DT) VALUES(GEN_ID(GEN_POZ2__ID, 1), :HDR_ID, DATEADD(-:P MINUTE TO LOCALTIMESTAMP));
                
                P = P + 1;
            END
        
        H = H + 1;
        P_CURR = P_CURR + 1;
        
        IF (P_CURR > P_MAX) THEN
            P_CURR = 0;
    END
  
END^
SET TERM ; ^

commit;

EXECUTE PROCEDURE FILL_DATA;

commit;

CREATE DESCENDING INDEX IXD_POZ1__DT ON POZ1(DT);
CREATE ASCENDING INDEX IXA_POZ2__DT ON POZ2(DT);

run this query

SELECT
H.*
, (SELECT FIRST 1 P.ID FROM  POZ1 P WHERE P.ID_HDR=H.ID ORDER BY P.DT DESC)
FROM
HDR H

PLAN (P ORDER IXD_POZ1__DT INDEX (FK_POZ1__HDR))
time 2044 ms

same for this:

SELECT
H.*
, (SELECT FIRST 1 P.ID FROM  POZ2 P WHERE P.ID_HDR=H.ID ORDER BY P.DT ASC)
FROM
HDR H

PLAN (P ORDER IXA_POZ2__DT INDEX (FK_POZ2__HDR))
time 2067 ms

now remove index retrival and change it to sort by +0 trick

SELECT
H.*
, (SELECT FIRST 1 P.ID FROM  POZ1 P WHERE P.ID_HDR=H.ID ORDER BY P.DT+0 DESC)
FROM
HDR H

PLAN SORT (P INDEX (FK_POZ1__HDR))
time 148 ms

so we have 13 times slower!

[livius2]

With lateral join under FB5 same problem with order by index vs sort:

SELECT
H.*
, P.ID
FROM
HDR H
 LEFT JOIN LATERAL (SELECT FIRST 1 P.ID FROM  POZ2 P WHERE P.ID_HDR=H.ID ORDER BY P.DT ASC) P ON TRUE

PLAN JOIN (H NATURAL, P P ORDER IXA_POZ2__DT INDEX (FK_POZ2__HDR))
time 2002 ms

SELECT
H.*
, P.ID
FROM
HDR H
 LEFT JOIN LATERAL (SELECT FIRST 1 P.ID FROM  POZ2 P WHERE P.ID_HDR=H.ID ORDER BY P.DT+0 ASC) P ON TRUE

PLAN JOIN (H NATURAL, SORT (P P INDEX (FK_POZ2__HDR)))

time longer then subquery but still much faster 367 ms

[dyemanov]

This is a known issue, plans of type ORDER ... INDEX are often bad from the performance POV. And this has been already addressed in v6.0.

Moreover, your example is not very accurate, because indices FK_POZ1__HDR / FK_POZ2__HDR are created before the table is populated and thus have zero selectivity. If I add SET STATISTICS for them to the end of the script, I see the following plan in v6.0 (without +0 hints):

PLAN SORT (P INDEX (FK_POZ1__HDR))