Query optimizer uses the optimal index only with FIRST clause
-------------------------------------------------------------
Key: CORE-6434
URL: http://tracker.firebirdsql.org/browse/CORE-6434
Project: Firebird Core
Issue Type: Improvement
Affects Versions: 3.0.7
Environment: Firebird 3.0.7 x64 on Windows 10
Reporter: Stephan Perktold
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.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
