Re: Index not used with IS NULL

On Mon, Feb 17, 2003 at 11:54:59PM -0500, Tom Lane wrote:
> Martijn van Oosterhout <kleptog(at)svana(dot)org> writes:
> > My Plan B was to create a operator IS (and its inverse ISNOT) which is then
> > binary operator. It would be identical to =3D and <> except that it would be
> > defined where either argument is NULL. Fiddle the parser to use this
> > operator instead of the unary ISNULL.
>
> I don't think there's anything fundamental that assumes that indexable
> operators are binary, so you might as well make the operator unary. The
> problem with this approach isn't that --- it's the tedium of making an
> ISNULL operator for every datatype, adding it to every opclass, etc.

I'll have to look again. I thought there was this whole section dealing with
how < related to <= and such which kind of implied it had to be binary.
Similarly, with an index scan on a unary operator would require a new entry
point, because it requires *no* parameters. An index scan relies on there
being an order, how can a unary operator have an order?

That said, how is this different from having an isnull() function and
building a functional index on that? An operator is just a function with
different syntax. Are there any optimisations involving IS NULL that
preclude us from simply replacing it with a function call globally. I guess
the issue is that it's a new index rather than using the existing one.

As for all the different versions, the actual implementation doesn't need to
know about the datatype, it only needs the NULL-ness of the argument so it
should be generic. Can an operator/function have a argument type that
matches anything?

> Maybe there's no non-kluge answer that doesn't make us buy into that,
> but it sure seems like the hard way. It's definitely not going to be
> a short and sweet patch :-(

I'm still hoping. I'm sure we just need to think about it the right way...
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Support bacteria! They're the only culture some people have.