Message-ID: <b540e14f-f815-8de4-690e-3eb84e932d9d@gmail.com>
Date: Sun, 8 Jan 2023 11:59:27 +0530
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: David Rowley <dgrowleyml@gmail.com>
Cc: pghackers <pgsql-hackers@lists.postgresql.org>,
 Vik Fearing <vik@postgresfriends.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>
 <295ebbb0-f5b4-f090-b27e-9e069ef7eae1@gmail.com>
 <CAApHDvqD0B21J2Z44G+_KEf8AmqHPjhuwbX4b6NB2eyP2CZo=w@mail.gmail.com>
From: Ankit Kumar Pandey <itsankitkp@gmail.com>
In-Reply-To: <CAApHDvqD0B21J2Z44G+_KEf8AmqHPjhuwbX4b6NB2eyP2CZo=w@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/b540e14f-f815-8de4-690e-3eb84e932d9d%40gmail.com>
Precedence: bulk


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