On Thu, 5 Jan 2023 at 15:18, Vik Fearing <v...@postgresfriends.org> wrote: > > On 1/4/23 13:07, Ankit Kumar Pandey wrote: > > Also, one thing, consider the following query: > > > > explain analyze select row_number() over (order by a,b),count(*) over > > (order by a) from abcd order by a,b,c; > > > > In this case, sorting is done on (a,b) followed by incremental sort on c > > at final stage. > > > > If we do just one sort: a,b,c at first stage then there won't be need to > > do another sort (incremental one). > > > This could give incorrect results.
Yeah, this seems to be what I warned against in [1]. If we wanted to make that work we'd need to do it without adjusting the WindowClause's orderClause so that the peer row checks still worked correctly in nodeWindowAgg.c. Additionally, it's also not that clear to me that sorting by more columns in the sort below the WindowAgg would always be a win over doing the final sort for the ORDER BY. What if the WHERE clause (that could not be pushed down before a join) filtered out the vast majority of the rows before the ORDER BY. It might be cheaper to do the sort then than to sort by the additional columns earlier. David [1] https://www.postgresql.org/message-id/CAApHDvp=r1lnekcmwcyarumpl-jp4j_sdc8yefywadt1ac5...@mail.gmail.com
