Re: Windowing Function Patch Review -> Performance Comparison.

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?

> Tests:
>
> Please see attached file. Perhaps there were more efficient ways for certain
> queries, I just couldn't think of them...
>
> Please let me know if you feel I should be conducting the review in another
> way.

Thanks for your test. Didn't post publicly, I've also tested real
problems and performed better than I thought. If you can afford it,
could you add selfjoin cases? It's like:

-- normal
SELECT t1.id, t1.grp, count(t2) + 1 AS row_number
FROM t t1
INNER JOIN t t2 ON t1.grp = t2.grp AND t1.id > t2.id;

-- windowing
SELECT id, grp, row_number() OVER (PARTITION grp ORDER BY id)
FROM t;

Regards,

--
Hitoshi Harada