Re: Todo: Teach planner to evaluate multiple windows in the optimal order

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. Consider the following query:

postgres=# select a, b, c, rank() over (order by a, b)
from (values (1, 2, 1), (1, 2, 2), (1, 2, 1)) as abcd (a, b, c)
order by a, b, c;

a | b | c | rank
---+---+---+------
1 | 2 | 1 | 1
1 | 2 | 1 | 1
1 | 2 | 2 | 1
(3 rows)

If you change the window's ordering like you suggest, you get this
different result:

postgres=# select a, b, c, rank() over (order by a, b, c)
from (values (1, 2, 1), (1, 2, 2), (1, 2, 1)) as abcd (a, b, c)
order by a, b, c;

a | b | c | rank
---+---+---+------
1 | 2 | 1 | 1
1 | 2 | 1 | 1
1 | 2 | 2 | 3
(3 rows)

--
Vik Fearing