On Wed, 4 Jan 2023 at 03:11, Ankit Kumar Pandey <itsanki...@gmail.com> wrote: > #2. If order by clause in the Window function is superset of order by in query > > explain analyze select a,row_number() over (order by a,b,c),count(*) over > (order by a,b) from abcd order by a; > > QUERY PLAN > ---------------------------------------------------------------------------------------------------------------------- > WindowAgg (cost=39.27..64.27 rows=625 width=28) (actual time=1.089..3.020 > rows=625 loops=1) > -> WindowAgg (cost=39.27..53.34 rows=625 width=20) (actual > time=1.024..1.635 rows=625 loops=1) > -> Sort (cost=39.27..40.84 rows=625 width=12) (actual > time=1.019..1.084 rows=625 loops=1) > Sort Key: a, b, c > Sort Method: quicksort Memory: 54kB > -> Seq Scan on abcd (cost=0.00..10.25 rows=625 width=12) > (actual time=0.023..0.265 rows=625 loops=1) > Planning Time: 0.071 ms > Execution Time: 3.156 ms > (8 rows) > > No, additional sort is needed to be performed in this case, as you referred.
It looks like that works by accident. I see no mention of this either in the comments or in [1]. What seems to be going on is that common_prefix_cmp() is coded in such a way that the WindowClauses end up ordered by the highest tleSortGroupRef first, resulting in the lowest order tleSortGroupRefs being the last WindowAgg to be processed. We do transformSortClause() before transformWindowDefinitions(), this is where the tleSortGroupRef indexes are assigned, so the ORDER BY clause will have a lower tleSortGroupRef than the WindowClauses. If we don't have one already, then we should likely add a regression test that ensures that this remains true. Since it does not seem to be documented in the code anywhere, it seems like something that could easily be overlooked if we were to ever refactor that code. I just tried moving the calls to transformWindowDefinitions() so that they come before transformSortClause() and our regression tests still pass. That's not great. With that change, the following query has an additional sort for the ORDER BY clause which previously wasn't done. explain select a,b,c,row_number() over (order by a) rn1, row_number() over(partition by b) rn2, row_number() over (order by c) from abc order by b; David [1] https://www.postgresql.org/message-id/flat/124A7F69-84CD-435B-BA0E-2695BE21E5C2%40yesql.se