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

On Tue, Jun 21, 2016 at 4:18 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> Observe the following test case (apologies if this is a well
> understood problem):
>
> create temp table foo as select generate_series(1,1000000) id;
> create index on foo(id);
>
> create temp table bar as select id, id % 100000 = 0 as good from
> generate_series(1,1000000) id;
> create index on bar(good);
>
> analyze foo;
> analyze bar;
>
> 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. 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. Obviously, in this case,
the WHERE false branch could then be pruned altogether, but there are
lots of other cases where both branches survived. Tom's upper planner
pathification stuff makes it much easier to think about how such an
optimization might work, but I think it's still not particularly
simple to get right.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company