Re: Windowing Function Patch Review -> Performance Comparison.

2008/11/10 David Rowley <dgrowley(at)gmail(dot)com>:
> 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