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.

Sorry for the delay on this. I've updated the benchmark results using the
new patch you sent today. I did a dump and re-load of the tables, since some
of the numbers are randomly generated I wouldn't want to compare them to the
old results for any of the tests. This is a complete new list with the CVS
head as of this morning.

Test   Sub Query Self Join Vladimir    Windowing UOM Window over Best alt
Test 1 504       N/A       N/A      568       TPS 12.7%
Test 2 340.9     425       182    450.38    TPS 6.0%
Test 3 1.304     8.12      1.963    7.52      TPS -7.4%
Test 4 422       365       195    630        TPS 49.3%
Test 5 8.874     N/A       5825     31203     TPH 435.6%
Test 6 251       N/A       N/A      300       TPS 19.5%

Only test 3 and 5 made use of the index scan, performance dropped slightly
on test 3 but there's not much point in paying much attention to that since
we're probably close to the cross over point between a seqscan and indexscan
where the planner's decision is not as critical.

Certain Self join methods I used don't implement the exact requirements I've
stated at the top of the test. For example the meter reading for self join
requires no days to be missed.

Maybe multi window optimisation is one for 8.5's TODO

I've attached the test scripts.

David.