Message-ID: <d6b86efa-c7c6-c4c9-8c6b-cf1385f14fa4@gmail.com>
Date: Tue, 10 Jan 2023 14:01:52 +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
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>
 <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>
 <CAApHDvp=YnmEQ8Ceu5g4wPL-b5YKmhQ7A3Lya6x=6ga=LLHMFA@mail.gmail.com>
 <8d0b24ce-f8ba-cd63-82ae-be853b4067e8@gmail.com>
 <CAApHDvpigtPVJOJ4EmWOAODtPTccz=L_gdPXDVgG2Z=iSPfNmQ@mail.gmail.com>
 <b1df021b-e6c0-f26a-1d5a-bd07f765db62@gmail.com>
 <CAApHDvqVjeHg6UgeCvFOa-uCaEFix9C_3YaT2qxsMtjN4oKbNQ@mail.gmail.com>
 <4ef7f947-526b-c4ec-f7a0-8b4586e693e4@gmail.com>
 <CAApHDvo2y9S2AO-BPYo7gMPYD0XE2Lo-KFLnqX80fcftqBCcyw@mail.gmail.com>
Content-Language: en-US
From: Ankit Kumar Pandey <itsankitkp@gmail.com>
In-Reply-To: <CAApHDvo2y9S2AO-BPYo7gMPYD0XE2Lo-KFLnqX80fcftqBCcyw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/d6b86efa-c7c6-c4c9-8c6b-cf1385f14fa4%40gmail.com>
Precedence: bulk


> On 10/01/23 10:53, David Rowley wrote:

> On Tue, 10 Jan 2023 at 06:15, Ankit Kumar Pandey <itsankitkp@gmail.com> wrote:
> > Do we have any pending items for this patch now?
>
> I'm just wondering if not trying this when the query has a DISTINCT
> clause is a copout.  What I wanted to avoid was doing additional
> sorting work for WindowAgg just to have it destroyed by Hash
> Aggregate.  I'm now wondering if adding both the original
> slightly-less-sorted path plus the new slightly-more-sorted path then
> if distinct decides to Hash Aggregate then it'll still be able to pick
> the cheapest input path to do that on.  Unfortunately, our sort
> costing just does not seem to be advanced enough to know that sorting
> by fewer columns might be cheaper, so adding the additional path is
> likely just going to result in add_path() ditching the old
> slightly-less-sorted path due to the new slightly-more-sorted path
> having better pathkeys. So, we'd probably be wasting our time if we
> added both paths with the current sort costing code.

> Maybe we should try and do this for DISTINCT queries if the
> distinct_pathkeys match the orderby_pathkeys. That seems a little less
> copout-ish. If the ORDER BY is the same as the DISTINCT then it seems
> likely that the ORDER BY might opt to use the Unique path for DISTINCT
> since it'll already have the correct pathkeys.  However, if the ORDER
> BY has fewer columns then it might be cheaper to Hash Aggregate and
> then sort all over again, especially so when the DISTINCT removes a
> large proportion of the rows.
>
> Ideally, our sort costing would just be better, but I think that
> raises the bar a little too high to start thinking of making
> improvements to that for this patch.

Let me take a stab at this. Depending on complexity, we can take

a call to address this in current patch or a follow up.

-- 
Regards,
Ankit Kumar Pandey