Re: Proposed patch for qual pushdown into UNION/INTERSECT

On Thu, 29 Aug 2002, Tom Lane wrote:

> Seems to work in preliminary testing, but I'd like someone else to
> give it a try before I decide it works.  Patch is against CVS tip.

It seems to work for me as well.  I didn't do complicated data, but
some complicated query structures.

I do wonder if queries like
select * from ((select * from bar1 union all select * from bar2
union all (select * from bar1 except select * from bar2)) intersect select
* from bar1) as foo where a>4;

can push the a>4 restriction into the right side of the intersect which it
doesn't seem to right now, but I haven't sat down to really think
ramifiactions all the way through (mostly because I'm getting ready to go
on long weekend vacation). Or for that matter into the left portions of
the union all and union.

Pushing to the parts of the except is not allowed, but I don't think
that means that it can't go to the other parts of the expression when
the output from the except is itself used in an union/intersect.
So instead of something like (if I'm reading the explain correctly)
Subquery (filter a>4)
 Intersect
  Append
   Subquery
   Subquery
   Except
    Subquery
    Subquery
  Subquery

We could probably do:
Subquery
 Intersect
  Append
   Subquery (passing down a>4)
   Subquery (passing down a>4)
   <something> (filter a>4)
    Except
     Subquery
     Subquery
  Subquery (passing down a>4)
and get the same effect, but I think
the mixed case is probably rare enough
to not worry about for now.