Re: Allowing join removals for more join types - Mailing list pgsql-hackers

From Tom Lane
Subject Re: Allowing join removals for more join types
Date
Msg-id 7675.1400953350@sss.pgh.pa.us
Whole thread Raw
In response to Re: Allowing join removals for more join types  (David Rowley <dgrowleyml@gmail.com>)
Responses Re: Allowing join removals for more join types
List pgsql-hackers
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



pgsql-hackers by date:

Previous
From: Noah Misch
Date:
Subject: Re: buildfarm: strange OOM failures on markhor (running CLOBBER_CACHE_RECURSIVELY)
Next
From: Jim Nasby
Date:
Subject: Re: SQL access to database attributes