Re: Allowing join removals for more join types

David Rowley <dgrowleyml@gmail.com> writes:
> I agree that there are not many cases left to remove the join that remain
> after is_simple_subquery() has decided not to pullup the subquery. Some of
> the perhaps more common cases would be having windowing functions in the
> subquery as this is what you need to do if you want to include the results
> of a windowing function from within the where clause. Another case, though
> I can't imagine it would be common, is ORDER BY in the subquery... But for
> that one I can't quite understand why is_simple_subquery() stops that being
> flattened in the first place.

The problem there is that (in general) pushing qual conditions to below a
window function will change the window function's results.  If we flatten
such a subquery then the outer query's quals can get evaluated before
the window function, so that's no good.  Another issue is that flattening
might cause the window function call to get copied to places in the outer
query where it can't legally go, such as the WHERE clause.
        regards, tom lane