Date: Sat, 15 May 2021 19:10:44 +0300
From: Eugen Konkov <kes-kes@yandex.ru>
Message-ID: <515893534.20210515191044@yandex.ru>
To: David Rowley <dgrowleyml@gmail.com>
CC: PG Bug reporting form <noreply@postgresql.org>,
 PostgreSQL mailing lists <pgsql-bugs@lists.postgresql.org>,
 pgsql-performance <pgsql-performance@postgresql.org>,
 ladayaroslav@yandex.ru
Subject: Re: BUG #16968: Planner does not recognize optimization
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/515893534.20210515191044%40yandex.ru>
Precedence: bulk

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 va=
r` 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 =3D 161::int  AND  (o).period_id =3D 10::int
>>
>> 2.
>> SELECT *
>>   sum( .... ) over wagreement
>> FROM ....
>> WHERE agreement_id =3D 161::int  AND  (o).period_id =3D 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 =E2=80=9Cvirtu=
al table=E2=80=9D produced by the query's FROM clause as filtered by its WH=
ERE, GROUP BY, and HAVING clauses if any. For example, a row removed becaus=
e 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 dif=
ferent ways using different OVER clauses, but they all act on the same coll=
ection 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.

--=20
Best regards,
Eugen Konkov