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 <[email protected]>
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:[email protected]]
Sent: Thursday, May 9, 2019 9:44 AM
To: Tom Lane <[email protected]>; Bruce Momjian <[email protected]>; Naik,
Sameer <[email protected]>
Cc: [email protected]
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 <[email protected]>
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:[email protected]]
Sent: Monday, April 29, 2019 8:06 PM
To: Bruce Momjian <[email protected]>
Cc: Naik, Sameer <[email protected]>;[email protected]
Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times
slower than Custom Plans
Bruce Momjian <[email protected]> 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
