Re: Index not used with IS NULL

From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Dmitry Tkach <dmitry(at)openratings(dot)com>, pgsql-general(at)postgresql(dot)org
Subject: Re: Index not used with IS NULL
Date: 2003-02-18 05:29:46
Message-ID: 20030218052946.GC23778@svana.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Shridhar Daithankar<shridhar_daithankar@persistent.co.in> 2003-02-18 05:34:03 Re: Table Partitioning in Postgres:
Previous Message Tom Lane 2003-02-18 05:17:01 Re: TIMESTAMP WITH( OUT)? TIME ZONE indexing/type choice...