From: Tom Lane <tgl@sss.pgh.pa.us>
To: "Naik, Sameer" <Sameer_Naik@bmc.com>
cc: Tomas Vondra <tomas.vondra@2ndquadrant.com>,
        Deepak Somaiya <deepsom@yahoo.com>,
        "pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>,
        "Bruce Momjian" <bruce@momjian.us>,
        "david@kineticode.com" <david@kineticode.com>
Subject: Re: Generic Plans for Prepared Statement are 158155 times slower than
 Custom Plans
In-reply-to: <33f1f723e93b4b93b1d48933ac3ef434@hou-exmbprd-03.adprod.bmc.com>
References: <a77204aa4c9b45a8bc86fe511e254ff4@hou-exmbprd-03.adprod.bmc.com>
 <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>
 <1460161008.2800629.1558388254959@mail.yahoo.com>
 <20190520221631.6i47hwyodfqdk2ym@development>
 <33f1f723e93b4b93b1d48933ac3ef434@hou-exmbprd-03.adprod.bmc.com>
Comments: In-reply-to "Naik, Sameer" <Sameer_Naik@bmc.com>
	message dated "Thu, 23 May 2019 06:37:19 -0000"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <15959.1558631850.1@sss.pgh.pa.us>
Content-Transfer-Encoding: quoted-printable
Date: Thu, 23 May 2019 13:17:30 -0400
Message-ID: <15960.1558631850@sss.pgh.pa.us>
Precedence: bulk

"Naik, Sameer" <Sameer_Naik@bmc.com> writes:
> On Mon, May 20, 2019 at 09:37:34PM +0000, Deepak Somaiya wrote:
>> wow this is interesting!
>> @Tom, Bruce, David - Experts
>> Any idea why would changing the datatype would cause so much degradatio=
n - this is even when plan remains the same ,data is same.

I see nothing very exciting here.  text equality comparison reduces to
a memcmp, while citext equality comparison is quite expensive, since
it has to case-fold both inputs before it can memcmp them.

For the given test case:

> '        ->  Index Scan using i776_0_400129200_t776 on t776  (cost=3D0.4=
2..12.66 rows=3D1 width=3D52) (actual time=3D1187.686..5531.421 rows=3D48 =
loops=3D1)'
> '              Index Cond: ((c400129200 =3D $1) AND (c400127400 =3D $2))=
'
> '              Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND=
 ((c179 =3D $4) OR (c179 =3D $5) OR (c179 =3D $6) OR (c179 =3D $7) OR (c17=
9 =3D $8) OR (c179 =3D $9) OR (c179 =3D $10) OR (c179 =3D $11) OR (c179 =3D=
 $12) OR (c179 =3D $13) OR (c179 =3D $14) OR (c179 =3D $15) OR (c179 =3D $=
16) OR (c179 =3D $17) OR (c179 =3D $18) OR (c179 =3D $19) OR (c179 =3D $20=
) OR (c179 =3D $21) OR (c179 =3D $22) OR (c179 =3D $23) OR (c179 =3D $24) =
OR (c179 =3D $25) OR (c179 =3D $26) OR (c179 =3D $27) OR (c179 =3D $28) OR=
 (c179 =3D $29) OR (c179 =3D $30) OR (c179 =3D $31) OR (c179 =3D $32) OR (=
c179 =3D $33) OR (c179 =3D $34) OR (c179 =3D $35) OR (c179 =3D $36) OR (c1=
79 =3D $37) OR (c179 =3D $38) OR (c179 =3D $39) OR (c179 =3D $40) OR (c179=
 =3D $41) OR (c179 =3D $42) OR (c179 =3D $43) OR (c179 =3D $44) OR (c179 =3D=
 $45) OR (c179 =3D $46) OR (c179 =3D $47) OR (c179 =3D $48) OR (c179 =3D $=
49) OR (c179 =3D $50) OR (c179 =3D $51)))'
> '              Rows Removed by Filter: 55322'

it's reasonable to suppose that not many of the rows are failing the
c400129100 conditions, so that in order to decide that a row doesn't
pass the filter, we are forced to perform each of the OR'd c179
comparisons.  So this query did something like 48 * 55322 equality
comparisons for c179.  If the cost of a citexteq evaluation is
around 2 microseconds, that'd fully explain the runtime differential.

The OP didn't say what locale or encoding he's using.  Maybe switching
to some other settings would improve matters ... though if non-ASCII
case folding is a business requirement, that likely won't go far.

Or you could get rid of the need for the repetitive case-folding,
say by storing lower(c179) in a separate column and doing plain
text comparisons to pre-lowercased input values.

			regards, tom lane