Re: Todo: Teach planner to evaluate multiple windows in the optimal order - Mailing list pgsql-hackers

From Vik Fearing
Subject Re: Todo: Teach planner to evaluate multiple windows in the optimal order
Date
Msg-id 8f73cdbd-a1fa-cc38-21e2-d98c9f5b4a31@postgresfriends.org
Whole thread Raw
In response to Re: Todo: Teach planner to evaluate multiple windows in the optimal order  (Ankit Kumar Pandey <itsankitkp@gmail.com>)
List pgsql-hackers
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




pgsql-hackers by date:

Previous
From: Mason Sharp
Date:
Subject: Re: POC: Lock updated tuples in tuple_update() and tuple_delete()
Next
From: Justin Pryzby
Date:
Subject: Re: Add LZ4 compression in pg_dump