On Fri, Jul 11, 2014 at 1:11 PM, Tom Lane <tgl@sss.pgh.pa.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.
