Re: Allowing NOT IN to use ANTI joins

On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> I wrote:
> > We could no doubt fix this by also insisting that the left-side vars
> > be provably not null, but that's going to make the patch even slower
> > and even less often applicable. I'm feeling discouraged about whether
> > this is worth doing in this form.
>
>
:-( seems I didn't do my analysis very well on that one.

> Hm ... actually, there might be a better answer: what about transforming
>
> WHERE (x,y) NOT IN (SELECT provably-not-null-values FROM ...)
>
> to
>
> WHERE <antijoin condition> AND x IS NOT NULL AND y IS NOT NULL
>
> ?
>
>
I think this is the way to go.
It's basically what I had to do with the WIP patch I have here for SEMI
JOIN removal, where when a IN() or EXISTS type join could be removed due to
the existence of a foreign key, the NULL values still need to be filtered
out.

Perhaps it would be possible for a future patch to check get_attnotnull and
remove these again in eval_const_expressions, if the column can't be null.

Thanks for taking the time to fix up the weirdness with the NATURAL joins
and also making use of the join condition to prove not null-ability.

I'll try and get some time soon to look into adding the IS NOT NULL quals,
unless you were thinking of looking again?

Regards

David Rowley

> Of course this would require x/y not being volatile, but if they are,
> we're not going to get far with optimizing the query anyhow.
>
> regards, tom lane
>