Re: IS NOT DISTINCT FROM + Indexing

On 2014-07-21 16:51:32 -0700, Peter Geoghegan wrote:
> On Mon, Jul 21, 2014 at 4:16 PM, Jonathan S. Katz
> <jonathan(dot)katz(at)excoventures(dot)com> wrote:
> > With NULLs being indexable, I was wondering if there was some reason why IS NOT DISTINCT FROM could not use the index?
>
> FWIW this works:
>
> postgres=# explain analyze select * from orders where orderid in (5, null);

I rather doubt it will. x in (y1, ... yn) is essentially expanded to x =
y1 OR x = y2, ... OR x = yn. I.e. the NULL comparison will be done using
normal equality comparison and thus not return a row with a NULL
orderid. Am I missing something?

> I think that it would almost be a Simple Matter of Programming to make
> IS NOT DISTINCT FROM indexable. Under the hood, IS DISTINCT FROM isn't
> very different to using the equality operator:

But yea, it probably wouldn't take very much for that.

Greetings,

Andres Freund