Firebird 3 apparently stubbornly uses an index corresponding to the WHERE clause, even though the same value is contained in all rows (index is not selective at all), and ignores a compound index that would exactly match the ORDER BY clause. The query of a large table thus takes about 1 minute until we can read the first record, because Firebird first sorts the table without the help of an index and then filters it with the help of a non-selective index. Tests have shown that specifying the FIRST clause (even with the specification of 1 billion data records!) leads to lightning fast results, because it uses the ideal index.
Up to Firebird 2.5.9 this always worked ideally even without the FIRST clause.
Test without FIRST clause:
SELECT * FROM GADR WHERE ID_TENANT=1 AND Status<>2 ORDER BY ID_TENANT,Suchbeg,ID;
Select Expression
-> Sort (record length: 14196, key length: 328)
-> Filter
-> Table "GADR" Access By ID
-> Bitmap
-> Index "FK_GADR02" Range Scan (full match)
show index FK_GADR02;
FK_GADR02 INDEX ON GADR(ID_TENANT)
SELECT RDB$STATISTICS FROM RDB$INDICES WHERE RDB$INDEX_NAME='FK_GADR02' --> 1.0
Test with FIRST clause:
SELECT FIRST 1000000000 * FROM GADR WHERE ID_TENANT=1 AND Status<>2 ORDER BY ID_TENANT,Suchbeg,ID;
Select Expression
-> First N Records
-> Filter
-> Table "GADR" Access By ID
-> Index "IX_GADR04" Range Scan (partial match: 1/3)
show index IX_GADR04;
IX_GADR04 UNIQUE INDEX ON GADR (ID_TENANT, SUCHBEG, ID)
SELECT RDB$STATISTICS FROM RDB$INDICES WHERE RDB$INDEX_NAME='IX_GADR04' --> 0.000008017896107
Tests where executed with a freshly created database.
firebird-automations
commented
3 years ago
Submitted by: Stephan Perktold (stephanp)
Firebird 3 apparently stubbornly uses an index corresponding to the WHERE clause, even though the same value is contained in all rows (index is not selective at all), and ignores a compound index that would exactly match the ORDER BY clause. The query of a large table thus takes about 1 minute until we can read the first record, because Firebird first sorts the table without the help of an index and then filters it with the help of a non-selective index. Tests have shown that specifying the FIRST clause (even with the specification of 1 billion data records!) leads to lightning fast results, because it uses the ideal index. Up to Firebird 2.5.9 this always worked ideally even without the FIRST clause.
Test without FIRST clause:
Test with FIRST clause:
Tests where executed with a freshly created database.