From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Deepak Somaiya <deepsom@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <bruce@momjian.us>
Cc: david@kineticode.com <david@kineticode.com>
Cc: Naik, Sameer <Sameer_Naik@bmc.com>
Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
Subject: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Date: Tue, 21 May 2019 00:16:31 +0200
Message-ID: <20190520221631.6i47hwyodfqdk2ym@development> (raw)
In-Reply-To: <1460161008.2800629.1558388254959@mail.yahoo.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>
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 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.
>
>
It's generally a good idea to share explain analyze output for both
versions of the query - both with citext and text.
regards
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, deepsom@yahoo.com, tgl@sss.pgh.pa.us, bruce@momjian.us, david@kineticode.com, Sameer_Naik@bmc.com, pgsql-performance@lists.postgresql.org
Subject: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
In-Reply-To: <20190520221631.6i47hwyodfqdk2ym@development>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox