Date: Tue, 21 May 2019 00:16:31 +0200
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Deepak Somaiya <deepsom@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <bruce@momjian.us>,
	"david@kineticode.com" <david@kineticode.com>,
	"Naik, Sameer" <Sameer_Naik@bmc.com>,
	"pgsql-performance@lists.postgresql.org"
 <pgsql-performance@lists.postgresql.org>
Subject: Re: Generic Plans for Prepared Statement are 158155 times slower
 than Custom Plans
Message-ID: <20190520221631.6i47hwyodfqdk2ym@development>
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>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1; format=flowed
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1460161008.2800629.1558388254959@mail.yahoo.com>
User-Agent: NeoMutt/20180716-1444-295967
Precedence: bulk

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