From: Eugen Konkov <kes-kes@yandex.ru>
To: David Rowley <dgrowleyml@gmail.com>
Cc: PG Bug reporting form <noreply@postgresql.org>
Cc: PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Cc: ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
Date: Sat, 15 May 2021 19:10:44 +0300
Message-ID: <515893534.20210515191044@yandex.ru> (raw)
In-Reply-To: <CAApHDvqds8-3kbRqqm4bBF77z2LTy+6y22HG424xhX8YRSvhmQ@mail.gmail.com>
References: <16968-9c8a1708c1c04355@postgresql.org>
<846547866.20210416222735@yandex.ru>
<226821618.20210513163513@yandex.ru>
<CAApHDvrayMkhak9eYbhHs67XvkZSOiscxJEER2yECN-k4L91oQ@mail.gmail.com>
<1035195294.20210515173416@yandex.ru>
<CAApHDvqds8-3kbRqqm4bBF77z2LTy+6y22HG424xhX8YRSvhmQ@mail.gmail.com>
Hello David,
Saturday, May 15, 2021, 5:52:47 PM, you wrote:
> On Sun, 16 May 2021 at 02:34, Eugen Konkov <kes-kes@yandex.ru> wrote:
>> I found a case when `not assigning a ressortgroupref to the whole-row var` cause
>> wrong window function calculations.
>>
>> I use same query. The difference come when I wrap my query into
>> function. (see full queries in attachment)
>>
>> 1.
>> SELECT *
>> FROM agreement_totals( tstzrange( '2020-07-01', '2020-08-01' ) )
>> WHERE agreement_id = 161::int AND (o).period_id = 10::int
>>
>> 2.
>> SELECT *
>> sum( .... ) over wagreement
>> FROM ....
>> WHERE agreement_id = 161::int AND (o).period_id = 10::int
>> WINDOW wagreement AS ( PARTITION BY agreement_id )
>>
>> For first query window function calculates SUM over all agreements,
>> then some are filtered out by (o).period_id condition.
> This is unrelated to the optimisation that you were asking about before.
> All that's going on here is that WHERE is evaluated before SELECT.
> This means that your filtering is done before the window functions are
> executed. This is noted in the docs in [1]:
>> The rows considered by a window function are those of the “virtual table” produced by the query's FROM clause as filtered by its WHERE, GROUP BY, and HAVING clauses if any. For example, a row removed because it does not meet the WHERE condition is not seen by any window function. A query can contain multiple window functions that slice up the data in different ways using different OVER clauses, but they all act on the same collection of rows defined by this virtual table.
> If you want to filter rows after the window functions are evaluated
> then you'll likely want to use a subquery.
> David
> [1] https://www.postgresql.org/docs/13/tutorial-window.html
Sorry, I miss that WHERE works first and after it
window function.
>This is unrelated to the optimisation that you were asking about before.
So, yes, unrelated.
Thank you for your answers.
--
Best regards,
Eugen Konkov
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kes-kes@yandex.ru, dgrowleyml@gmail.com, noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
In-Reply-To: <515893534.20210515191044@yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox