Re: Index ignored with "is not distinct from", 8.2 beta2

I can see that adding null to indexes would allow all cases of "is not
distinct from"
to use them.

The lack of null in indexes would explain why a condition such as "col is
not distinct from null"
would not pick up an index.

But my example was: "col is not distinct from 123"
and the equivalent longer form has no problem picking up the index

I could restate the problem as: Why can't the planner handle
"col is not distinct from 123" as well as
"col is not null and 123 is not null and col = 123 or col is null and 123 is
null"

Jean-Pierre Pelletier

>From: Martijn van Oosterhout <kleptog(at)svana(dot)org>
>Reply-To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
>To: JEAN-PIERRE PELLETIER <pelletier_32(at)sympatico(dot)ca>
>CC: jim(at)nasby(dot)net, pgsql-hackers(at)postgresql(dot)org
>Subject: Re: [HACKERS] Index ignored with "is not distinct from", 8.2 beta2
>Date: Tue, 7 Nov 2006 11:03:42 +0100
>
>On Mon, Nov 06, 2006 at 09:10:40PM -0500, JEAN-PIERRE PELLETIER wrote:
> > I understand that the planner doesn't use indexes for IS NOT DISTINCT
>FROM,
> > but it would
> > be good because "is not distinct from" is very useful when you have
>nulls
> > but don't want to use three value logic.
>
>The main issue is that currently indexes cannot be used to find NULLs
>in a table. Patches have been created that cover most index types, but
>it's not part of the main distribution.
>
>Partial indexes can be a solution to the "x IS NULL" clauses.
>
>Hope this helps,
>--
>Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> > From each according to his ability. To each according to his ability to
>litigate.

><< signature.asc >>