On Sun, May 25, 2014 at 5:42 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:
> David Rowley <dgrowle...@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 ; Are likely less common. Regards David Rowley
