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

On 1/8/23 18:05, Ankit Kumar Pandey wrote:
>
>> On 08/01/23 21:36, Vik Fearing wrote:
>
>> On 1/8/23 11:21, Ankit Kumar Pandey wrote:
>>>
>>> Please find attached patch with addressed issues mentioned before.
>
>
>> I am curious about this plan:
>
>> +-- ORDER BY's in multiple Window functions can be combined into one
>> +-- if they are subset of QUERY's ORDER BY
>> +EXPLAIN (COSTS OFF)
>> +SELECT empno,
>> +       depname,
>> +       min(salary) OVER (PARTITION BY depname ORDER BY empno)
>> depminsalary,
>> +       sum(salary) OVER (PARTITION BY depname) depsalary,
>> +       count(*) OVER (ORDER BY enroll_date DESC) c
>> +FROM empsalary
>> +ORDER BY depname, empno, enroll_date;
>> +                      QUERY PLAN
>> +------------------------------------------------------
>> + WindowAgg
>> +   ->  WindowAgg
>> +         ->  Sort
>> +               Sort Key: depname, empno, enroll_date
>> +               ->  WindowAgg
>> +                     ->  Sort
>> +                           Sort Key: enroll_date DESC
>> +                           ->  Seq Scan on empsalary
>> +(8 rows)
>> +
>
>
>> Why aren't min() and sum() calculated on the same WindowAgg run?
>
> Isn't that exactly what is happening here? First count() with sort on
> enroll_date is run and
>
> then min() and sum()?

No, there are two passes over the window for those two but I don't see
that there needs to be.

> Only difference between this and plan generated by master(given below)
> is a sort in the end.

Then this is probably not this patch's job to fix.
--
Vik Fearing