Re: Windowing Function Patch Review -> Performance Comparison.

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.27rows=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?

The table I was testing on split the sort to disk, I would probably not have
noticed otherwise.

David.