Better plan/execution possible?

[Attila Molnár]

Hi *!

CREATE TABLE T1 (
    ID     INTEGER NOT NULL,
    ROWNO  INTEGER NOT NULL
);
ALTER TABLE T1 ADD CONSTRAINT T1_PK PRIMARY KEY (ID);
CREATE INDEX T1_I1 ON T1 (ROWNO);

CREATE TABLE T2 (
    ID     INTEGER NOT NULL,
    ID_T1  INTEGER NOT NULL,
    ROWNO  INTEGER NOT NULL
);
ALTER TABLE T2 ADD CONSTRAINT T2_PK PRIMARY KEY (ID);
ALTER TABLE T2 ADD CONSTRAINT T2_FK1 FOREIGN KEY (ID_T1) REFERENCES T1 (ID);
CREATE INDEX T2_I1 ON T2 (ROWNO);

SELECT *
FROM t1
    LEFT OUTER JOIN t2 ON t2.id_t1 = t1.id
ORDER BY t1.rowno, t2.rowno

The plan is PLAN SORT (JOIN (T1 NATURAL, T2 INDEX (T2_FK1))) in Firebird 3.x, 4.x, 5.x.

Why does Firebird not use T1_I1? The SORT could perform better when the result is already partly ordered and not completly random.

It is this possible for the engine to use T11_I1 in this case?  Should I create an issue in Github fot this?

Thank You!

[Dimitry Sibiryakov]

Attila Molnár wrote 03.09.2025 14:29:
> Why does Firebird not use T1_I1?

Because you requested order by two fields while index cover only one.

> The SORT could perform better when the result
> is already partly ordered and not completly random.

On contrary: for quick sort algorithm it is known that partially sorted input
is the worst case, according to Knuth.

> It is this possible for the engine to use T11_I1 in this case?

Very theoretically engine can split partially sorted input stream to
partitions and sort them independently but I wouldn't expect anybody to be able
to implement that.

> Should I create an issue in Github fot this?

A Pull Request would be better. Otherwise it will be an another ticket that
is never closed.

--
WBR, SD.