On Sun, May 25, 2014 at 5:42 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
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;