Re: Allowing join removals for more join types

On Sun, May 25, 2014 at 5:42 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> David Rowley <dgrowleyml(at)gmail(dot)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.
>
>
I should have explained more clearly. I was meaning that a query such as
this:

SELECT a.* FROM a LEFT OUTER JOIN (SELECT id,LAG(id) OVER (ORDER BY id) AS
prev_id FROM b) b ON a.id=b.id;

assuming that id is the primary key, could have the join removed.
I was just commenting on this as it's probably a fairly common thing to
have a subquery with windowing functions in order to perform some sort of
filtering of window function columns in the outer query.
The other use cases for example:

SELECT a.* FROM a LEFT OUTER JOIN (SELECT id FROM b LIMIT 10) b ON a.id=b.id
;

Are likely less common.

Regards

David Rowley