Re: EXISTS clauses not being optimized in the face of 'one time pass' optimizable expressions

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Jun 21, 2016 at 4:18 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>> explain analyze select * from foo where false or exists (select 1 from
>> bar where good and foo.id = bar.id); -- A
>> explain analyze select * from foo where exists (select 1 from bar
>> where good and foo.id = bar.id); -- B
>>
>> These queries are trivially verified as identical but give very different plans.

> Right. I suspect wouldn't be very hard to notice the special case of
> FALSE OR (SOMETHING THAT MIGHT NOT BE FALSE) but I'm not sure that's
> worth optimizing by itself.

Constant-folding will get rid of the OR FALSE (as well as actually-useful
variants of this example). The problem is that that doesn't happen till
after we identify semijoins. So the second one gives you a semijoin plan
and the first doesn't. This isn't especially easy to improve. Much of
the value of doing constant-folding would disappear if we ran it before
subquery pullup + join simplification, because in non-stupidly-written
queries those are what expose the expression simplification opportunities.
We could run it twice but that seems certain to be a dead loser most of
the time.

> A more promising line of attack as it
> seems to me is to let the planner transform back and forth between
> this form for the query and the UNION form.

Maybe, but neither UNION nor UNION ALL would duplicate the semantics
of OR, so there's some handwaving here that I missed.

regards, tom lane