Re: Automatic optimization of IN clauses via INNER JOIN

On Fri, Dec 18, 2009 at 7:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> If at least one column in the subselect is strict, you can rewrite it
>> that way yourself, but the optimizer won't do it. I wish it did, but I
>> don't wish it badly enough to have written the code myself, and
>> apparently neither does anyone else.
>
> I was thinking about this earlier today.  It's a bit of a PITA because
> we need the information very early in the planner, before it's done much
> analysis.  So for example we might find ourselves duplicating the work
> that will happen later to determine which tables are nullable by outer
> joins.  I think this would be all right as long as we ensure that it's
> only done when there's a chance for a win (ie, no extra cycles if
> there's not actually a NOT IN present).  It could still be an
> unpleasantly large amount of new code though.

I haven't looked at the code (I'm not even sure where you're thinking
this would need to happen) but is there any way that we can do this
and usefully hold onto the results for future use?

> Wouldn't we need to enforce that *all* columns of the subselect are
> non-null, rather than *any*?

[ thinks about it ]

Yes.

...Robert