Agree that windowing function will return all the rows compared to max and group by returing only max rows per group. But even while arriving at the aggregate/sorting windowing function seems to spend more effort than group by/order by.
I am just trying to see if we could somehow optimize the way windowing operations are performed. (May be in query rewrite). Datawarehouses could use that improvement. Its not my production box, so I can live with disk sort. I have tried with huge sorting memory but still I see a similar difference in cost of sorting for grouping/ordering Vs windowing function. Another thing regarding work_memory, I have generally seen that windowing functions expect more amount of memory for sorting compared to grouping/ordering clauses. On 24 Oct 2013 10:54, "David Johnston" <pol...@yahoo.com> wrote: > 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. > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
