Re: Using indexes for ORDER BY and PARTITION BY clause in windowing functions

Sameer Kumar wrote
> edb=# explain analyze select max(score) from student_score group by
> course;

This query returns 6 records. The window one returns 123,000. Why do you
expect these to have anywhere near the same performance or plan?

You can enable/disable indexes/scans to see what alternatives plans may
provide but nothing here stands out as being obviously incorrect.

I'm not really clear on what your question is. Generally it sounds as if
you are wondering if there are any plans to I prove the algorithms behind
window function processing. Are you just looking at symptoms and thus
possibly have unreasonable expectations or do you actually see an avenue for
improvement in the engine?

> QUERY PLAN | Sort Method: external merge Disk: 7576kB

Work memory; I/O is killing your performance on this query. It is more
flexible but you pay a price for that.

> Another thing, (I may be stupid and naive here) does PostgreSQL re-uses
> the
> hash which has been already created for sort. In this case the inner query
> must have created a hash for windoing aggregate. Can't we use that same
> one
> while applying the the filter "rn=1" ?

Probably but others more knowledgable will need to answer authoritatively.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Using-indexes-for-ORDER-BY-and-PARTITION-BY-clause-in-windowing-functions-tp5775605p5775708.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.