You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
SELECT FIRST(1) http://doc.id, doc.documentdate
FROM
usr$wg_taxation card
JOIN gd_document doc ON http://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
It seems that this issue is the same described in CORE5310 and comments (Incorrect PLAN using in Firebird 3 making it slow): Firebird 3 is sometimes choosing the index with less selectivity.
Submitted by: Andrei Kireev (andreik)
Votes: 2
Here is the query:
SELECT FIRST(1)
http://doc.id, doc.documentdate
FROM
usr$wg_taxation card
JOIN gd_document doc ON http://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
Commits: 60d20db 5dca3df
The text was updated successfully, but these errors were encountered: