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@xxxxxxx> 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
