Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Naik, Sameer

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).

I am observing that the Generic plan for Prepared statement requires 5544.701 ms to execute where as custom plan for same query requires 3.497 ms.

The cost of execution is reduced from 402 (custom plan) to 12.68 (generic plan).

However the execution time has gone up from 3.497 ms to 5544.701 ms.

 

Below are the details about this use case.

 

Postgres version - PostgreSQL 9.6.6, compiled by Visual C++ build 1800, 64-bit

1.      Full Table and Index Schema -

                            Table "public.t776"

   Column    |  Type   | Modifiers | Storage  | Stats target | Description

-------------+---------+-----------+----------+--------------+-------------

c1          | citext  | not null  | extended |              |

c2          | citext  |           | extended |              |                                                                         

c3          | integer |           | plain    |              |

c4          | citext  |           | extended |              |

c5          | citext  | not null  | extended |              |

c6          | integer |           | plain    |              |

c7          | integer |           | plain    |              |

c8          | citext  | not null  | extended |              |

c112        | citext  |           | extended |              |

c179        | citext  |           | extended |              |

c60513      | citext  |           | extended |              |

c60914      | citext  |           | extended |              |

c60989      | citext  |           | extended |              |

c200000001  | citext  |           | extended |              |

c200000003  | citext  |           | extended |              |

c200000004  | citext  |           | extended |              |

c200000005  | citext  |           | extended |              |

c200000020  | citext  |           | extended |              |

c200003000  | citext  |           | extended |              |

c240000007  | citext  |           | extended |              |

c240000008  | citext  |           | extended |              |

c240001002  | citext  |           | extended |              |

c240001003  | citext  |           | extended |              |

c240001005  | citext  |           | extended |              |

c260100002  | integer |           | plain    |              |

c300927600  | integer |           | plain    |              |

c301002800  | citext  |           | extended |              |

c301002900  | citext  |           | extended |              |

c301003400  | citext  |           | extended |              |

c301047700  | citext  |           | extended |              |

c301047800  | citext  |           | extended |              |

c301089100  | citext  |           | extended |              |

c301118000  | integer |           | plain    |              |

c301136600  | citext  |           | extended |              |

c301136800  | citext  |           | extended |              |

c301136900  | integer |           | plain    |              |

c301137000  | integer |           | plain    |              |

c301137100  | citext  |           | extended |              |

c301137200  | citext  |           | extended |              |

c301137300  | citext  |           | extended |              |

c301137400  | citext  |           | extended |              |

c301172600  | integer |           | plain    |              |

c301186800  | citext  |           | extended |              |

c400079600  | citext  |           | extended |              |

c400124500  | integer |           | plain    |              |

c400127400  | citext  |           | extended |              |

c400128800  | citext  |           | extended |              |

c400128900  | citext  |           | extended |              |

c400129100  | integer |           | plain    |              |

c400129200  | citext  |           | extended |              |

c400130900  | citext  |           | extended |              |

c400131000  | citext  |           | extended |              |

c400131200  | citext  |           | extended |              |

c400131300  | citext  |           | extended |              |

c490001289  | citext  |           | extended |              |

c490008000  | citext  |           | extended |              |

c490008100  | citext  |           | extended |              |

c490009000  | citext  |           | extended |              |

c490009100  | citext  |           | extended |              |

c530010100  | citext  |           | extended |              |

c530010200  | citext  |           | extended |              |

c530014300  | integer |           | plain    |              |

c530014400  | integer |           | plain    |              |

c530014500  | integer |           | plain    |              |

c530019500  | citext  |           | extended |              |

c530031600  | integer |           | plain    |              |

c530032500  | integer |           | plain    |              |

c530035000  | citext  |           | extended |              |

c530035200  | citext  |           | extended |              |

c530041601  | integer |           | plain    |              |

c530054200  | integer |           | plain    |              |

c530054400  | integer |           | plain    |              |

c530058400  | citext  |           | extended |              |

c530058500  | citext  |           | extended |              |

c530059800  | citext  |           | extended |              |

c530060100  | integer |           | plain    |              |

c530060200  | citext  |           | extended |              |

c530062400  | citext  |           | extended |              |

c530067430  | integer |           | plain    |              |

c530067920  | integer |           | plain    |              |

c530067930  | citext  |           | extended |              |

c530068090  | integer |           | plain    |              |

c530070390  | integer |           | plain    |              |

c530071130  | citext  |           | extended |              |

c530071180  | citext  |           | extended |              |

c530072336  | citext  |           | extended |              |

c530074016  | integer |           | plain    |              |

c200000006  | citext  |           | extended |              |

c200000007  | citext  |           | extended |              |

c200000012  | citext  |           | extended |              |

c240001004  | citext  |           | extended |              |

c260000001  | citext  |           | extended |              |

c260000005  | citext  |           | extended |              |

c260400003  | integer |           | plain    |              |

c1000000001 | citext  |           | extended |              |

Indexes:

    "pk_t776" PRIMARY KEY, btree (c1)

    "i776_0_179_t776" UNIQUE, btree (c179)

    "i776_0_200000001_t776" btree (c200000001)

    "i776_0_240001002_t776" btree (c240001002)

    "i776_0_301186800_t776" btree (c301186800, c400127400)

    "i776_0_400079600_1136943505_t776" btree (c400079600, c530041601, c179)

    "i776_0_400079600_t776" btree (c400079600)

    "i776_0_400129200_1337395809_t776" btree (c400129200, c400129100)

    "i776_0_400129200_t776" btree (c400129200, c400129100, c400127400, c1)

    "i776_0_400131200_t776" btree (c400131200)

    "i776_0_400131300_t776" btree (c400131300)

    "i776_0_530010100_t776" btree (c530010100, c400127400)

    "i776_0_530060100_207771634_t776" btree (c530060100, c6, c400129200)

    "i776_0_530060100_t776" btree (c530060100, c6, c400129100, c400129200)

    "i776_0_530060200_t776" btree (c530060200, c400127400)

Check constraints:

    "len_c1" CHECK (length(c1::text) <= 15)

    "len_c112" CHECK (length(c112::text) <= 255)

    "len_c179" CHECK (length(c179::text) <= 38)

    "len_c2" CHECK (length(c2::text) <= 254)

    "len_c200000001" CHECK (length(c200000001::text) <= 254)

    "len_c200000003" CHECK (length(c200000003::text) <= 60)

    "len_c200000004" CHECK (length(c200000004::text) <= 60)

    "len_c200000005" CHECK (length(c200000005::text) <= 60)

    "len_c200000020" CHECK (length(c200000020::text) <= 254)

    "len_c240000007" CHECK (length(c240000007::text) <= 254)

    "len_c240001002" CHECK (length(c240001002::text) <= 254)

    "len_c240001003" CHECK (length(c240001003::text) <= 254)

    "len_c240001005" CHECK (length(c240001005::text) <= 254)

    "len_c301002800" CHECK (length(c301002800::text) <= 254)

    "len_c301002900" CHECK (length(c301002900::text) <= 254)

    "len_c301003400" CHECK (length(c301003400::text) <= 255)

    "len_c301047700" CHECK (length(c301047700::text) <= 254)

    "len_c301047800" CHECK (length(c301047800::text) <= 38)

    "len_c301089100" CHECK (length(c301089100::text) <= 80)

    "len_c301136600" CHECK (length(c301136600::text) <= 254)

    "len_c301136800" CHECK (length(c301136800::text) <= 254)

    "len_c301137100" CHECK (length(c301137100::text) <= 254)

    "len_c301137200" CHECK (length(c301137200::text) <= 254)

    "len_c301137300" CHECK (length(c301137300::text) <= 254)

    "len_c301137400" CHECK (length(c301137400::text) <= 254)

    "len_c301186800" CHECK (length(c301186800::text) <= 254)

    "len_c4" CHECK (length(c4::text) <= 254)

    "len_c400079600" CHECK (length(c400079600::text) <= 38)

    "len_c400127400" CHECK (length(c400127400::text) <= 127)

    "len_c400128800" CHECK (length(c400128800::text) <= 255)

    "len_c400128900" CHECK (length(c400128900::text) <= 255)

    "len_c400129200" CHECK (length(c400129200::text) <= 38)

    "len_c400130900" CHECK (length(c400130900::text) <= 38)

    "len_c400131000" CHECK (length(c400131000::text) <= 38)

    "len_c400131200" CHECK (length(c400131200::text) <= 255)

    "len_c400131300" CHECK (length(c400131300::text) <= 255)

    "len_c490001289" CHECK (length(c490001289::text) <= 127)

    "len_c490008000" CHECK (length(c490008000::text) <= 40)

    "len_c490008100" CHECK (length(c490008100::text) <= 40)

    "len_c490009000" CHECK (length(c490009000::text) <= 40)

    "len_c490009100" CHECK (length(c490009100::text) <= 40)

    "len_c5" CHECK (length(c5::text) <= 254)

    "len_c530010100" CHECK (length(c530010100::text) <= 254)

    "len_c530010200" CHECK (length(c530010200::text) <= 254)

    "len_c530035200" CHECK (length(c530035200::text) <= 255)

    "len_c530058400" CHECK (length(c530058400::text) <= 254)

    "len_c530058500" CHECK (length(c530058500::text) <= 254)

    "len_c530059800" CHECK (length(c530059800::text) <= 255)

    "len_c530060200" CHECK (length(c530060200::text) <= 255)

    "len_c530062400" CHECK (length(c530062400::text) <= 254)

    "len_c530067930" CHECK (length(c530067930::text) <= 127)

    "len_c530071130" CHECK (length(c530071130::text) <= 128)

    "len_c530071180" CHECK (length(c530071180::text) <= 128)

    "len_c530072336" CHECK (length(c530072336::text) <= 254)

    "len_c60513" CHECK (length(c60513::text) <= 255)

    "len_c60914" CHECK (length(c60914::text) <= 255)

    "len_c60989" CHECK (length(c60989::text) <= 255)

    "len_c8" CHECK (length(c8::text) <= 254)

 

 

\d+: extra argument ">>c:/table_schemat.txt" ignored

 

Note : No custom functions used.

 

 

3.      SELECT relname, relpages, reltuples, relallvisible, relkind, relnatts, relhassubclass, reloptions, pg_table_size(oid) FROM pg_class WHERE relname='T776';

't776',13295,'110743',0,'r',95,false,,'108920832'

 

4.      Explain (Analyze, Buffers)-

 

PREPARE query (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;

  

     

Explain (analyze,buffers) Execute query('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);

 

 

 

4.a ) Explain (Analyze,Buffers) output for first 5 runs.

'Limit  (cost=402.71..402.74 rows=12 width=52) (actual time=3.185..3.266 rows=48 loops=1)'

'  Buffers: shared hit=184'

'  ->  Sort  (cost=402.71..402.74 rows=12 width=52) (actual time=3.179..3.207 rows=48 loops=1)'

'        Sort Key: c1'

'        Sort Method: quicksort  Memory: 31kB'

'        Buffers: shared hit=184'

'        ->  Bitmap Heap Scan on t776  (cost=212.54..402.49 rows=12 width=52) (actual time=2.629..2.794 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=184'

'              ->  BitmapOr  (cost=212.54..212.54 rows=48 width=0) (actual time=2.607..2.607 rows=0 loops=1)'

'                    Buffers: shared hit=145'

'                    ->  Bitmap Index Scan on i776_0_179_t776  (cost=0.00..4.43 rows=1 width=0) (actual time=0.065..0.065 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::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.087..0.087 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-44502144c7be49f4840d9d30c724f11b'::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.052..0.052 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::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.053..0.053 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-dd23d23ea6ca459ab6fc3256682df66a'::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.056..0.056 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::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.061..0.061 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-f268ba1f12014f07b1b34fd9050aa92d'::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.052..0.052 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-da2e9a38f45b41e9baea8c35b45577dc'::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-df0d9473d3934de29435d1c22fc9a269'::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.053..0.053 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-bd704daa55d24f12a54da6d5df68d05c'::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.049..0.049 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.049..0.049 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.052..0.052 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::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.044..0.044 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-f5fca0c13c454a04939b6f6a4871d647'::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.041..0.041 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::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.053..0.053 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-4316868d400d450fb60bb620a89778f2'::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-4abdb84db1414bd1abbb66f2a35de267'::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.044..0.044 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-fbb28f59448d44adb65c1145b94e23fc'::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.049..0.049 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::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.055..0.055 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-ecde76cbefd847ed9602a2c875529123'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-55cf16be8f6e43aba7813d7dd898432c'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::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.101..0.101 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-81071273eacc44c4a46180be3a7d6a04'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::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.048..0.048 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-0ed0ff8956a84c598226f7e71f37f012'::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.053..0.053 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::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.042..0.042 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::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.054..0.054 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::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-78263146f1694c39935578c3fa4c6415'::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.052..0.052 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::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.057..0.057 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-8dd73d417cf84827bc3708a362c7ee40'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::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.049..0.049 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-a6eb0ec674d242b793a26b259d15435f'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::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-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.054..0.054 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-c7675935bd974244939ccac9181d9129'::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.051..0.051 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-64c958575289438bb86455ed81517df1'::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.048..0.048 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::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.054..0.054 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-462d7db8d54541b996bbc977e3f4e6ec'::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.058..0.058 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext)'

'                          Buffers: shared hit=4'

'                    ->  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-e31f38e2a95e44bfa8b71ee1d31a66fa'::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-56e85efaaa5f42c0913fed3745687a23'::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.049..0.049 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.054..0.054 rows=1 loops=1)'

'                          Index Cond: (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::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-6f3333da01f349a3a17a5714a82530a6'::citext)'

'                          Buffers: shared hit=3'

'Execution time: 3.497 ms'

 

Link to Analyze output for Custom Plan - https://explain.depesz.com/s/6u6H

 

 

4.b) Explain (Analyze,Buffers) output from 6th run onwards

 

 

 

'Limit  (cost=12.67..12.68 rows=1 width=52) (actual time=5544.509..5544.590 rows=48 loops=1)'

'  Buffers: shared hit=55114'

'  ->  Sort  (cost=12.67..12.68 rows=1 width=52) (actual time=5544.507..5544.535 rows=48 loops=1)'

'        Sort Key: c1'

'        Sort Method: quicksort  Memory: 31kB'

'        Buffers: shared hit=55114'

'        ->  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))'

'              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=55114'

'Execution time: 5544.701 ms'

 

 

Link to Analyze output for Generic Plan - https://explain.depesz.com/s/7jph

 

5.      History - Always slower on 6th iteration since Postgres 9.2

6.      System Information -

OS Name        Microsoft Windows Server 2008 R2 Enterprise

Version        6.1.7601 Service Pack 1 Build 7601

Other OS Description         Not Available

OS Manufacturer        Microsoft Corporation

System Name        VW-AUS-ATM-PG01

System Manufacturer        VMware, Inc.

System Model        VMware Virtual Platform

System Type        x64-based PC

Processor        Intel(R) Xeon(R) CPU E5-2660 v3 @ 2.60GHz, 2593 Mhz, 3 Core(s), 3 Logical Processor(s)

Processor        Intel(R) Xeon(R) CPU E5-2660 v3 @ 2.60GHz, 2593 Mhz, 3 Core(s), 3 Logical Processor(s)

BIOS Version/Date        Phoenix Technologies LTD 6.00, 9/21/2015

SMBIOS Version        2.4

Windows Directory        C:\Windows

System Directory        C:\Windows\system32

Boot Device        \Device\HarddiskVolume1

Locale        United States

Hardware Abstraction Layer        Version = "6.1.7601.24354"

User Name        Not Available

Time Zone        Central Daylight Time

Installed Physical Memory (RAM)        24.0 GB

Total Physical Memory        24.0 GB

Available Physical Memory        21.1 GB

Total Virtual Memory        24.0 GB

Available Virtual Memory        17.3 GB

Page File Space        0 bytes

 

 

-Thanks and Regards,

Sameer Naik

 

Reply | Threaded
Open this post in threaded view
|

Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Bruce Momjian
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  <[hidden email]>        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 +


Reply | Threaded
Open this post in threaded view
|

Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Tom Lane-2
Bruce Momjian <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Andres Freund
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


Reply | Threaded
Open this post in threaded view
|

Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Tom Lane-2
Andres Freund <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Naik, Sameer
In reply to this post by Tom Lane-2
>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:[hidden email]]
Sent: Monday, April 29, 2019 8:06 PM
To: Bruce Momjian <[hidden email]>
Cc: Naik, Sameer <[hidden email]>; [hidden email]
Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans

Bruce Momjian <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Deepak Somaiya
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 <[hidden email]> 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:[hidden email]]
Sent: Monday, April 29, 2019 8:06 PM
To: Bruce Momjian <[hidden email]>
Cc: Naik, Sameer <[hidden email]>; [hidden email]
Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans

Bruce Momjian <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Naik, Sameer

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

Bruce Momjian <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

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

Bruce Momjian <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Tomas Vondra-4
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 <[hidden email]> 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


Reply | Threaded
Open this post in threaded view
|

RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Naik, Sameer
@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 <[hidden email]>
Sent: Tuesday, May 21, 2019 3:47 AM
To: Deepak Somaiya <[hidden email]>
Cc: Tom Lane <[hidden email]>; Bruce Momjian <[hidden email]>; [hidden email]; Naik, Sameer <[hidden email]>; [hidden email]
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 <[hidden email]> 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=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=K893err8oTutgRKCeLUAsHd_iqcPBdCmI71ID5BjsTk&m=3dYLVBgo4Y0o0EkCgQ-pKShXctMnCCJCaKme72rIPeI&s=XeEyBe6Oi1N5Bqgt9HnirKF_kBqs5QYEgNtxf8UZiyc&e=
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Reply | Threaded
Open this post in threaded view
|

Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans

Tom Lane-2
"Naik, Sameer" <[hidden email]> 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