Re: Index not used with IS NULL

Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
>> 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.

Well, (a) the other index types don't support uniqueness checks, and (b)
it wouldn't be relevant anyway, because multiple nulls don't violate
a unique constraint. GIST does support nulls in second and subsequent
columns of a multi-column index, because it *has* to do so, but not in
the first column --- and hash and rtree don't store nulls at all.

> I remember looking into this a while ago. My solution to that problem was
> that x =3D 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 =3D NULL case
> elsewhere, you can use the available state for x IS NULL.

But how do you get from point A to point B? You need to represent both
cases in ScanKeys further upstream than where that conclusion can be
drawn (namely _bt_orderkeys()) --- or else do some very substantial
restructuring work, which is exactly the point.

Also, this would amount to hard-wiring the assumption that indexable
operators are always strict. Which is rather a curious assumption
to be putting in, if your goal is to support the obviously-not-strict
construct IS NULL as an indexable operator. (Now I believe we make
that assumption anyway in the index access methods ... but wiring it
into ScanKeys, which is a very widespread data structure, would be the
death knell for any hope of removing it someday.)

regards, tom lane