Re: Re: Using indexes for ORDER BY and PARTITION BY clause in windowing functions - Mailing list pgsql-hackers

<p dir="ltr">Agree that windowing function will return all the rows compared to max and group by returing only max rows
pergroup. But even while arriving at the aggregate/sorting windowing function seems to spend more effort than group
by/orderby. <p dir="ltr">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.<br /> Its not my production box, so I
canlive with disk sort. I have tried with huge sorting memory but still I see a similar difference in cost of sorting
forgrouping/ordering Vs windowing function.<br /> Another thing regarding work_memory, I have generally seen that
windowingfunctions expect more amount of memory for sorting compared to grouping/ordering clauses.<div
class="gmail_quote">On24 Oct 2013 10:54, "David Johnston" <<a
href="mailto:polobo@yahoo.com">polobo@yahoo.com</a>>wrote:<br type="attribution" /><blockquote class="gmail_quote"
style="margin:00 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> Sameer Kumar wrote<br /> > edb=# explain
analyzeselect max(score) from student_score group by<br /> > course;<br /><br /> This query returns 6 records.  The
windowone returns 123,000.  Why do you<br /> expect these to have anywhere near the same performance or plan?<br /><br
/>You can enable/disable indexes/scans to see what alternatives plans may<br /> provide but nothing here stands out as
beingobviously incorrect.<br /><br /> I'm not really clear on what your question is.  Generally it sounds as if<br />
youare wondering if there are any plans to I prove the algorithms behind<br /> window function processing.  Are you
justlooking at symptoms and thus<br /> possibly have unreasonable expectations or do you actually see an avenue for<br
/>improvement in the engine?<br /><br /><br /> > QUERY PLAN |               Sort Method: external merge  Disk:
7576kB<br/><br /> Work memory; I/O is killing your performance on this query.  It is more<br /> flexible but you pay a
pricefor that.<br /><br /><br /> > Another thing, (I may be stupid and naive here) does PostgreSQL re-uses<br />
>the<br /> > hash which has been already created for sort. In this case the inner query<br /> > must have
createda hash for windoing aggregate. Can't we use that same<br /> > one<br /> > while applying the the filter
"rn=1"?<br /><br /> Probably but others more knowledgable will need to answer authoritatively.<br /><br /> David J.<br
/><br/><br /><br /><br /><br /> --<br /> View this message in context: <a
href="http://postgresql.1045698.n5.nabble.com/Using-indexes-for-ORDER-BY-and-PARTITION-BY-clause-in-windowing-functions-tp5775605p5775708.html"
target="_blank">http://postgresql.1045698.n5.nabble.com/Using-indexes-for-ORDER-BY-and-PARTITION-BY-clause-in-windowing-functions-tp5775605p5775708.html</a><br
/>Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.<br /><br /><br /> --<br /> Sent via
pgsql-hackersmailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br /> To
makechanges to your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-hackers"
target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></blockquote></div>