Thread: Generic Plans for Prepared Statement are 158155 times slower thanCustom Plans

On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote:
> Hi,
> 
> 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.  The PREPARE manual page for PG 11 says:

       Prepared statements can use generic plans rather than re-planning
       with each set of supplied EXECUTE values. This occurs immediately
       for prepared statements with no parameters; otherwise it occurs
       only after five or more executions produce plans whose estimated
-->    cost average (including planning overhead) is more expensive than
-->    the generic plan cost estimate. Once a generic plan is chosen, it
       is used for the remaining lifetime of the prepared statement. Using
       EXECUTE values which are rare in columns with many duplicates can
       generate custom plans that are so much cheaper than the generic
       plan, even after adding planning overhead, that the generic plan
       might never be used.

Also, PG 9.2 is EOL so are you actually using that or something more
recent?  It would be interesting to see if this is true on a supported
version of Postgres.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

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.385rows=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

Hi,

On 2019-04-29 10:35:39 -0400, Tom Lane wrote:
> 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.

Yea, I've both seen the "generic is cheaper due to averaged selectivity"
and the "insignificant cost variations lead to always prefer custom
plan" problems in production.

I've also - but less severely - seen that the "planning cost" we add to
the custom plan leads to the generic plan to always be preferred. In
particular for indexed queries, on system that set random_page_cost =
seq_page_cost = 1 (due to SSD or expectation that workload is entirely
cached), the added cost from cached_plan_cost() can be noticable in
comparison to the estimated cost of the total query.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2019-04-29 10:35:39 -0400, Tom Lane wrote:
>> 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.

> Yea, I've both seen the "generic is cheaper due to averaged selectivity"
> and the "insignificant cost variations lead to always prefer custom
> plan" problems in production.

I wonder if we couldn't do something based on having seen several
different custom plans before we try to make this decision.  It'd be
just about free to track the min and max custom cost estimates, along
with their average.  The case where it is sensible to be switching to
a generic plan is where all the plans come out looking more or less
alike --- if the workload is such that we get markedly different plans
for different inputs, then we'd probably better just eat the cost of
planning every time.  So maybe the rule should be something like
"if the min and max custom costs, as well as the generic cost
estimate, are all within 10% of the average custom cost, then it's
okay to switch to generic".  We might need to collect more than 5
custom estimates before we put much faith in the decision, too.

> I've also - but less severely - seen that the "planning cost" we add to
> the custom plan leads to the generic plan to always be preferred.

Yeah; the planning cost business is very much of a hack, because we
don't have a good handle on how that really relates to execution
costs.  But if we're thinking of the decision as being risk-based,
which is basically what I'm suggesting above, maybe we could just
drop that whole component of the algorithm?

            regards, tom lane

>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
ofthe 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.385rows=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
keysis. 

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
remaining55,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
remaining55,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
ofthe 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.385rows=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
keysis. 


In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would
producea 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
thecustom plans, on the grounds that they must be estimation errors.  In principle a generic plan could never really be
betterthan a custom plan; so if it looks that way on a cost basis, what that probably means is that the actual
parametervalues are outliers of some sort (e.g. extremely common), and the custom plan "knows" that it's going to be
takinga hit from that, but the generic plan doesn't.  In this sort of situation, going with the generic plan could be
reallydisastrous, 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
likelythat there will be some variation in the cost estimates between the generic and specific cases, even if the plan
structureis exactly the same; and that variation could go in either direction. 

            regards, tom lane

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
,datais 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
oftext.
 
>
>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 

@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.157rows=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.163rows=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.075rows=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.077rows=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.091rows=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.101rows=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.071rows=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.067rows=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.073rows=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.096rows=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.051rows=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.050rows=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.070rows=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.101rows=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.055rows=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.066rows=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.069rows=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.063rows=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.080rows=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.072rows=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.071rows=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.069rows=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.070rows=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.066rows=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.066rows=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.064rows=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.072rows=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.088rows=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.068rows=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.057rows=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.091rows=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.068rows=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.071rows=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.069rows=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.070rows=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.083rows=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.073rows=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.051rows=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.079rows=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.081rows=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.084rows=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.077rows=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.069rows=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.067rows=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.066rows=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.050rows=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.070rows=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.067rows=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.421rows=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.023rows=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.011rows=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.010rows=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.009rows=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.009rows=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.009rows=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.008rows=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.009rows=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.009rows=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.009rows=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.008rows=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.008rows=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.009rows=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.009rows=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.008rows=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.008rows=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.009rows=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.009rows=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.009rows=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.008rows=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.008rows=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.043rows=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.010rows=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.009rows=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.008rows=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.009rows=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.009rows=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.009rows=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.008rows=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.008rows=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.008rows=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.008rows=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.009rows=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.008rows=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.009rows=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.008rows=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.008rows=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.010rows=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.008rows=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.008rows=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.008rows=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.008rows=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.009rows=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.008rows=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.009rows=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.008rows=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.009rows=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.008rows=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.650rows=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
,datais 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
oftext. 
>
>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=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=K893err8oTutgRKCeLUAsHd_iqcPBdCmI71ID5BjsTk&m=3dYLVBgo4Y0o0EkCgQ-pKShXctMnCCJCaKme72rIPeI&s=XeEyBe6Oi1N5Bqgt9HnirKF_kBqs5QYEgNtxf8UZiyc&e=
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

"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
,datais 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.421rows=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