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

On 08/01/23 04:06, David Rowley wrote:

> On Sun, 8 Jan 2023 at 05:45, Ankit Kumar Pandey <itsankitkp@gmail.com> wrote:
>> Attached patch with test cases.

> I can look at this in a bit more detail if you find a way to fix the
> case you mentioned earlier. i.e, push the sort down to the deepest
> WindowAgg that has pathkeys contained in the query's ORDER BY
> pathkeys.

> EXPLAIN (COSTS OFF)
> SELECT empno,
>       depname,
>       min(salary) OVER (PARTITION BY depname ORDER BY empno) depminsalary,
>       sum(salary) OVER (PARTITION BY depname) depsalary
> FROM empsalary
> ORDER BY depname, empno, enroll_date;
>                  QUERY PLAN
> -----------------------------------------------
> Incremental Sort
>   Sort Key: depname, empno, enroll_date
>   Presorted Key: depname, empno
>   ->  WindowAgg
>         ->  WindowAgg
>              ->  Sort
>                    Sort Key: depname, empno
>                    ->  Seq Scan on empsalary
> (8 rows)
>
> You'll also need to pay attention to how the has_runcondition is set.
> If you start pushing before looking at all the WindowClauses then you
> won't know if some later WindowClause has a runCondition. 

Yes, this should be the main culprit.


> Adding an additional backwards foreach loop should allow you to do all the
> required prechecks and find the index of the WindowClause which you
> should start pushing from.

This should do the trick. Thanks for headup, I will update the patch 
with suggested

changes and required fixes.


Regards,

Ankit