firebird
firebird copied to clipboard
"Unique scan" is incorrectly reported in the explained plan for unique index and IS NULL predicate
SELECT *
FROM RDB$RELATIONS
WHERE RDB$RELATION_NAME IS NULL
Explain plan
Select Expression
-> Filter
-> Table "RDB$RELATIONS" Access By ID
-> Bitmap
-> Index "RDB$INDEX_0" Unique Scan
Unique indexes can generally contain multiple NULL values, so "Unique scan" is only possible for pure equality (=) comparisons, but not for IS NULL and IS NOT DISTINCT FROM predicates. For such predicates, "Range Scan (full match)" is expected.
Select Expression
-> Filter
-> Table "RDB$RELATIONS" Access By ID
-> Bitmap
-> Index "RDB$INDEX_0" Range Scan (full match)