Re: Index not used with IS NULL

Dmitry Tkach <dmitry(at)openratings(dot)com> writes:
> Tom Lane wrote:
>> I don't see anything dangerous about it --- except perhaps to
>> readability and mantainability of the code. The problem is that IS NULL
>> doesn't fit into the operator-and-opclass model of what indexes can do.
>> If you can find a solution to that problem that's not a complete kluge,
>> I'm all ears.

> Well... At first glance, it seems that what needs to be done is:

> - add a special case in is_indexable_operator() to return true for IS_NULL

And is_indexable_operator() will know that this is safe how? Or do you
plan to fix the other three index types to support NULLs too?

> - modify _bt_checkkeys () to return isNull from inside if
> (key->sk_flags & SK_ISNULL) clause instead of just false.
> - remove sk_flags & SK_ISNULL checks from _bt_orderkeys

IIRC, SK_ISNULL marks that the value being compared against is null
--- not that the scan operator is ISNULL. An approach as above would
cause "WHERE x = something" indexscans to start returning nulls if the
"something" is null, no? You need a representation that preserves the
difference between "x = NULL" and "x IS NULL". The ScanKey structure
can't do this at the moment, mainly because it assumes that the scan
operator *is* an operator. Which IS NULL is not.

regards, tom lane