Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lhCUt-0006bc-Rr for pgsql-bugs@arkaria.postgresql.org; Thu, 13 May 2021 14:39:32 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1lhCUs-0007CL-PQ for pgsql-bugs@arkaria.postgresql.org; Thu, 13 May 2021 14:39:30 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lhBUm-0002zT-LS for pgsql-bugs@lists.postgresql.org; Thu, 13 May 2021 13:35:20 +0000 Received: from forward106j.mail.yandex.net ([2a02:6b8:0:801:2::109]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1lhBUj-0004TK-56 for pgsql-bugs@lists.postgresql.org; Thu, 13 May 2021 13:35:20 +0000 Received: from iva6-d3271451d97b.qloud-c.yandex.net (iva6-d3271451d97b.qloud-c.yandex.net [IPv6:2a02:6b8:c0c:9126:0:640:d327:1451]) by forward106j.mail.yandex.net (Yandex) with ESMTP id CB28611A1B21; Thu, 13 May 2021 16:35:14 +0300 (MSK) Received: from iva6-2d18925256a6.qloud-c.yandex.net (iva6-2d18925256a6.qloud-c.yandex.net [2a02:6b8:c0c:7594:0:640:2d18:9252]) by iva6-d3271451d97b.qloud-c.yandex.net (mxback/Yandex) with ESMTP id RBYgR7ZzRf-ZEJ0iwb5; Thu, 13 May 2021 16:35:14 +0300 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex.ru; s=mail; t=1620912914; bh=qxSsOiETOosRvTmVXYP90G6Yp6KPrQb1/U3078vzGQM=; h=In-Reply-To:Subject:To:Message-ID:From:References:Date:CC; b=HUobJ6tfOtOguXT3rnIWmrKFMM4gkoLQ4CFXke0AyHbdgVMfj3dsX/AVezfkKWUs9 p9Y95FEyorFX4ry3xgxml9kQ4AVY+hkROHMDDuRJsYfMKyqKtjLzLMGjxrfa5AHOrn fAmWMBY/8XGLAW9ArS+0PXfwDVAekYZ625WEhaSE= Authentication-Results: iva6-d3271451d97b.qloud-c.yandex.net; dkim=pass header.i=@yandex.ru Received: by iva6-2d18925256a6.qloud-c.yandex.net (smtp/Yandex) with ESMTPSA id eY5HZQmHnH-ZDLaZ37g; Thu, 13 May 2021 16:35:13 +0300 (using TLSv1.2 with cipher ECDHE-RSA-AES128-SHA256 (128/128 bits)) (Client certificate not present) Date: Thu, 13 May 2021 16:35:13 +0300 From: Eugen Konkov Message-ID: <226821618.20210513163513@yandex.ru> To: PG Bug reporting form , pgsql-bugs@lists.postgresql.org CC: pgsql-performance@postgresql.org, ladayaroslav@yandex.ru Subject: Re: BUG #16968: Planner does not recognize optimization In-Reply-To: <846547866.20210416222735@yandex.ru> References: <16968-9c8a1708c1c04355@postgresql.org> <846547866.20210416222735@yandex.ru> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----------00608A1780C578A95" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------------00608A1780C578A95 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Now I create minimal reproducible test case. https://dbfiddle.uk/?rdbms=3Dpostgres_13&fiddle=3D761a00fb599789d3db31b1208= 51d6341 Optimization is not applyed when I filter/partition by column using composi= te type name. Looking at this comparison table, we can see that optimization work only wh= en I refer to column using alias: (t.ag).ag_id as agreement_id -- making an alias PARTITION | FILTER | IS USED? ------------------------------ ALIAS | ORIG | NO ALIAS | ALIAS | YES ORIG | ALIAS | NO ORIG | ORIG | NO link to original problem with EXPLAIN ANALYZE: https://stackoverflow.com/q/= 67492673/4632019 Links to similar problems: https://stackoverflow.com/a/26237464/4632019 https://stackoverflow.com/q/65780112/4632019 Friday, April 16, 2021, 10:27:35 PM, you wrote: Now I attarch plans for both queries. tucha=3D> \out f2 tucha=3D> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) select *= from order_total_suma() ots where agreement_id =3D 3943; tucha=3D> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) select *= from order_total_suma() ots where (ots.o).agreement_id =3D 3943; tucha=3D> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select * from order_t= otal_suma() ots where agreement_id =3D 3943; tucha=3D> EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select * from order_t= otal_suma() ots where (ots.o).agreement_id =3D 3943; Friday, April 16, 2021, 10:18:45 PM, you wrote: > The following bug has been logged on the website: > Bug reference: 16968 > Logged by: Eugen Konkov > Email address: kes-kes@yandex.ru > PostgreSQL version: 13.1 > Operating system: Linux Mint 19.3 > Description: =20 > TLDR; > If I refer to same column by different ways planner may or may not recogn= ize > optimization > select * from order_total_suma() ots where agreement_id =3D 3943; =20 > -- fast > select * from order_total_suma() ots where (ots.o).agreement_id =3D 3943;= -- > slow > Where `order_total_suma` is sql function: > SELECT > sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agre= ement_id > ) AS agreement_suma, > sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agre= ement_id, > (ocd.o).id ) AS order_suma, > sum( ocd.item_cost ) OVER( PARTITION BY (ocd.o).agre= ement_id, > (ocd.o).id, (ocd.ic).consumed_period ) AS group_cost, > sum( ocd.item_suma ) OVER( PARTITION BY (ocd.o).agre= ement_id, > (ocd.o).id, (ocd.ic).consumed_period ) AS group_suma, > max( (ocd.ic).consumed ) OVER( PARTITION BY (ocd.o).agre= ement_id, > (ocd.o).id, (ocd.ic).consumed_period ) AS consumed, > ocd.item_qty, ocd.item_price, ocd.item_cost, ocd.item_su= ma, > ocd.o, ocd.c, ocd.p, ocd.ic, > (ocd.o).id as order_id, > (ocd.o).agreement_id as agreement_id > FROM order_cost_details( _target_range ) ocd > Problem is window function, because ID can not go through. But this occur > not always. > When I filter by field I partition result by then optimization occur > BUT only when I create an alias for this field and do filtering via this > alias. > Expected: apply optimization not only when I do `WHERE agreement_id =3D X= XX` > but and for `WHERE (ots.o).agreement_id =3D XXX` > Thank you. -- Best regards, Eugen Konkov --=20 Best regards, Eugen Konkov ------------00608A1780C578A95 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Re: BUG #16968: Planner does not recognize optimization<= /title> </head> <body> <span style=3D" font-family:'Tahoma'; font-size: 9pt;">Now I create minimal= reproducible test case.<br> </span><a style=3D" font-family:'Tahoma'; font-size: 9pt;" href=3D"https://= dbfiddle.uk/?rdbms=3Dpostgres_13&fiddle=3D761a00fb599789d3db31b120851d6= 341">https://dbfiddle.uk/?rdbms=3Dpostgres_13&fiddle=3D761a00fb599789d3= db31b120851d6341</a><br> <br> <span style=3D" font-family:'Consolas'; font-size: 9pt; color: #000000;">Op= timization is not applyed when I filter/partition by column using composite= type name.<br> <br> Looking at this comparison table, we can see that optimization work only wh= en I refer to column using alias:<br> <br>     (t.ag).ag_id as agreement_id  -- making an alias<br> <br> PARTITION | FILTER | IS USED?<br> ------------------------------<br> ALIAS     | ORIG   | NO<br> ALIAS     | ALIAS  | YES<br> ORIG      | ALIAS  | NO<br> ORIG      | ORIG   | NO<br> <br> link to original problem with EXPLAIN ANALYZE: </span><a style=3D" font-fam= ily:'Consolas'; font-size: 9pt;" href=3D"https://stackoverflow.com/q/674926= 73/4632019">https://stackoverflow.com/q/67492673/4632019</a><br> <span style=3D" font-family:'Consolas'; font-size: 9pt; color: #000000;">Li= nks to similar problems:<br> </span><a style=3D" font-family:'Consolas'; font-size: 9pt;" href=3D"https:= //stackoverflow.com/a/26237464/4632019">https://stackoverflow.com/a/2623746= 4/4632019</a><br> <a style=3D" font-family:'Consolas'; font-size: 9pt;" href=3D"https://stack= overflow.com/q/65780112/4632019">https://stackoverflow.com/q/65780112/46320= 19</a><br> <br> <br> <span style=3D" font-family:'Tahoma'; font-size: 9pt;">Friday, April 16, 20= 21, 10:27:35 PM, you wrote:<br> <br> </span><table style =3D" border-collapse: collapse;" cellSpacing =3D 2> <tr> <td width=3D3 bgcolor=3D #0000ff style=3D"border: 1px solid black;"><br> </td> <td style=3D"border: 1px solid black;"><span style=3D" font-family:'Tahoma'= ; font-size: 9pt;">Now I attarch plans for both queries.<br> <br> tucha=3D> \out f2<br> tucha=3D>  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) = select * from order_total_suma() ots where agreement_id =3D 3943;<br> tucha=3D>  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) = select * from order_total_suma() ots where (ots.o).agreement_id =3D 3943;<b= r> tucha=3D>  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select * from= order_total_suma() ots where agreement_id =3D 3943;<br> tucha=3D>  EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS) select * from= order_total_suma() ots where (ots.o).agreement_id =3D 3943;<br> <br> <br> <br> Friday, April 16, 2021, 10:18:45 PM, you wrote:<br> <br> <span style=3D" color: #800000;"><b>> The following bug has been logged = on the website:<br> <br> > Bug reference:      16968<br> > Logged by:          Eugen Konkov<br> > Email address:      </b></span></span><a style=3D" font= -family:'tahoma'; font-size: 9pt;" href=3D"mailto:kes-kes@yandex.ru">kes-ke= s@yandex.ru</a><br> <span style=3D" font-family:'tahoma'; font-size: 9pt; color: #800000;"><b>&= gt; PostgreSQL version: 13.1<br> > Operating system:   Linux Mint 19.3<br> > Description:        <br> <br> > TLDR;<br> > If I refer to same column by different ways planner may or may not rec= ognize<br> > optimization<br> <br> > select * from order_total_suma() ots where agreement_id =3D 3943; &nbs= p;  <br> > -- fast<br> > select * from order_total_suma() ots where (ots.o).agreement_id =3D 39= 43;  --<br> > slow<br> <br> > Where `order_total_suma` is sql function:<br> <br> >                 SELECT<br> >                  sum( ocd= .item_suma     ) OVER( PARTITION BY (ocd.o).agreement_id<br> >        ) AS agreement_suma,<br> >                  sum( ocd= .item_suma     ) OVER( PARTITION BY (ocd.o).agreement_id,<br> > (ocd.o).id                 &nb= sp;         ) AS order_suma,<br> >                  sum( ocd= .item_cost     ) OVER( PARTITION BY (ocd.o).agreement_id,<br> > (ocd.o).id, (ocd.ic).consumed_period ) AS group_cost,<br> >                  sum( ocd= .item_suma     ) OVER( PARTITION BY (ocd.o).agreement_id,<br> > (ocd.o).id, (ocd.ic).consumed_period ) AS group_suma,<br> >                  max( (oc= d.ic).consumed ) OVER( PARTITION BY (ocd.o).agreement_id,<br> > (ocd.o).id, (ocd.ic).consumed_period ) AS consumed,<br> >                  ocd.item= _qty, ocd.item_price, ocd.item_cost, ocd.item_suma,<br> >                  ocd.o, o= cd.c, ocd.p, ocd.ic,<br> >                  (ocd.o).= id as order_id,<br> >                  (ocd.o).= agreement_id as agreement_id<br> >                 FROM order_cos= t_details( _target_range ) ocd<br> <br> > Problem is window function, because ID can not go through. But this oc= cur<br> > not always.<br> > When I filter by field I partition result by then optimization occur<b= r> > BUT only when I create an alias for this field and do filtering via th= is<br> > alias.<br> <br> > Expected: apply optimization not only when I do `WHERE agreement_id = =3D XXX`<br> <br> > but and for `WHERE (ots.o).agreement_id =3D XXX`<br> <br> > Thank you.<br> <br> <br> <br> <br> </b><span style=3D" color: #c0c0c0;"><i>--<br> Best regards,<br> Eugen Konkov</td> </tr> </table> <br> <br> <br> <span style=3D" font-family:'tahoma'; font-size: 9pt; color: #c0c0c0;"><i>-= -<br> Best regards,<br> Eugen Konkov</body></html> ------------00608A1780C578A95--