Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hOaRq-0005ZF-S0 for pgsql-performance@arkaria.postgresql.org; Thu, 09 May 2019 04:14:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hOaRp-0007UZ-L3 for pgsql-performance@arkaria.postgresql.org; Thu, 09 May 2019 04:14:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hOaRp-0007S0-3X for pgsql-performance@lists.postgresql.org; Thu, 09 May 2019 04:14:21 +0000 Received: from sonic303-1.consmr.mail.bf2.yahoo.com ([74.6.131.40]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hOaRm-0006M5-8l for pgsql-performance@lists.postgresql.org; Thu, 09 May 2019 04:14:19 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1557375256; bh=RmCWdYLgwMd/T0vQgkT48gmGw0hWe3fgCiJJ3Ha+F9g=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=nqe13B5BqE8peyBmeHmoCU3Z0CufxPUH+ugsMGJj1oLxx8panbZCLUaZNYAXg1nQLp2H7tjNMMdeJ/DJyRiAUodt6Jo1P33Ilkf1wZbQoBzVF1XetZY+/yV0Qo+iEaaXsEMX5VtWTVfXn8odjSz5c0DFbmUgvpPLaoayY5tt75ZHdxEXK5w7CDDnSGT7+hFp0q8HCuh6Xbcl2GurfkUTd25T9KkJuRJzTMFTXJWAMrOp//Iefy4mEUOzDSKvHOeIG36qhwdsJo6yX0GmWeulH2rXxwMt4Y8Q96QcHKMpDnyegWwT1j4AF2rzRnOYtz3Z+819WwegOTr2r9gCuH4f6w== X-YMail-OSG: uBXfmh8VM1lh_5kcWTU8xNpGdFYV6dH4.mvbXExlKwtFQDtmKR1m.fhebTumiUD by88tMEUycBe_k_FAl6n7gVZERcJ8ZfSznvGne7z4lZKwBkLIpMUAABclQDxNy9QVrOvs55Fl50g 6oMojsnpH4ztbgJWRRgQvKRBLGAyIRyRR.LtnYIoOo4f4QTWXL4KMUkdzMd709rjfpTMTPlxdtKJ qgak5lRaiNCJYbbUvJARvvzo09Q_RCI0vNZg1rRpj86vnBZRFy3ceTLHaBgi36.uAEXwFUJvHOCk BxOxu22k4fZlO7T797TyfdXC6P1i5CcL5beq__Ep4IG6RV7j0oh9VnZlzdLtmdMo15Ti7MKKq5CL 2cps2rpdb11klmrwIlwO78njRZLBtMSERfNfIrRHeDnksI1bBK7iCBMTFL.EAkY3qs.iHQ1f0U_O 0Ki.I.8Z16wiDfg4XaEtPswCUdj1oaO6EMTspCleQ97_I0i20hIbtNO8UvJbYuNspepUKef8iIQO hMdrrhXWAbWVRAqKyqueUVa0biPr6hR4CQZZJrtOrgsQj7Ssp.mygHKqaFurvc7sVQUn9zHOe2z0 TRb9dIE2WhvSm4h8bxIn7eRkgb6pTuOYKKMzCkJjSE_KUge_4ttIeAA4ywJW9xLWBJOOhrTfD7vf 8jtjw4boryOhZ69RDTFLdMG3F1Trwxizp8M5rVDB.6Ziou9uQyeAGKqVirbo_uQnTNf5KO9SVo9_ 6OJB49oV7vrCYWwc_ztjzR3hlZwpnT_rBXgXJKJDAzQLo6L6VoYuDWcGDfR16CpYHKaWDuYz.pgY y.5r3xUYgSMXZ8XRwvPbKlNxn8yCo8ju6fy79CW.AkQ4KpWcPRARJZG0dn3eiVi1R1rn6DNTR.4o q8yuTbQ90eifUptKk0gvjYy9j9HmIxofDbXw9zo.KAGlveEzQAAgodGFmSw07LL3yqMSmdYpuIIS PG1rl.0po9fQbh.V.u4aWJzeljsMUlT9BRrNCCDdybDcn8XbpcTR0eMfR4YWVZCETQyY597iE9zJ eOtx73.fZ3xafk_jVIEqL0oTdRd25nMuVRnfKQPUnYs3QzFlg.UI74XHPnRYUU_2EoasReVVtm2F cLpJNw4EjuW0Z98hgjmNN4n1HutcmbcUACxHn8U44YKQG.w.GJzpld7zr2LFqplKmuwNjTfYw9Rr 33ZhqVEJrObsIBgcbENR2EkvlCKsK1g-- Received: from sonic.gate.mail.ne1.yahoo.com by sonic303.consmr.mail.bf2.yahoo.com with HTTP; Thu, 9 May 2019 04:14:16 +0000 Date: Thu, 9 May 2019 04:14:15 +0000 (UTC) From: Deepak Somaiya To: Tom Lane , Bruce Momjian , "Naik, Sameer" Cc: "pgsql-performance@lists.postgresql.org" Message-ID: <670927089.1909643.1557375255783@mail.yahoo.com> In-Reply-To: <16b3416fb88b48a382df0145bdd76f19@hou-exmbprd-03.adprod.bmc.com> References: <20190429133607.3jh7bqx4vznuhok7@momjian.us> <22414.1556548539@sss.pgh.pa.us> <16b3416fb88b48a382df0145bdd76f19@hou-exmbprd-03.adprod.bmc.com> Subject: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1909642_1746925276.1557375255781" X-Mailer: WebService/1.1.13583 YMailNorrin Mozilla/5.0 (Windows NT 6.1; Win64; x64; rv:66.0) Gecko/20100101 Firefox/66.0 Content-Length: 12989 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1909642_1746925276.1557375255781 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Sameer,were you able to resolve it?=20 I am not sure if this is very common in postges=C2=A0 - I doubt though but = have not seen such a drastic performance degradation and that too when plan= ner making the call.=C2=A0=20 Deepak On Tuesday, April 30, 2019, 1:27:14 AM PDT, Naik, Sameer wrote: =20 =20 >The problem seems to be that the actual values being used for >c400129200 and c400127400 are quite common in the dataset, so that when co= nsidering >Filter: ... (c400129200 =3D '0'::citext) AND (c400127400 =3D 'DATASET1M'::= citext) >the planner makes a roughly correct assessment that there are a lot of suc= h rows, so it prefers to index on the basis of the giant OR clause instead,= even though that's fairly expensive.=C2=A0 But, when considering the gener= ic case >=C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using i776_0_400129200_t776 on t7= 76=C2=A0 (cost=3D0.42..12.66 rows=3D1 width=3D52) (actual time=3D1190.399..= 5544.385 rows=3D48 loops=3D1) >=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: ((c400129200 =3D $1)= AND (c400127400 =3D $2)) > it's evidently guessing that just a few rows will match the index conditi= on (no more than about 3 given the cost number), making this plan look much= cheaper, so it goes with this plan.=C2=A0 I wonder what the actual distrib= ution of those keys is. Distribution of the keys c400129200 and c400127400 . The distribution of c400129200 is as follows-=20 In entire table having 110743 records, there are 55370 records for which th= e value of c400129200 is 0. For each of the remaining 55,373 records the va= lue of c400129200 is distinct. The distribution of c400127400 is as follows-=20 In entire table having 110743 records, there are 55370 records for which th= e value of c400127400 is=C2=A0 'DATASET1M' . For remaining 55,373 records t= he value of c400127400 the value is same and is ' 'DATASET2M'=C2=A0 . -Thanks and Regards, Sameer Naik -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20 Sent: Monday, April 29, 2019 8:06 PM To: Bruce Momjian Cc: Naik, Sameer ; pgsql-performance@lists.postgresql.= org Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 ti= mes slower than Custom Plans Bruce Momjian writes: > On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: >> Since Postgres 9.2, for prepared statements, the CBO automatically=20 >> switches from Custom Plan to Generic plan on the sixth iteration=20 >> (reference backend/ utils/cache/plancache.c). > This is not totally true. Yeah, that's a pretty inaccurate statement of the behavior. The problem seems to be that the actual values being used for c400129200 and c400127400 are quite common in the dataset, so that when con= sidering Filter: ... (c400129200 =3D '0'::citext) AND (c400127400 =3D 'DATASET1M'::c= itext) the planner makes a roughly correct assessment that there are a lot of such= rows, so it prefers to index on the basis of the giant OR clause instead, = even though that's fairly expensive.=C2=A0 But, when considering the generi= c case =C2=A0 =C2=A0 =C2=A0 ->=C2=A0 Index Scan using i776_0_400129200_t776 on t77= 6=C2=A0 (cost=3D0.42..12.66 rows=3D1 width=3D52) (actual time=3D1190.399..5= 544.385 rows=3D48 loops=3D1) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Index Cond: ((c400129200 =3D $1) = AND (c400127400 =3D $2)) it's evidently guessing that just a few rows will match the index condition= (no more than about 3 given the cost number), making this plan look much c= heaper, so it goes with this plan.=C2=A0 I wonder what the actual distribut= ion of those keys is. In v10 and later, it's quite possible that creating extended stats on the c= ombination of those two columns would produce a better estimate.=C2=A0 Won'= t help OP on 9.6, though. This isn't the first time we've seen a plan-choice failure of this sort. I've wondered if we should make the plancache simply disbelieve generic cos= t estimates that are actually cheaper than the custom plans, on the grounds= that they must be estimation errors.=C2=A0 In principle a generic plan cou= ld never really be better than a custom plan; so if it looks that way on a = cost basis, what that probably means is that the actual parameter values ar= e outliers of some sort (e.g. extremely common), and the custom plan "knows= " that it's going to be taking a hit from that, but the generic plan doesn'= t.=C2=A0 In this sort of situation, going with the generic plan could be re= ally disastrous, which is exactly what the OP is seeing (and what we've see= n reported before). However, I'm not sure how to tune this idea so that it doesn't end up rejec= ting perfectly good generic plans.=C2=A0 It's likely that there will be som= e variation in the cost estimates between the generic and specific cases, e= ven if the plan structure is exactly the same; and that variation could go = in either direction. =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 =C2=A0=C2=A0=C2=A0 regards, tom lane =20 ------=_Part_1909642_1746925276.1557375255781 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Sameer,
w= ere you able to resolve it?

I am not sure if = this is very common in postges  - I doubt though but have not seen suc= h a drastic performance degradation and that too when planner making the ca= ll. 

Deepak


=20
On Tuesday, April 30, 2019, 1:27:14 AM PDT, Naik, Samee= r <Sameer_Naik@bmc.com> wrote:


>The problem seems to be that the = actual values being used for
>c400129200 and c40012740= 0 are quite common in the dataset, so that when considering

>Filter: ... (c400129200 =3D '0'::citext) AND (c40= 0127400 =3D 'DATASET1M'::citext)

>t= he planner makes a roughly correct assessment that there are a lot of such = rows, so it prefers to index on the basis of the giant OR clause instead, e= ven though that's fairly expensive.  But, when considering the generic= case

>      ->&= nbsp; Index Scan using i776_0_400129200_t776 on t776  (cost=3D0.42..12= .66 rows=3D1 width=3D52) (actual time=3D1190.399..5544.385 rows=3D48 loops= =3D1)
>            Inde= x Cond: ((c400129200 =3D $1) AND (c400127400 =3D $2))
> it's evidently guessing that just a few rows will matc= h the index condition (no more than about 3 given the cost number), making = this plan look much cheaper, so it goes with this plan.  I wonder what= the actual distribution of those keys is.

Distribution of the keys c400129200 and c400127400 .

The distribution of c400129200 is as follows-
In entire table having 110743 records, there are 55370 records fo= r which the value of c400129200 is 0. For each of the remaining 55,373 reco= rds the value of c400129200 is distinct.


The distribution of c400127400 is as follows-
In entire table having 110743 records, there are 55370 records = for which the value of c400127400 is  'DATASET1M' . For remaining 55,= 373 records the value of c400127400 the value is same and is ' 'DATASET2M'&= nbsp; .


-Thanks and= Regards,
Sameer Naik

----= -Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, April 29, 2019 8:0= 6 PM
To: Bruce Momjian <bruce@momjian.us>
Cc: Naik, Sameer <Sameer_Naik@bmc.= com>; pgsql-performance@lists.postg= resql.org
Subject: [EXTERNAL] Re: Generic> Plans f= or Prepared Statement are 158155 times slower than Custom Plans

Bruce Momjian <bruce@momjian.us= > writes:
> On Mon, Apr 29, 2019 at 10:36:20AM +000= 0, Naik, Sameer wrote:
>> Since Postgres 9.2, for p= repared statements, the CBO automatically
>> switc= hes from Custom Plan to Generic plan on the sixth iteration
>> (reference backend/ utils/cache/plancache.c).

> This is not totally true.

Yeah, that's a pretty inaccurate statement of the behavior.
The problem seems to be that the actual= values being used for
c400129200 and c400127400 are quit= e common in the dataset, so that when considering

Filter: ... (c400129200 =3D '0'::citext) AND (c400127400 =3D 'D= ATASET1M'::citext)

the planner makes a= roughly correct assessment that there are a lot of such rows, so it prefer= s to index on the basis of the giant OR clause instead, even though that's = fairly expensive.  But, when considering the generic case

      ->  Index Scan using= i776_0_400129200_t776 on t776  (cost=3D0.42..12.66 rows=3D1 width=3D5= 2) (actual time=3D1190.399..5544.385 rows=3D48 loops=3D1)
            Index Cond: ((c400129200 =3D $1= ) AND (c400127400 =3D $2))

it's eviden= tly guessing that just a few rows will match the index condition (no more t= han about 3 given the cost number), making this plan look much cheaper, so = it goes with this plan.  I wonder what the actual distribution of thos= e keys is.


In v10 a= nd later, it's quite possible that creating extended stats on the combinati= on of those two columns would produce a better estimate.  Won't help O= P on 9.6, though.

This isn't the first= time we've seen a plan-choice failure of this sort.
I've= wondered if we should make the plancache simply disbelieve generic cost es= timates that are actually cheaper than the custom plans, on the grounds tha= t they must be estimation errors.  In principle a generic plan could n= ever really be better than a custom plan; so if it looks that way on a cost= basis, what that probably means is that the actual parameter values are ou= tliers of some sort (e.g. extremely common), and the custom plan "knows" th= at it's going to be taking a hit from that, but the generic plan doesn't.&n= bsp; In this sort of situation, going with the generic plan could be really= disastrous, which is exactly what the OP is seeing (and what we've seen re= ported before).

However, I'm not sure = how to tune this idea so that it doesn't end up rejecting perfectly good ge= neric plans.  It's likely that there will be some variation in the cos= t estimates between the generic and specific cases, even if the plan struct= ure is exactly the same; and that variation could go in either direction.
        &= nbsp;   regards, tom lane

------=_Part_1909642_1746925276.1557375255781--