2008/11/10 David Rowley <[EMAIL PROTECTED]>: > Hitoshi Harada wrote: >> I found how to do it, though it's only on the case you gave. Thinking >> about the planner optimization of the Window nodes (and its attached >> Sort nodes), we must consider the execution order of more than one >> node. In the test case we only take care of only one window, but there >> may be more window/sort node sets, which is too difficult to choose >> the best execution order including the downer indexscan, mergejoin in >> subquery and sort-based GROUP BY. So I didn't touch the complicated >> planner jungle. I rewrote the patch so that only the given bottom >> window's sort can consider indexscan. Deeper optimizations are over my >> capability. > > After more playing around with a few queries and testing some performance of > larger tables. I discovered something strange in the plan for this query. > > > david=# explain select date,lag(date,1) over (order by date) from > meter_Readings order by date; > QUERY PLAN > ---------------------------------------------------------------------------- > -------------------------------- > Sort (cost=1038.73..1063.74 rows=10001 width=4) > Sort Key: date > -> Window (cost=0.00..374.27 rows=10001 width=4) > -> Index Scan using meter_readings_pkey on meter_readings > (cost=0.00..299.27 rows=10001 width=4) > (4 rows) > > Is the final sort still required? Is it not already sorted in the window? >
Oh, I forgot to mention about it. This behavior is also fixed and it works without sort on the window now. I don't remember at all why I did so and there's no comment around that but regression tests showed there is no preblem without it. Regards, -- Hitoshi Harada -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers