pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
6+ messages / 4 participants
[nested] [flat]
* Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
@ 2019-05-09 04:14 Deepak Somaiya <deepsom@yahoo.com>
2019-05-17 06:42 ` RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
0 siblings, 1 reply; 6+ messages in thread
From: Deepak Somaiya @ 2019-05-09 04:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Naik, Sameer <Sameer_Naik@bmc.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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 <Sameer_Naik@bmc.com> 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:tgl@sss.pgh.pa.us]
Sent: Monday, April 29, 2019 8:06 PM
To: Bruce Momjian <bruce@momjian.us>
Cc: Naik, Sameer <Sameer_Naik@bmc.com>; pgsql-performance@lists.postgresql.org
Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans
Bruce Momjian <bruce@momjian.us> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
2019-05-09 04:14 Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
@ 2019-05-17 06:42 ` Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-20 21:37 ` Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
0 siblings, 1 reply; 6+ messages in thread
From: Naik, Sameer @ 2019-05-17 06:42 UTC (permalink / raw)
To: Deepak Somaiya <deepsom@yahoo.com>; Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; david@kineticode.com <david@kineticode.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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:deepsom@yahoo.com]
Sent: Thursday, May 9, 2019 9:44 AM
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Naik, Sameer <Sameer_Naik@bmc.com>
Cc: pgsql-performance@lists.postgresql.org
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 <Sameer_Naik@bmc.com<mailto:Sameer_Naik@bmc.com>> 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:tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>]
Sent: Monday, April 29, 2019 8:06 PM
To: Bruce Momjian <bruce@momjian.us<mailto:bruce@momjian.us>>
Cc: Naik, Sameer <Sameer_Naik@bmc.com<mailto:Sameer_Naik@bmc.com>>; pgsql-performance@lists.postgresql.org<mailto:pgsql-performance@lists.postgresql.org>
Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans
Bruce Momjian <bruce@momjian.us<mailto:bruce@momjian.us>> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
2019-05-09 04:14 Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-17 06:42 ` RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
@ 2019-05-20 21:37 ` Deepak Somaiya <deepsom@yahoo.com>
2019-05-20 22:16 ` Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 1 reply; 6+ messages in thread
From: Deepak Somaiya @ 2019-05-20 21:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; david@kineticode.com <david@kineticode.com>; Naik, Sameer <Sameer_Naik@bmc.com>; +Cc: pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>
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.
-Thanks and Regards,
Sameer Naik
From: Deepak Somaiya [mailto:deepsom@yahoo.com]
Sent: Thursday, May 9, 2019 9:44 AM
To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Naik, Sameer <Sameer_Naik@bmc.com>
Cc: pgsql-performance@lists.postgresql.org
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 <Sameer_Naik@bmc.com> 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:tgl@sss.pgh.pa.us]
Sent: Monday, April 29, 2019 8:06 PM
To: Bruce Momjian <bruce@momjian.us>
Cc: Naik, Sameer <Sameer_Naik@bmc.com>;pgsql-performance@lists.postgresql.org
Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans
Bruce Momjian <bruce@momjian.us> 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
2019-05-09 04:14 Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-17 06:42 ` RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-20 21:37 ` Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
@ 2019-05-20 22:16 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-05-23 06:37 ` RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
0 siblings, 1 reply; 6+ messages in thread
From: Tomas Vondra @ 2019-05-20 22:16 UTC (permalink / raw)
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>
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
2019-05-09 04:14 Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-17 06:42 ` RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-20 21:37 ` Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-20 22:16 ` Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Tomas Vondra <tomas.vondra@2ndquadrant.com>
@ 2019-05-23 06:37 ` Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-23 17:17 ` Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Naik, Sameer @ 2019-05-23 06:37 UTC (permalink / raw)
To: Tomas Vondra <tomas.vondra@2ndquadrant.com>; Deepak Somaiya <deepsom@yahoo.com>; pgsql-performance@lists.postgresql.org <pgsql-performance@lists.postgresql.org>; Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; david@kineticode.com <david@kineticode.com>
@Tom, Bruce, David
>> It's generally a good idea to share explain analyze output for both versions of the query - both with citext and text.
Below are the queries and explain plan output(custom plan and generic plan) for both versions (with citext and text)
Case Insensitive -
PREPARE slowQuery (citext,citext,int,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext) as
SELECT
T776.C179,
T776.C1
FROM
T776
WHERE
(
(T776.C400129200 = $1)
AND
(
T776.C400127400 = $2
)
AND
(
(T776.C400129100 <> $3)
OR
(
T776.C400129100 IS NULL
)
)
AND
(
(T776.C179 = $4)
OR
(
T776.C179 = $5
)
OR
(
T776.C179 = $6
)
OR
(
T776.C179 = $7
)
OR
(
T776.C179 = $8
)
OR
(
T776.C179 = $9
)
OR
(
T776.C179 = $10
)
OR
(
T776.C179 = $11
)
OR
(
T776.C179 = $12
)
OR
(
T776.C179 = $13
)
OR
(
T776.C179 = $14
)
OR
(
T776.C179 = $15
)
OR
(
T776.C179 = $16
)
OR
(
T776.C179 = $17
)
OR
(
T776.C179 = $18
)
OR
(
T776.C179 = $19
)
OR
(
T776.C179 = $20
)
OR
(
T776.C179 = $21
)
OR
(
T776.C179 = $22
)
OR
(
T776.C179 = $23
)
OR
(
T776.C179 = $24
)
OR
(
T776.C179 = $25
)
OR
(
T776.C179 = $26
)
OR
(
T776.C179 = $27
)
OR
(
T776.C179 = $28
)
OR
(
T776.C179 = $29
)
OR
(
T776.C179 = $30
)
OR
(
T776.C179 = $31
)
OR
(
T776.C179 = $32
)
OR
(
T776.C179 = $33
)
OR
(
T776.C179 = $34
)
OR
(
T776.C179 = $35
)
OR
(
T776.C179 = $36
)
OR
(
T776.C179 = $37
)
OR
(
T776.C179 = $38
)
OR
(
T776.C179 = $39
)
OR
(
T776.C179 = $40
)
OR
(
T776.C179 = $41
)
OR
(
T776.C179 = $42
)
OR
(
T776.C179 = $43
)
OR
(
T776.C179 = $44
)
OR
(
T776.C179 = $45
)
OR
(
T776.C179 = $46
)
OR
(
T776.C179 = $47
)
OR
(
T776.C179 = $48
)
OR
(
T776.C179 = $49
)
OR
(
T776.C179 = $50
)
OR
(
T776.C179 = $51
)
)
)
ORDER BY
T776.C1 ASC LIMIT 2001 OFFSET 0
select count(*) from T776 where C400129200='0'
Explain (analyze,buffers) Execute slowQuery('0'::citext,'DATASET1M'::citext, 1,'OI-d791e838d0354ea59aa1c04622b7c8be'::citext, 'OI-44502144c7be49f4840d9d30c724f11b'::citext, 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext, 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext, 'OI-9239a9fa93c9459387d564940c0b4289'::citext, 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext, 'OI-8e365fa8461043a69950a638d3f3830a'::citext, 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext, 'OI-df0d9473d3934de29435d1c22fc9a269'::citext, 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext, 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext, 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext, 'OI-d0c049f6459e4174bb4e2ea025104298'::citext, 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext, 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext, 'OI-4316868d400d450fb60bb620a89778f2'::citext, 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext, 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext, 'OI-02577caeab904f37b6d13bb761805e02'::citext, 'OI-ecde76cbefd847ed9602a2c875529123'::citext, 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext, 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext, 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext, 'OI-81071273eacc44c4a46180be3a7d6a04'::citext, 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext, 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext, 'OI-7fc180b8d2944391b41ed90d70915357'::citext, 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext, 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext, 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext, 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext, 'OI-78263146f1694c39935578c3fa4c6415'::citext, 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext, 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext, 'OI-83e223fa1b364ac8b20e396b21387758'::citext, 'OI-a6eb0ec674d242b793a26b259d15435f'::citext, 'OI-195dfbe207a64130b3bc686bfdabe051'::citext, 'OI-7ba86277cbce489694ba03c98e7d2059'::citext, 'OI-c7675935bd974244939ccac9181d9129'::citext, 'OI-64c958575289438bb86455ed81517df1'::citext, 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext, 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext, 'OI-42de43dda54a4a018c0038c0de241da1'::citext, 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext, 'OI-56e85efaaa5f42c0913fed3745687a23'::citext, 'OI-def2602379db49cfadf6c31d7dfc4872'::citext, 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext, 'OI-6f3333da01f349a3a17a5714a82530a6'::citext)
Custom Plan for Case Insensitive ---
'Limit (cost=402.71..402.74 rows=12 width=52) (actual time=4.724..4.803 rows=48 loops=1)'
' Buffers: shared hit=139 read=53'
' -> Sort (cost=402.71..402.74 rows=12 width=52) (actual time=4.720..4.747 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' Buffers: shared hit=139 read=53'
' -> Bitmap Heap Scan on t776 (cost=212.54..402.49 rows=12 width=52) (actual time=3.715..4.040 rows=48 loops=1)'
' Recheck Cond: ((c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::citext) OR (c179 = 'OI-44502144c7be49f4840d9d30c724f11b'::citext) OR (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext) OR (c179 = 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext) OR (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::citext) OR (c179 = 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext) OR (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::citext) OR (c179 = 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext) OR (c179 = 'OI-df0d9473d3934de29435d1c22fc9a269'::citext) OR (c179 = 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext) OR (c179 = 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext) OR (c179 = 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext) OR (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::citext) OR (c179 = 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext) OR (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext) OR (c179 = 'OI-4316868d400d450fb60bb620a89778f2'::citext) OR (c179 = 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext) OR (c179 = 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext) OR (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::citext) OR (c179 = 'OI-ecde76cbefd847ed9602a2c875529123'::citext) OR (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext) OR (c179 = 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext) OR (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext) OR (c179 = 'OI-81071273eacc44c4a46180be3a7d6a04'::citext) OR (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext) OR (c179 = 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext) OR (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::citext) OR (c179 = 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext) OR (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext) OR (c179 = 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext) OR (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext) OR (c179 = 'OI-78263146f1694c39935578c3fa4c6415'::citext) OR (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext) OR (c179 = 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext) OR (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::citext) OR (c179 = 'OI-a6eb0ec674d242b793a26b259d15435f'::citext) OR (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::citext) OR (c179 = 'OI-7ba86277cbce489694ba03c98e7d2059'::citext) OR (c179 = 'OI-c7675935bd974244939ccac9181d9129'::citext) OR (c179 = 'OI-64c958575289438bb86455ed81517df1'::citext) OR (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext) OR (c179 = 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext) OR (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext) OR (c179 = 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext) OR (c179 = 'OI-56e85efaaa5f42c0913fed3745687a23'::citext) OR (c179 = 'OI-def2602379db49cfadf6c31d7dfc4872'::citext) OR (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext) OR (c179 = 'OI-6f3333da01f349a3a17a5714a82530a6'::citext))'
' Filter: (((c400129100 <> 1) OR (c400129100 IS NULL)) AND (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext))'
' Heap Blocks: exact=39'
' Buffers: shared hit=131 read=53'
' -> BitmapOr (cost=212.54..212.54 rows=48 width=0) (actual time=3.690..3.690 rows=0 loops=1)'
' Buffers: shared hit=92 read=53'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.157..0.157 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::citext)'
' Buffers: shared read=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.163..0.163 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-44502144c7be49f4840d9d30c724f11b'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.075..0.075 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.101..0.101 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.071..0.071 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.073..0.073 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-df0d9473d3934de29435d1c22fc9a269'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.096..0.096 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.051..0.051 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.101..0.101 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.055..0.055 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4316868d400d450fb60bb620a89778f2'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.063..0.063 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.080..0.080 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.072..0.072 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-ecde76cbefd847ed9602a2c875529123'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.071..0.071 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-81071273eacc44c4a46180be3a7d6a04'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.064..0.064 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.072..0.072 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.088..0.088 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.068..0.068 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.057..0.057 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.068..0.068 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-78263146f1694c39935578c3fa4c6415'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.071..0.071 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.083..0.083 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-a6eb0ec674d242b793a26b259d15435f'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.073..0.073 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.051..0.051 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-7ba86277cbce489694ba03c98e7d2059'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.079..0.079 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-c7675935bd974244939ccac9181d9129'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.081..0.081 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-64c958575289438bb86455ed81517df1'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.084..0.084 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext)'
' Buffers: shared hit=3 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-56e85efaaa5f42c0913fed3745687a23'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-def2602379db49cfadf6c31d7dfc4872'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)'
' Index Cond: (c179 = 'OI-6f3333da01f349a3a17a5714a82530a6'::citext)'
' Buffers: shared hit=2 read=1'
'Execution time: 5.150 ms'
Generic Plan for Case Insensitive ---
'Limit (cost=12.67..12.68 rows=1 width=52) (actual time=5531.555..5531.634 rows=48 loops=1)'
' Buffers: shared hit=54716 read=398'
' -> Sort (cost=12.67..12.68 rows=1 width=52) (actual time=5531.552..5531.580 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' Buffers: shared hit=54716 read=398'
' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1187.686..5531.421 rows=48 loops=1)'
' Index Cond: ((c400129200 = $1) AND (c400127400 = $2))'
' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND ((c179 = $4) OR (c179 = $5) OR (c179 = $6) OR (c179 = $7) OR (c179 = $8) OR (c179 = $9) OR (c179 = $10) OR (c179 = $11) OR (c179 = $12) OR (c179 = $13) OR (c179 = $14) OR (c179 = $15) OR (c179 = $16) OR (c179 = $17) OR (c179 = $18) OR (c179 = $19) OR (c179 = $20) OR (c179 = $21) OR (c179 = $22) OR (c179 = $23) OR (c179 = $24) OR (c179 = $25) OR (c179 = $26) OR (c179 = $27) OR (c179 = $28) OR (c179 = $29) OR (c179 = $30) OR (c179 = $31) OR (c179 = $32) OR (c179 = $33) OR (c179 = $34) OR (c179 = $35) OR (c179 = $36) OR (c179 = $37) OR (c179 = $38) OR (c179 = $39) OR (c179 = $40) OR (c179 = $41) OR (c179 = $42) OR (c179 = $43) OR (c179 = $44) OR (c179 = $45) OR (c179 = $46) OR (c179 = $47) OR (c179 = $48) OR (c179 = $49) OR (c179 = $50) OR (c179 = $51)))'
' Rows Removed by Filter: 55322'
' Buffers: shared hit=54716 read=398'
'Execution time: 5531.741 ms'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Case Sensitive -
PREPARE fastquery (text,text,int,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text) as
SELECT
T776.C179,
T776.C1,
T776.C400129200
FROM
T776
WHERE
(
(T776.C400129200 = $1)
AND
(
T776.C400127400 = $2
)
AND
(
(T776.C400129100 <> $3)
OR
(
T776.C400129100 IS NULL
)
)
AND
(
(T776.C179 = $4)
OR
(
T776.C179 = $5
)
OR
(
T776.C179 = $6
)
OR
(
T776.C179 = $7
)
OR
(
T776.C179 = $8
)
OR
(
T776.C179 = $9
)
OR
(
T776.C179 = $10
)
OR
(
T776.C179 = $11
)
OR
(
T776.C179 = $12
)
OR
(
T776.C179 = $13
)
OR
(
T776.C179 = $14
)
OR
(
T776.C179 = $15
)
OR
(
T776.C179 = $16
)
OR
(
T776.C179 = $17
)
OR
(
T776.C179 = $18
)
OR
(
T776.C179 = $19
)
OR
(
T776.C179 = $20
)
OR
(
T776.C179 = $21
)
OR
(
T776.C179 = $22
)
OR
(
T776.C179 = $23
)
OR
(
T776.C179 = $24
)
OR
(
T776.C179 = $25
)
OR
(
T776.C179 = $26
)
OR
(
T776.C179 = $27
)
OR
(
T776.C179 = $28
)
OR
(
T776.C179 = $29
)
OR
(
T776.C179 = $30
)
OR
(
T776.C179 = $31
)
OR
(
T776.C179 = $32
)
OR
(
T776.C179 = $33
)
OR
(
T776.C179 = $34
)
OR
(
T776.C179 = $35
)
OR
(
T776.C179 = $36
)
OR
(
T776.C179 = $37
)
OR
(
T776.C179 = $38
)
OR
(
T776.C179 = $39
)
OR
(
T776.C179 = $40
)
OR
(
T776.C179 = $41
)
OR
(
T776.C179 = $42
)
OR
(
T776.C179 = $43
)
OR
(
T776.C179 = $44
)
OR
(
T776.C179 = $45
)
OR
(
T776.C179 = $46
)
OR
(
T776.C179 = $47
)
OR
(
T776.C179 = $48
)
OR
(
T776.C179 = $49
)
OR
(
T776.C179 = $50
)
OR
(
T776.C179 = $51
)
)
)
ORDER BY
T776.C1 ASC LIMIT 2001 OFFSET 0;
EXPLAIN analyze EXECUTE fastquery ('0','DATASET1M', 1,N'OI-941ed5dc3b644849afd6bae91ebf02d1','OI-476186266411406ba9967c732fc6f1f2','OI-d627a532701942129f531c74ab40e05b','OI-6d2c55fa269c47789130f05afc8ffa6d','OI-f1734c5368c4496c9a13035b8b236d13','OI-a63664f325144f958332044a4ea2705c','OI-70f148ef11e241409191faf63650a8a8','OI-c24bc2a9e24b4c8b8c9c11061a1bf631','OI-27ec4c51369d49958fc04ae9a6fe547f','OI-0555e41446ef420d93a78214f5253e1c','OI-95e0ca98affb4d5ebab38fe1990cf4be','OI-800e9fb833724a8585920f7a169556eb','OI-1c11e40c56904ecea9a78653f04bde84','OI-4b8f52e78d124ba89d7fde2b0fb6a720','OI-1d64f5df07ee490c88cdacabb5eb740a','OI-af68ae5b648f46ab926d9fafde6a5bb7','OI-5a0f26ba1d35460d953316496f7b7899','OI-3709034c00774804801227d21a5b1e41','OI-11fe926e91db4950b1c24159bb2022da','OI-836924722a304f8a86ff88783166e437','OI-c3a1738a5d384544b70dc3670831033f','OI-467d16d39a0e45dbbefdf20ec3c68b0c','OI-ceee9fa8436a4f72991883387074b744','OI-523324e70f8f4ae3b717b29a82776f33','OI-1a790b65e7c7458ba1567bd2c2ff35be','OI-4115e27566474081b0881ea8de0fcb88','OI-b9366dd534ae4d16a92e17abca8ae097','OI-3c3d9217564e4a82b43a230aa6e3f091','OI-8ca511ce33a84941868bd59b3e54b6b0','OI-77b1d7fa60ce4aa9899c4a56b6037cc6','OI-cd099418c1394100b7c14de9306521bd','OI-fc32fa20d0fb4e40bfad8c361889bcb6','OI-0e7ff2d492d5476b8d390456b4d619f0','OI-289fbe99682948ae86eb8e1fbf7e2350','OI-1e8ac9e7b1924505919c5e703838be54','OI-15672685a4ee4642a9f2f4926c8dace0','OI-1d6eb6a8fb0c437593d46099ef8544ed','OI-ba1326a7763240b19f0ac49934e815ac','OI-ce1e718ec2a844c383743755b976fc70','OI-454967f97851473baba213b03f4099d3','OI-699ac5def19744bf9ceee531b1c4b05d','OI-8f7140b0c06b482e8c8d9123cfe23d73','OI-295d7dc1291f45e1abf8354e735a191a','OI-813ad79d8ed14dff82a6ae0960c65515','OI-28d4d1da3a284f2e8ce5de08d8049819','OI-e0da6cbc49f44977b147cecf9da3c0c2','OI-2bf0a9c92a0543019fcefeb7b227dbf8','OI-e4fd3311fe7240019b6344ad0e357c4c')
Custom Plan for Case Sensitive-
'Limit (cost=404.05..404.08 rows=12 width=70) (actual time=0.740..0.818 rows=48 loops=1)'
' -> Sort (cost=404.05..404.08 rows=12 width=70) (actual time=0.737..0.765 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' -> Bitmap Heap Scan on t776 (cost=212.54..403.83 rows=12 width=70) (actual time=0.530..0.624 rows=48 loops=1)'
' Recheck Cond: (((c179)::text = 'OI-941ed5dc3b644849afd6bae91ebf02d1'::text) OR ((c179)::text = 'OI-476186266411406ba9967c732fc6f1f2'::text) OR ((c179)::text = 'OI-d627a532701942129f531c74ab40e05b'::text) OR ((c179)::text = 'OI-6d2c55fa269c47789130f05afc8ffa6d'::text) OR ((c179)::text = 'OI-f1734c5368c4496c9a13035b8b236d13'::text) OR ((c179)::text = 'OI-a63664f325144f958332044a4ea2705c'::text) OR ((c179)::text = 'OI-70f148ef11e241409191faf63650a8a8'::text) OR ((c179)::text = 'OI-c24bc2a9e24b4c8b8c9c11061a1bf631'::text) OR ((c179)::text = 'OI-27ec4c51369d49958fc04ae9a6fe547f'::text) OR ((c179)::text = 'OI-0555e41446ef420d93a78214f5253e1c'::text) OR ((c179)::text = 'OI-95e0ca98affb4d5ebab38fe1990cf4be'::text) OR ((c179)::text = 'OI-800e9fb833724a8585920f7a169556eb'::text) OR ((c179)::text = 'OI-1c11e40c56904ecea9a78653f04bde84'::text) OR ((c179)::text = 'OI-4b8f52e78d124ba89d7fde2b0fb6a720'::text) OR ((c179)::text = 'OI-1d64f5df07ee490c88cdacabb5eb740a'::text) OR ((c179)::text = 'OI-af68ae5b648f46ab926d9fafde6a5bb7'::text) OR ((c179)::text = 'OI-5a0f26ba1d35460d953316496f7b7899'::text) OR ((c179)::text = 'OI-3709034c00774804801227d21a5b1e41'::text) OR ((c179)::text = 'OI-11fe926e91db4950b1c24159bb2022da'::text) OR ((c179)::text = 'OI-836924722a304f8a86ff88783166e437'::text) OR ((c179)::text = 'OI-c3a1738a5d384544b70dc3670831033f'::text) OR ((c179)::text = 'OI-467d16d39a0e45dbbefdf20ec3c68b0c'::text) OR ((c179)::text = 'OI-ceee9fa8436a4f72991883387074b744'::text) OR ((c179)::text = 'OI-523324e70f8f4ae3b717b29a82776f33'::text) OR ((c179)::text = 'OI-1a790b65e7c7458ba1567bd2c2ff35be'::text) OR ((c179)::text = 'OI-4115e27566474081b0881ea8de0fcb88'::text) OR ((c179)::text = 'OI-b9366dd534ae4d16a92e17abca8ae097'::text) OR ((c179)::text = 'OI-3c3d9217564e4a82b43a230aa6e3f091'::text) OR ((c179)::text = 'OI-8ca511ce33a84941868bd59b3e54b6b0'::text) OR ((c179)::text = 'OI-77b1d7fa60ce4aa9899c4a56b6037cc6'::text) OR ((c179)::text = 'OI-cd099418c1394100b7c14de9306521bd'::text) OR ((c179)::text = 'OI-fc32fa20d0fb4e40bfad8c361889bcb6'::text) OR ((c179)::text = 'OI-0e7ff2d492d5476b8d390456b4d619f0'::text) OR ((c179)::text = 'OI-289fbe99682948ae86eb8e1fbf7e2350'::text) OR ((c179)::text = 'OI-1e8ac9e7b1924505919c5e703838be54'::text) OR ((c179)::text = 'OI-15672685a4ee4642a9f2f4926c8dace0'::text) OR ((c179)::text = 'OI-1d6eb6a8fb0c437593d46099ef8544ed'::text) OR ((c179)::text = 'OI-ba1326a7763240b19f0ac49934e815ac'::text) OR ((c179)::text = 'OI-ce1e718ec2a844c383743755b976fc70'::text) OR ((c179)::text = 'OI-454967f97851473baba213b03f4099d3'::text) OR ((c179)::text = 'OI-699ac5def19744bf9ceee531b1c4b05d'::text) OR ((c179)::text = 'OI-8f7140b0c06b482e8c8d9123cfe23d73'::text) OR ((c179)::text = 'OI-295d7dc1291f45e1abf8354e735a191a'::text) OR ((c179)::text = 'OI-813ad79d8ed14dff82a6ae0960c65515'::text) OR ((c179)::text = 'OI-28d4d1da3a284f2e8ce5de08d8049819'::text) OR ((c179)::text = 'OI-e0da6cbc49f44977b147cecf9da3c0c2'::text) OR ((c179)::text = 'OI-2bf0a9c92a0543019fcefeb7b227dbf8'::text) OR ((c179)::text = 'OI-e4fd3311fe7240019b6344ad0e357c4c'::text))'
' Filter: (((c400129100 <> 1) OR (c400129100 IS NULL)) AND ((c400129200)::text = '0'::text) AND ((c400127400)::text = 'DATASET1M'::text))'
' Heap Blocks: exact=41'
' -> BitmapOr (cost=212.54..212.54 rows=48 width=0) (actual time=0.516..0.516 rows=0 loops=1)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.023..0.023 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-941ed5dc3b644849afd6bae91ebf02d1'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-476186266411406ba9967c732fc6f1f2'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-d627a532701942129f531c74ab40e05b'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-6d2c55fa269c47789130f05afc8ffa6d'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-f1734c5368c4496c9a13035b8b236d13'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-a63664f325144f958332044a4ea2705c'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-70f148ef11e241409191faf63650a8a8'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-c24bc2a9e24b4c8b8c9c11061a1bf631'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-27ec4c51369d49958fc04ae9a6fe547f'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-0555e41446ef420d93a78214f5253e1c'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-95e0ca98affb4d5ebab38fe1990cf4be'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-800e9fb833724a8585920f7a169556eb'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1c11e40c56904ecea9a78653f04bde84'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-4b8f52e78d124ba89d7fde2b0fb6a720'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1d64f5df07ee490c88cdacabb5eb740a'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-af68ae5b648f46ab926d9fafde6a5bb7'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-5a0f26ba1d35460d953316496f7b7899'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-3709034c00774804801227d21a5b1e41'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-11fe926e91db4950b1c24159bb2022da'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-836924722a304f8a86ff88783166e437'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-c3a1738a5d384544b70dc3670831033f'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.043..0.043 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-467d16d39a0e45dbbefdf20ec3c68b0c'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-ceee9fa8436a4f72991883387074b744'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-523324e70f8f4ae3b717b29a82776f33'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1a790b65e7c7458ba1567bd2c2ff35be'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-4115e27566474081b0881ea8de0fcb88'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-b9366dd534ae4d16a92e17abca8ae097'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-3c3d9217564e4a82b43a230aa6e3f091'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-8ca511ce33a84941868bd59b3e54b6b0'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-77b1d7fa60ce4aa9899c4a56b6037cc6'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-cd099418c1394100b7c14de9306521bd'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-fc32fa20d0fb4e40bfad8c361889bcb6'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-0e7ff2d492d5476b8d390456b4d619f0'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-289fbe99682948ae86eb8e1fbf7e2350'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1e8ac9e7b1924505919c5e703838be54'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-15672685a4ee4642a9f2f4926c8dace0'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1d6eb6a8fb0c437593d46099ef8544ed'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-ba1326a7763240b19f0ac49934e815ac'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-ce1e718ec2a844c383743755b976fc70'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-454967f97851473baba213b03f4099d3'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-699ac5def19744bf9ceee531b1c4b05d'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-8f7140b0c06b482e8c8d9123cfe23d73'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-295d7dc1291f45e1abf8354e735a191a'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-813ad79d8ed14dff82a6ae0960c65515'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-28d4d1da3a284f2e8ce5de08d8049819'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-e0da6cbc49f44977b147cecf9da3c0c2'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-2bf0a9c92a0543019fcefeb7b227dbf8'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-e4fd3311fe7240019b6344ad0e357c4c'::text)'
'Execution time: 1.013 ms'
Generic Plan for Case Sensitive -
'Limit (cost=12.74..12.75 rows=1 width=70) (actual time=185.728..185.806 rows=48 loops=1)'
' -> Sort (cost=12.74..12.75 rows=1 width=70) (actual time=185.726..185.753 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.73 rows=1 width=70) (actual time=39.277..185.650 rows=48 loops=1)'
' Index Cond: (((c400129200)::text = $1) AND ((c400127400)::text = $2))'
' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND (((c179)::text = $4) OR ((c179)::text = $5) OR ((c179)::text = $6) OR ((c179)::text = $7) OR ((c179)::text = $8) OR ((c179)::text = $9) OR ((c179)::text = $10) OR ((c179)::text = $11) OR ((c179)::text = $12) OR ((c179)::text = $13) OR ((c179)::text = $14) OR ((c179)::text = $15) OR ((c179)::text = $16) OR ((c179)::text = $17) OR ((c179)::text = $18) OR ((c179)::text = $19) OR ((c179)::text = $20) OR ((c179)::text = $21) OR ((c179)::text = $22) OR ((c179)::text = $23) OR ((c179)::text = $24) OR ((c179)::text = $25) OR ((c179)::text = $26) OR ((c179)::text = $27) OR ((c179)::text = $28) OR ((c179)::text = $29) OR ((c179)::text = $30) OR ((c179)::text = $31) OR ((c179)::text = $32) OR ((c179)::text = $33) OR ((c179)::text = $34) OR ((c179)::text = $35) OR ((c179)::text = $36) OR ((c179)::text = $37) OR ((c179)::text = $38) OR ((c179)::text = $39) OR ((c179)::text = $40) OR ((c179)::text = $41) OR ((c179)::text = $42) OR ((c179)::text = $43) OR ((c179)::text = $44) OR ((c179)::text = $45) OR ((c179)::text = $46) OR ((c179)::text = $47) OR ((c179)::text = $48) OR ((c179)::text = $49) OR ((c179)::text = $50) OR ((c179)::text = $51)))'
' Rows Removed by Filter: 55322'
'Execution time: 185.916 ms'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-Thanks and Regards,
Sameer Naik
-----Original Message-----
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Sent: Tuesday, May 21, 2019 3:47 AM
To: Deepak Somaiya <deepsom@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; david@kineticode.com; Naik, Sameer <Sameer_Naik@bmc.com>; pgsql-performance@lists.postgresql.org
Subject: [EXTERNAL] Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.2ndQuadrant.com&d=DwIDAw&c=UrUhmHsiT...
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
2019-05-09 04:14 Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-17 06:42 ` RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-20 21:37 ` Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-20 22:16 ` Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-05-23 06:37 ` RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Naik, Sameer <Sameer_Naik@bmc.com>
@ 2019-05-23 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2019-05-23 17:17 UTC (permalink / raw)
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>
"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 degradation - 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=0.42..12.66 rows=1 width=52) (actual time=1187.686..5531.421 rows=48 loops=1)'
> ' Index Cond: ((c400129200 = $1) AND (c400127400 = $2))'
> ' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND ((c179 = $4) OR (c179 = $5) OR (c179 = $6) OR (c179 = $7) OR (c179 = $8) OR (c179 = $9) OR (c179 = $10) OR (c179 = $11) OR (c179 = $12) OR (c179 = $13) OR (c179 = $14) OR (c179 = $15) OR (c179 = $16) OR (c179 = $17) OR (c179 = $18) OR (c179 = $19) OR (c179 = $20) OR (c179 = $21) OR (c179 = $22) OR (c179 = $23) OR (c179 = $24) OR (c179 = $25) OR (c179 = $26) OR (c179 = $27) OR (c179 = $28) OR (c179 = $29) OR (c179 = $30) OR (c179 = $31) OR (c179 = $32) OR (c179 = $33) OR (c179 = $34) OR (c179 = $35) OR (c179 = $36) OR (c179 = $37) OR (c179 = $38) OR (c179 = $39) OR (c179 = $40) OR (c179 = $41) OR (c179 = $42) OR (c179 = $43) OR (c179 = $44) OR (c179 = $45) OR (c179 = $46) OR (c179 = $47) OR (c179 = $48) OR (c179 = $49) OR (c179 = $50) OR (c179 = $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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2019-05-23 17:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-09 04:14 Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Deepak Somaiya <deepsom@yahoo.com>
2019-05-17 06:42 ` Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-20 21:37 ` Deepak Somaiya <deepsom@yahoo.com>
2019-05-20 22:16 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2019-05-23 06:37 ` Naik, Sameer <Sameer_Naik@bmc.com>
2019-05-23 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
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