Message-ID: <8f73cdbd-a1fa-cc38-21e2-d98c9f5b4a31@postgresfriends.org>
Date: Sun, 8 Jan 2023 20:21:03 +0100
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101
 Thunderbird/102.4.2
Subject: Re: Todo: Teach planner to evaluate multiple windows in the optimal
 order
Content-Language: en-US
To: Ankit Kumar Pandey <itsankitkp@gmail.com>,
 David Rowley <dgrowleyml@gmail.com>
Cc: pghackers <pgsql-hackers@lists.postgresql.org>
References: <83d80853-a45c-d85c-68eb-59acfe7fb5fb@gmail.com>
 <CAApHDvp=r1LnEKCmWCYaruMPL-jP4j_sdc8yeFYwaDT1ac5GsQ@mail.gmail.com>
 <7f77ee7d-bd04-d8e2-bb34-42395fd1f7c2@gmail.com>
 <CAApHDvq=g2=ny59f1bvwRVvupsgPHK-KjLPBsSL25fVuGZ4idQ@mail.gmail.com>
 <6ebcc137-45ea-6373-e3e1-3c304c452e1f@gmail.com>
 <441d135e-1941-c3ef-1649-18c3e8811549@postgresfriends.org>
 <9620d994-b89a-2dcf-fca5-821e19d56858@gmail.com>
 <CAApHDvruS-3gG_KYXPb_=Um_TfUXi+G4o7VyBVbChTjThXF2yA@mail.gmail.com>
 <a58a04a9-2dd6-7290-35c5-e3e23f326b85@gmail.com>
 <01248493-182b-0f92-f2f2-ff28dc4b2e83@gmail.com>
 <CAApHDvpEYcQ7d-c6Lje26gLi4dXOM3qQ9nrTe1Z241tT8nBiCg@mail.gmail.com>
 <c1d1aace-ce9f-9ec3-1984-5319920b1594@gmail.com>
 <CAApHDvp8BpUEo_kQdGHWNPCjcmRWCdiy5p26SoQA4R6rinkaLA@mail.gmail.com>
 <2040c902-32b1-46c8-0b26-d3f43a16b9bb@gmail.com>
 <12f6ef19-e06e-8ea9-ad98-3fd92afb7f0b@gmail.com>
 <600c351a-ba63-8264-d51a-5d10bf966a52@postgresfriends.org>
 <2eb403f8-9bc8-a743-2ddf-2cd96e73536d@gmail.com>
From: Vik Fearing <vik@postgresfriends.org>
In-Reply-To: <2eb403f8-9bc8-a743-2ddf-2cd96e73536d@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/8f73cdbd-a1fa-cc38-21e2-d98c9f5b4a31%40postgresfriends.org>
Precedence: bulk

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