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 1hSq1g-00076f-Ny for pgsql-performance@arkaria.postgresql.org; Mon, 20 May 2019 21:40:57 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hSq1f-0001Ql-Gz for pgsql-performance@arkaria.postgresql.org; Mon, 20 May 2019 21:40:55 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSpyY-0004Vz-OD for pgsql-performance@lists.postgresql.org; Mon, 20 May 2019 21:37:43 +0000 Received: from sonic315-14.consmr.mail.bf2.yahoo.com ([74.6.134.124]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hSpyV-0001nz-O0 for pgsql-performance@lists.postgresql.org; Mon, 20 May 2019 21:37:42 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1558388257; bh=uIR0aXhBsPYJRHRJlkr7HPjZthSljw7rXUKRMKn8cNY=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=L3uwXABqldERki1dLe27+YMISfrGPBN+yHDatUlEFF5Namf+pGCYv4duyws4uZ6wsMEg9unQGTb1RAVEs5mK/DOh7pP6fOi2Uz8i4zHB6+mhZcuiR36OWi2frGhHLvC+dvQDmFAvmqAtMhAupG/tnnl45QWmhdyeAGqMvEToPx+/aBAlXb1io/Aaceg/R7lnG2OAzQ+bsexC8i91VTZkfJrDnfRSVl7B4BePRSva18SVDIpo9J3H0Y5f2n6FSlhq9u1c5n9vk0RucpwEWluhZA1PYTXhOFiH2FhLz9ELFSQlT65a9ABFELJ/SgKJ9KFvmICI2lVgIHwIAAOBjctMXg== X-YMail-OSG: hDJnyxUVM1lIB7ObjKOtJ.S_Lsx3gJsv7c_n2Liz4VlUkQHt3u_Lux8GDPAQ0xV f2bkSdWjn1eumozieHa_2vWJ8dTqWz.VxzGE9ssfZEUq8HQRqcapd0dfWs5b408FhfL6V39JFe6i 6fn5k87TwKtfzTXM5PvZTAmaNRylYD0Of.r7M8KfgS4hvDuIHUjYtN.v1IYlVx9_xYiDvTLTE2hL ZKTIpoGWxgc3eIxKM6AJZf6zBTfswBcZfwQnOtH71Obqgb9mTFYqKsguUbCuYb2kC16z23VwteI5 4QTD4q2dy2VnFM3Dlqzrqw27BCaxv5md0scEfOs0l3pPNthcYlS0Wz2_QoLoDPWx63E5eABslCYT 0.T9C8H47q205v8JimzJPcNIgF7DmCo0QD5306ABV_P_1jy._1ccbi.Y_ZYAdONlmY5OnxhVrGiS xpua0rUUPsA6jwnWjY6LTsmmy7AVg3MivZmCSogYd2mY7SyCzuJqpXCXj.lzRq70Zz10fLhO0Mme 2o0pBsDKLYw5N4cfbXfwIqEmleNy9NFJlAQnpAnF5vJJqboJzJWFNRo1gUjuuao_Z4QcKhy9WiRb rHAVsyT60Zf9h.I9Hb02uvilc23PZhy3I0mUZDsuolsSN10H30dAyH5F706qcAAzEL4bceIBYa5m oi6Pz4YfCC9OM4yLWflLVG.8feCpCS_a_OlQ67quhNhYeex._QZHAh0AfORBYvRAo6wo.CvE7Wag By0.GZ.0E9gni_sI.4x3swVo3MJMnnDl5K368wxN5tyFLxwCs80zGIIEb9SDWDhKF1NtaddB1F3x I3h8XiUsK54xibnqsQZ9Mpuikn4jjv0sr7piSHO8TweS_.4mDvk_SWhRWuO1ZaTj9hetGgFZ79aG CcrxibDh3RcSeJbyJ46rjimEF8dvboiYMaQ61vYZXUju4DnyKhGOhCYG7wPUvI5kBPFXosaASEI7 Td3uZ1ETwS15rODPV3vUUVvmwD3zidJmN7LOXx7x5iuz2DfyBDJbsjK2JjmWZAaiWS1rtkIjJQ6K sAkFUIIqqJcql95EQ38aAeXOaHRdr0z.1FWmWrIKDR8nGrhTLZNYHvmWVoNrqNycuEEN4YnJO9W1 PGbtHUpPyP9.DLwSS1u3o1O9T2hIZYQjr.Ld3qx.UFvE6.D2qlqu6g2Etak6v.1xsl_XlxIzIQ_t 7Qke.3zl4TTQv Received: from sonic.gate.mail.ne1.yahoo.com by sonic315.consmr.mail.bf2.yahoo.com with HTTP; Mon, 20 May 2019 21:37:37 +0000 Date: Mon, 20 May 2019 21:37:34 +0000 (UTC) From: Deepak Somaiya To: Tom Lane , Bruce Momjian , "david@kineticode.com" , "Naik, Sameer" Cc: "pgsql-performance@lists.postgresql.org" Message-ID: <1460161008.2800629.1558388254959@mail.yahoo.com> In-Reply-To: <4e741138fe5944b68328a05bf095f68e@hou-exmbprd-03.adprod.bmc.com> References: <20190429133607.3jh7bqx4vznuhok7@momjian.us> <22414.1556548539@sss.pgh.pa.us> <16b3416fb88b48a382df0145bdd76f19@hou-exmbprd-03.adprod.bmc.com> <670927089.1909643.1557375255783@mail.yahoo.com> <4e741138fe5944b68328a05bf095f68e@hou-exmbprd-03.adprod.bmc.com> Subject: Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_2800628_1772506791.1558388254956" X-Mailer: WebService/1.1.13634 YMailNorrin Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/65.0.3325.162 Safari/537.36 Content-Length: 17826 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2800628_1772506791.1558388254956 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable wow this is interesting!=C2=A0 @Tom, Bruce, David - Experts Any idea why would changing the datatype would cause so much degradation - = this is even when plan remains the same ,data is same. Deepak On Friday, May 17, 2019, 2:36:05 AM PDT, Naik, Sameer wrote: =20 =20 =20 Deepak, =20 I changed the datatype from citext to text and now everything works fine. =20 The data distribution is same, plan is same, yet there is a huge performanc= e degradation when citext is used instead of text. =20 However the business case requires case insensitive string handling. =20 I am looking forward to some expert advice here when dealing with citext da= ta type. =20 =C2=A0 =20 =C2=A0 =20 -Thanks and Regards, =20 Sameer Naik =20 =C2=A0 =20 From: Deepak Somaiya [mailto:deepsom@yahoo.com]=20 Sent: Thursday, May 9, 2019 9:44 AM To: Tom Lane ; Bruce Momjian ; Naik, S= ameer Cc: pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Re: Generic Plans for Prepared Statement are 158155= times slower than Custom Plans =20 =C2=A0 =20 Sameer, =20 were you able to resolve it? =20 =C2=A0 =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 =C2=A0 =20 Deepak =20 =C2=A0 =20 =C2=A0 =20 On Tuesday, April 30, 2019, 1:27:14 AM PDT, Naik, Sameer wrote: =20 =C2=A0 =20 =C2=A0 =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 =20 -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 29, 2019 8:06 PM To: Bruce Momjian Cc: Naik, Sameer ;pgsql-performance@lists.postgresql.o= rg 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_2800628_1772506791.1558388254956 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit
wow this is interesting! 

@Tom, Bruce, David - Experts

Any idea why would changing the datatype would cause so much degradation - this is even when plan remains the same ,data is same.

Deepak

On Friday, May 17, 2019, 2:36:05 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote:


Deepak,

I changed the datatype from citext to text and now everything works fine.

The data distribution is same, plan is same, yet there is a huge performance degradation when citext is used instead of text.

However the business case requires case insensitive string handling.

I am looking forward to some expert advice here when dealing with citext data type.

 

 

-Thanks and Regards,

Sameer Naik

 

From: Deepak Somaiya [mailto:deepsom@yahoo.com]
Sent: Thursday, May 9, 2019 9:44 AM
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Naik, Sameer <Sameer_Naik@bmc.com>
Cc: pgsql-performance@lists.postgresql.org
Subject: [EXTERNAL] Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

 

Sameer,

were you able to resolve it?

 

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

 

Deepak

 

 

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

 

 

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

>Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext)

>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.  But, when considering the generic case

>      ->  Index Scan using i776_0_400129200_t776 on t776  (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1)
>            Index Cond: ((c400129200 = $1) AND (c400127400 = $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 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 for which the value of c400129200 is 0. For each of the remaining 55,373 records 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'  .


-Thanks and Regards,
Sameer Naik


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

Bruce Momjian <bruce@momjian.us> writes:
> On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote:
>> Since Postgres 9.2, for prepared statements, the CBO automatically
>> switches 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 quite common in the dataset, so that when considering

Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext)

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.  But, when considering the generic case

      ->  Index Scan using i776_0_400129200_t776 on t776  (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1)
            Index Cond: ((c400129200 = $1) AND (c400127400 = $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 cheaper, so it goes with this plan.  I wonder what the actual distribution of those keys is.


In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would produce a better estimate.  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 cost estimates that are actually cheaper than the custom plans, on the grounds that they must be estimation errors.  In principle a generic plan could 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 are 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.  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 reported before).

However, I'm not sure how to tune this idea so that it doesn't end up rejecting perfectly good generic plans.  It's likely that there will be some variation in the cost estimates between the generic and specific cases, even if the plan structure is exactly the same; and that variation could go in either direction.

            regards, tom lane

------=_Part_2800628_1772506791.1558388254956--