IS NOT NULL / IS DISTINCT FROM NULL should use index

[EPluribusUnum]

Hi *!

SELECT * FROM customer c WHERE c.country IS NOT NULL
SELECT * FROM customer c WHERE c.country IS DISTINCT FROM NULL

both PLAN is NATURAL.

for every data type there is an equivalent expression that uses index.
varchar : field >= ''
numeric : (field >=0 OR field < 0); or field>= "possible minimum value"
date/time : like numeric
boolean : field IN (TRUE, FALSE)

[sim1984]

Why is this? If NULL were a value, then it would be equivalent to <>NULL. Using an inequality index is usually not useful, since in this case we need to read all index keys that are not equal to some value, and also retrieve the records themselves from these keys. Theoretically, this could be an advantage, but only if the almost values are NULL. But we have no information in statistics about NULL selectivity.

[EPluribusUnum]

Yes, in our case a lot of our fields (with foreign keys) contains mostly NULLs.
To utilize FK indexes corrently we write the equivalent expressions, but it is not as expressive than the IS NOT NULL/IS DISTINCT FROM condition.
Maybe then the next ODS version could keep statistics about null/not null ratio as well.

[tomaszdubiel18]

Yeah, this topic has been already a few times mentioned in firebird support google group. In our case it's also quite frequent to have a lot of nulls for columns used in indexes. Firebird's ability to know about null selectivity would be very appreciated.

[mrotteveel]

Have you tried in Firebird 5.0 with a partial index?

[EPluribusUnum]

Partial index does not help.
COMPUTED BY (country IS NOT NULL)
COMPUTED BY (country IS DISTINCT FROM NULL)

in both case the PLAN is still NATURAL

[dyemanov]

Partial index (in v5) is not he same as computed index.

CREATE INDEX ... WHERE country IS NOT NULL
CREATE INDEX ... WHERE country IS DISTINCT FROM NULL

[EPluribusUnum]

Oh, I see. Partial index helps, FB uses the new index.
But now the data is indexed twice, 1 index for FK and 1 partial index.

[mrotteveel]

That is a trade-off, yes, but unless and until we get better index statistics, this is probably the only workaround you can apply.

[dyemanov]

Not really twice, I'd say 1.x times, where x < 0.5 ;-) And this is just a workaround until NULLs are accounted in statistics.