Re: Index not used with IS NULL

On Mon, Feb 17, 2003 at 10:52:54AM -0500, Tom Lane wrote:
> Dmitry Tkach <dmitry@openratings.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?

I would have thought that the other index type supported null anyway, for
the purposes of uniqueness checks.

> > - 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.

I remember looking into this a while ago. My solution to that problem was
that x = NULL is always NULL and so doesn't need to go through the scan
anyway (index or sequential). Once you've taken care of the x = NULL case
elsewhere, you can use the available state for x IS NULL.

I don't remember if I actually found the place to fix that though.

I would really like it if this was finally made to work.
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.