Re: Windowing Function Patch Review -> Performance Comparison.

2008/11/2 David Rowley <dgrowley(at)gmail(dot)com>:
> Hitoshi Harada Wrote:
>> 2008/11/2 David Rowley <dgrowley(at)gmail(dot)com>:
>> > Obervations:
>> >
>> > Test 3 and 5 did not seem to make use of an index to get a sorted list
>> of
>> > results. I disabled enable_seqscan but the planner still failed to
>> choose
>> > index_scan. Is there any reason for this? Perhaps I'm missing something.
>> > Hitoshi, can you take a look at this?
>
>> Ah, good point. Maybe it's because I haven't paid attention to choose
>> index_scan for upper sort node. I just put the sort node whatever the
>> downer node is, so it might be needed to sink the information down to
>> scan choice process that we use sort node upper. Could someone point
>> me out how to do it, or which part of the existing code would be a
>> good guide?
>
> I know you need to wait for an answer about this, so I'd like to delay any
> further performance tests until that's sorted out as it should affect
> performance of larger tables quite a bit.
>

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.

Attach is a delta patch against the last one. Also see the git diff:
http://git.postgresql.org/?p=~davidfetter/window_functions/.git;a=commitdiff;h=bbba638f721a7e1d11cb3ee6af3bc1d7d3c11aa8;hp=48b73ee574779a14a3c36d373d8544d59a5b8b46

Regards,

--
Hitoshi Harada