Performance degradation in FB 3.0.2 compared to FB 2.5.7
--------------------------------------------------------
Key: CORE-5421
URL: http://tracker.firebirdsql.org/browse/CORE-5421
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 3.0.1
Environment: Windows 10
Reporter: Andrei Kireev
Here is the query:
SELECT FIRST(1)
doc.id, doc.documentdate
FROM
usr$wg_taxation card
JOIN gd_document doc ON doc.id = card.documentkey
WHERE
card.usr$emplkey = :emplkey
AND
doc.documentdate <= :begindate
ORDER BY
doc.documentdate DESC
Table usr$wg_taxation has 1 record, table gd_document -- 7.5 millions.
Statistics for PK of usr$wg_taxation -- 1.
Statistics for PK of GD_DOCUMENT -- 1.3158557976567E-7
Statistics for GD_X_DOCUMENT_DOCUMENTDATE index -- 0.00020177561964374.
FB 2.5.7 execution time less than 1 ms.
Query plan: SORT (JOIN (CARD INDEX (USR$FKWG_TAXATION951), DOC INDEX
(RDB$PRIMARY112)))
FB 3.0.2 execution time 13 156 ms.
Query plan:
Select Expression
-> First N Records
-> Nested Loop Join (inner)
-> Filter
-> Table "GD_DOCUMENT" as "DOC" Access By ID
-> Index "GD_X_DOCUMENT_DOCUMENTDATE" Range Scan (lower
bound: 1/1)
-> Filter
-> Table "USR$WG_TAXATION" as "CARD" Access By ID
-> Bitmap
-> Index "RDB$PRIMARY502" Unique Scan
--
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
------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, SlashDot.org! http://sdm.link/slashdot
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
