Thread
Commits
-
doc: remove pg_config mention from PG 12 release notes
- 31f11f964734 12.0 landed
-
Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Naik, Sameer <sameer_naik@bmc.com> — 2019-04-29T10:36:20Z
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 -
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Bruce Momjian <bruce@momjian.us> — 2019-04-29T13:36:07Z
On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: > Hi, > > Since Postgres 9.2, for prepared statements, the CBO automatically switches > from Custom Plan to Generic plan on the sixth iteration (reference backend/ > utils/cache/plancache.c). This is not totally true. The PREPARE manual page for PG 11 says: Prepared statements can use generic plans rather than re-planning with each set of supplied EXECUTE values. This occurs immediately for prepared statements with no parameters; otherwise it occurs only after five or more executions produce plans whose estimated --> cost average (including planning overhead) is more expensive than --> the generic plan cost estimate. Once a generic plan is chosen, it is used for the remaining lifetime of the prepared statement. Using EXECUTE values which are rare in columns with many duplicates can generate custom plans that are so much cheaper than the generic plan, even after adding planning overhead, that the generic plan might never be used. Also, PG 9.2 is EOL so are you actually using that or something more recent? It would be interesting to see if this is true on a supported version of Postgres. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + As you are, so once was I. As I am, so you will be. + + Ancient Roman grave inscription + -
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-29T14:35:39Z
Bruce Momjian <bruce@momjian.us> writes: > On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: >> Since Postgres 9.2, for prepared statements, the CBO automatically switches >> from Custom Plan to Generic plan on the sixth iteration (reference backend/ >> utils/cache/plancache.c). > This is not totally true. Yeah, that's a pretty inaccurate statement of the behavior. The problem seems to be that the actual values being used for c400129200 and c400127400 are quite common in the dataset, so that when considering Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would produce a better estimate. Won't help OP on 9.6, though. This isn't the first time we've seen a plan-choice failure of this sort. I've wondered if we should make the plancache simply disbelieve generic cost estimates that are actually cheaper than the custom plans, on the grounds that they must be estimation errors. In principle a generic plan could never really be better than a custom plan; so if it looks that way on a cost basis, what that probably means is that the actual parameter values are outliers of some sort (e.g. extremely common), and the custom plan "knows" that it's going to be taking a hit from that, but the generic plan doesn't. In this sort of situation, going with the generic plan could be really disastrous, which is exactly what the OP is seeing (and what we've seen reported before). However, I'm not sure how to tune this idea so that it doesn't end up rejecting perfectly good generic plans. It's likely that there will be some variation in the cost estimates between the generic and specific cases, even if the plan structure is exactly the same; and that variation could go in either direction. regards, tom lane -
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Andres Freund <andres@anarazel.de> — 2019-04-29T16:23:38Z
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
-
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Tom Lane <tgl@sss.pgh.pa.us> — 2019-04-29T16:51:36Z
Andres Freund <andres@anarazel.de> writes: > On 2019-04-29 10:35:39 -0400, Tom Lane wrote: >> This isn't the first time we've seen a plan-choice failure of this sort. >> I've wondered if we should make the plancache simply disbelieve generic >> cost estimates that are actually cheaper than the custom plans, on the >> grounds that they must be estimation errors. In principle a generic >> plan could never really be better than a custom plan; so if it looks >> that way on a cost basis, what that probably means is that the actual >> parameter values are outliers of some sort (e.g. extremely common), >> and the custom plan "knows" that it's going to be taking a hit from >> that, but the generic plan doesn't. In this sort of situation, going >> with the generic plan could be really disastrous, which is exactly >> what the OP is seeing (and what we've seen reported before). >> >> However, I'm not sure how to tune this idea so that it doesn't end up >> rejecting perfectly good generic plans. It's likely that there will be >> some variation in the cost estimates between the generic and specific >> cases, even if the plan structure is exactly the same; and that >> variation could go in either direction. > Yea, I've both seen the "generic is cheaper due to averaged selectivity" > and the "insignificant cost variations lead to always prefer custom > plan" problems in production. I wonder if we couldn't do something based on having seen several different custom plans before we try to make this decision. It'd be just about free to track the min and max custom cost estimates, along with their average. The case where it is sensible to be switching to a generic plan is where all the plans come out looking more or less alike --- if the workload is such that we get markedly different plans for different inputs, then we'd probably better just eat the cost of planning every time. So maybe the rule should be something like "if the min and max custom costs, as well as the generic cost estimate, are all within 10% of the average custom cost, then it's okay to switch to generic". We might need to collect more than 5 custom estimates before we put much faith in the decision, too. > I've also - but less severely - seen that the "planning cost" we add to > the custom plan leads to the generic plan to always be preferred. Yeah; the planning cost business is very much of a hack, because we don't have a good handle on how that really relates to execution costs. But if we're thinking of the decision as being risk-based, which is basically what I'm suggesting above, maybe we could just drop that whole component of the algorithm? regards, tom lane
-
RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Naik, Sameer <sameer_naik@bmc.com> — 2019-04-30T04:58:50Z
>The problem seems to be that the actual values being used for >c400129200 and c400127400 are quite common in the dataset, so that when considering >Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) >the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case > -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) > Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) > it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. Distribution of the keys c400129200 and c400127400 . The distribution of c400129200 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400129200 is 0. For each of the remaining 55,373 records the value of c400129200 is distinct. The distribution of c400127400 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400127400 is 'DATASET1M' . For remaining 55,373 records the value of c400127400 the value is same and is ' 'DATASET2M' . -Thanks and Regards, Sameer Naik -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 29, 2019 8:06 PM To: Bruce Momjian <bruce@momjian.us> Cc: Naik, Sameer <Sameer_Naik@bmc.com>; pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans Bruce Momjian <bruce@momjian.us> writes: > On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: >> Since Postgres 9.2, for prepared statements, the CBO automatically >> switches from Custom Plan to Generic plan on the sixth iteration >> (reference backend/ utils/cache/plancache.c). > This is not totally true. Yeah, that's a pretty inaccurate statement of the behavior. The problem seems to be that the actual values being used for c400129200 and c400127400 are quite common in the dataset, so that when considering Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would produce a better estimate. Won't help OP on 9.6, though. This isn't the first time we've seen a plan-choice failure of this sort. I've wondered if we should make the plancache simply disbelieve generic cost estimates that are actually cheaper than the custom plans, on the grounds that they must be estimation errors. In principle a generic plan could never really be better than a custom plan; so if it looks that way on a cost basis, what that probably means is that the actual parameter values are outliers of some sort (e.g. extremely common), and the custom plan "knows" that it's going to be taking a hit from that, but the generic plan doesn't. In this sort of situation, going with the generic plan could be really disastrous, which is exactly what the OP is seeing (and what we've seen reported before). However, I'm not sure how to tune this idea so that it doesn't end up rejecting perfectly good generic plans. It's likely that there will be some variation in the cost estimates between the generic and specific cases, even if the plan structure is exactly the same; and that variation could go in either direction. regards, tom lane -
Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Deepak Somaiya <deepsom@yahoo.com> — 2019-05-09T04:14:15Z
Sameer,were you able to resolve it? I am not sure if this is very common in postges - I doubt though but have not seen such a drastic performance degradation and that too when planner making the call. Deepak On Tuesday, April 30, 2019, 1:27:14 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote: >The problem seems to be that the actual values being used for >c400129200 and c400127400 are quite common in the dataset, so that when considering >Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) >the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case > -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) > Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) > it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. Distribution of the keys c400129200 and c400127400 . The distribution of c400129200 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400129200 is 0. For each of the remaining 55,373 records the value of c400129200 is distinct. The distribution of c400127400 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400127400 is 'DATASET1M' . For remaining 55,373 records the value of c400127400 the value is same and is ' 'DATASET2M' . -Thanks and Regards, Sameer Naik -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 29, 2019 8:06 PM To: Bruce Momjian <bruce@momjian.us> Cc: Naik, Sameer <Sameer_Naik@bmc.com>; pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans Bruce Momjian <bruce@momjian.us> writes: > On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: >> Since Postgres 9.2, for prepared statements, the CBO automatically >> switches from Custom Plan to Generic plan on the sixth iteration >> (reference backend/ utils/cache/plancache.c). > This is not totally true. Yeah, that's a pretty inaccurate statement of the behavior. The problem seems to be that the actual values being used for c400129200 and c400127400 are quite common in the dataset, so that when considering Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would produce a better estimate. Won't help OP on 9.6, though. This isn't the first time we've seen a plan-choice failure of this sort. I've wondered if we should make the plancache simply disbelieve generic cost estimates that are actually cheaper than the custom plans, on the grounds that they must be estimation errors. In principle a generic plan could never really be better than a custom plan; so if it looks that way on a cost basis, what that probably means is that the actual parameter values are outliers of some sort (e.g. extremely common), and the custom plan "knows" that it's going to be taking a hit from that, but the generic plan doesn't. In this sort of situation, going with the generic plan could be really disastrous, which is exactly what the OP is seeing (and what we've seen reported before). However, I'm not sure how to tune this idea so that it doesn't end up rejecting perfectly good generic plans. It's likely that there will be some variation in the cost estimates between the generic and specific cases, even if the plan structure is exactly the same; and that variation could go in either direction. regards, tom lane
-
RE: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Naik, Sameer <sameer_naik@bmc.com> — 2019-05-17T06:42:23Z
Deepak, I changed the datatype from citext to text and now everything works fine. The data distribution is same, plan is same, yet there is a huge performance degradation when citext is used instead of text. However the business case requires case insensitive string handling. I am looking forward to some expert advice here when dealing with citext data type. -Thanks and Regards, Sameer Naik From: Deepak Somaiya [mailto:deepsom@yahoo.com] Sent: Thursday, May 9, 2019 9:44 AM To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Naik, Sameer <Sameer_Naik@bmc.com> Cc: pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Sameer, were you able to resolve it? I am not sure if this is very common in postges - I doubt though but have not seen such a drastic performance degradation and that too when planner making the call. Deepak On Tuesday, April 30, 2019, 1:27:14 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com<mailto:Sameer_Naik@bmc.com>> wrote: >The problem seems to be that the actual values being used for >c400129200 and c400127400 are quite common in the dataset, so that when considering >Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) >the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case > -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) > Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) > it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. Distribution of the keys c400129200 and c400127400 . The distribution of c400129200 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400129200 is 0. For each of the remaining 55,373 records the value of c400129200 is distinct. The distribution of c400127400 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400127400 is 'DATASET1M' . For remaining 55,373 records the value of c400127400 the value is same and is ' 'DATASET2M' . -Thanks and Regards, Sameer Naik -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us<mailto:tgl@sss.pgh.pa.us>] Sent: Monday, April 29, 2019 8:06 PM To: Bruce Momjian <bruce@momjian.us<mailto:bruce@momjian.us>> Cc: Naik, Sameer <Sameer_Naik@bmc.com<mailto:Sameer_Naik@bmc.com>>; pgsql-performance@lists.postgresql.org<mailto:pgsql-performance@lists.postgresql.org> Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans Bruce Momjian <bruce@momjian.us<mailto:bruce@momjian.us>> writes: > On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: >> Since Postgres 9.2, for prepared statements, the CBO automatically >> switches from Custom Plan to Generic plan on the sixth iteration >> (reference backend/ utils/cache/plancache.c). > This is not totally true. Yeah, that's a pretty inaccurate statement of the behavior. The problem seems to be that the actual values being used for c400129200 and c400127400 are quite common in the dataset, so that when considering Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would produce a better estimate. Won't help OP on 9.6, though. This isn't the first time we've seen a plan-choice failure of this sort. I've wondered if we should make the plancache simply disbelieve generic cost estimates that are actually cheaper than the custom plans, on the grounds that they must be estimation errors. In principle a generic plan could never really be better than a custom plan; so if it looks that way on a cost basis, what that probably means is that the actual parameter values are outliers of some sort (e.g. extremely common), and the custom plan "knows" that it's going to be taking a hit from that, but the generic plan doesn't. In this sort of situation, going with the generic plan could be really disastrous, which is exactly what the OP is seeing (and what we've seen reported before). However, I'm not sure how to tune this idea so that it doesn't end up rejecting perfectly good generic plans. It's likely that there will be some variation in the cost estimates between the generic and specific cases, even if the plan structure is exactly the same; and that variation could go in either direction. regards, tom lane -
Re: Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Deepak Somaiya <deepsom@yahoo.com> — 2019-05-20T21:37:34Z
wow this is interesting! @Tom, Bruce, David - Experts Any idea why would changing the datatype would cause so much degradation - this is even when plan remains the same ,data is same. Deepak On Friday, May 17, 2019, 2:36:05 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote: Deepak, I changed the datatype from citext to text and now everything works fine. The data distribution is same, plan is same, yet there is a huge performance degradation when citext is used instead of text. However the business case requires case insensitive string handling. I am looking forward to some expert advice here when dealing with citext data type. -Thanks and Regards, Sameer Naik From: Deepak Somaiya [mailto:deepsom@yahoo.com] Sent: Thursday, May 9, 2019 9:44 AM To: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; Naik, Sameer <Sameer_Naik@bmc.com> Cc: pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans Sameer, were you able to resolve it? I am not sure if this is very common in postges - I doubt though but have not seen such a drastic performance degradation and that too when planner making the call. Deepak On Tuesday, April 30, 2019, 1:27:14 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote: >The problem seems to be that the actual values being used for >c400129200 and c400127400 are quite common in the dataset, so that when considering >Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) >the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case > -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) > Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) > it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. Distribution of the keys c400129200 and c400127400 . The distribution of c400129200 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400129200 is 0. For each of the remaining 55,373 records the value of c400129200 is distinct. The distribution of c400127400 is as follows- In entire table having 110743 records, there are 55370 records for which the value of c400127400 is 'DATASET1M' . For remaining 55,373 records the value of c400127400 the value is same and is ' 'DATASET2M' . -Thanks and Regards, Sameer Naik -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 29, 2019 8:06 PM To: Bruce Momjian <bruce@momjian.us> Cc: Naik, Sameer <Sameer_Naik@bmc.com>;pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Generic> Plans for Prepared Statement are 158155 times slower than Custom Plans Bruce Momjian <bruce@momjian.us> writes: > On Mon, Apr 29, 2019 at 10:36:20AM +0000, Naik, Sameer wrote: >> Since Postgres 9.2, for prepared statements, the CBO automatically >> switches from Custom Plan to Generic plan on the sixth iteration >> (reference backend/ utils/cache/plancache.c). > This is not totally true. Yeah, that's a pretty inaccurate statement of the behavior. The problem seems to be that the actual values being used for c400129200 and c400127400 are quite common in the dataset, so that when considering Filter: ... (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext) the planner makes a roughly correct assessment that there are a lot of such rows, so it prefers to index on the basis of the giant OR clause instead, even though that's fairly expensive. But, when considering the generic case -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1190.399..5544.385 rows=48 loops=1) Index Cond: ((c400129200 = $1) AND (c400127400 = $2)) it's evidently guessing that just a few rows will match the index condition (no more than about 3 given the cost number), making this plan look much cheaper, so it goes with this plan. I wonder what the actual distribution of those keys is. In v10 and later, it's quite possible that creating extended stats on the combination of those two columns would produce a better estimate. Won't help OP on 9.6, though. This isn't the first time we've seen a plan-choice failure of this sort. I've wondered if we should make the plancache simply disbelieve generic cost estimates that are actually cheaper than the custom plans, on the grounds that they must be estimation errors. In principle a generic plan could never really be better than a custom plan; so if it looks that way on a cost basis, what that probably means is that the actual parameter values are outliers of some sort (e.g. extremely common), and the custom plan "knows" that it's going to be taking a hit from that, but the generic plan doesn't. In this sort of situation, going with the generic plan could be really disastrous, which is exactly what the OP is seeing (and what we've seen reported before). However, I'm not sure how to tune this idea so that it doesn't end up rejecting perfectly good generic plans. It's likely that there will be some variation in the cost estimates between the generic and specific cases, even if the plan structure is exactly the same; and that variation could go in either direction. regards, tom lane -
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Tomas Vondra <tomas.vondra@2ndquadrant.com> — 2019-05-20T22:16:31Z
On Mon, May 20, 2019 at 09:37:34PM +0000, Deepak Somaiya wrote: > wow this is interesting! >@Tom, Bruce, David - Experts >Any idea why would changing the datatype would cause so much degradation - this is even when plan remains the same ,data is same. >Deepak > On Friday, May 17, 2019, 2:36:05 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote: > > >Deepak, > >I changed the datatype from citext to text and now everything works fine. > >The data distribution is same, plan is same, yet there is a huge performance degradation when citext is used instead of text. > >However the business case requires case insensitive string handling. > >I am looking forward to some expert advice here when dealing with citext data type. > > It's generally a good idea to share explain analyze output for both versions of the query - both with citext and text. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
-
RE: Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Naik, Sameer <sameer_naik@bmc.com> — 2019-05-23T06:37:19Z
@Tom, Bruce, David >> It's generally a good idea to share explain analyze output for both versions of the query - both with citext and text. Below are the queries and explain plan output(custom plan and generic plan) for both versions (with citext and text) Case Insensitive - PREPARE slowQuery (citext,citext,int,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext) as SELECT T776.C179, T776.C1 FROM T776 WHERE ( (T776.C400129200 = $1) AND ( T776.C400127400 = $2 ) AND ( (T776.C400129100 <> $3) OR ( T776.C400129100 IS NULL ) ) AND ( (T776.C179 = $4) OR ( T776.C179 = $5 ) OR ( T776.C179 = $6 ) OR ( T776.C179 = $7 ) OR ( T776.C179 = $8 ) OR ( T776.C179 = $9 ) OR ( T776.C179 = $10 ) OR ( T776.C179 = $11 ) OR ( T776.C179 = $12 ) OR ( T776.C179 = $13 ) OR ( T776.C179 = $14 ) OR ( T776.C179 = $15 ) OR ( T776.C179 = $16 ) OR ( T776.C179 = $17 ) OR ( T776.C179 = $18 ) OR ( T776.C179 = $19 ) OR ( T776.C179 = $20 ) OR ( T776.C179 = $21 ) OR ( T776.C179 = $22 ) OR ( T776.C179 = $23 ) OR ( T776.C179 = $24 ) OR ( T776.C179 = $25 ) OR ( T776.C179 = $26 ) OR ( T776.C179 = $27 ) OR ( T776.C179 = $28 ) OR ( T776.C179 = $29 ) OR ( T776.C179 = $30 ) OR ( T776.C179 = $31 ) OR ( T776.C179 = $32 ) OR ( T776.C179 = $33 ) OR ( T776.C179 = $34 ) OR ( T776.C179 = $35 ) OR ( T776.C179 = $36 ) OR ( T776.C179 = $37 ) OR ( T776.C179 = $38 ) OR ( T776.C179 = $39 ) OR ( T776.C179 = $40 ) OR ( T776.C179 = $41 ) OR ( T776.C179 = $42 ) OR ( T776.C179 = $43 ) OR ( T776.C179 = $44 ) OR ( T776.C179 = $45 ) OR ( T776.C179 = $46 ) OR ( T776.C179 = $47 ) OR ( T776.C179 = $48 ) OR ( T776.C179 = $49 ) OR ( T776.C179 = $50 ) OR ( T776.C179 = $51 ) ) ) ORDER BY T776.C1 ASC LIMIT 2001 OFFSET 0 select count(*) from T776 where C400129200='0' Explain (analyze,buffers) Execute slowQuery('0'::citext,'DATASET1M'::citext, 1,'OI-d791e838d0354ea59aa1c04622b7c8be'::citext, 'OI-44502144c7be49f4840d9d30c724f11b'::citext, 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext, 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext, 'OI-9239a9fa93c9459387d564940c0b4289'::citext, 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext, 'OI-8e365fa8461043a69950a638d3f3830a'::citext, 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext, 'OI-df0d9473d3934de29435d1c22fc9a269'::citext, 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext, 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext, 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext, 'OI-d0c049f6459e4174bb4e2ea025104298'::citext, 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext, 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext, 'OI-4316868d400d450fb60bb620a89778f2'::citext, 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext, 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext, 'OI-02577caeab904f37b6d13bb761805e02'::citext, 'OI-ecde76cbefd847ed9602a2c875529123'::citext, 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext, 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext, 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext, 'OI-81071273eacc44c4a46180be3a7d6a04'::citext, 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext, 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext, 'OI-7fc180b8d2944391b41ed90d70915357'::citext, 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext, 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext, 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext, 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext, 'OI-78263146f1694c39935578c3fa4c6415'::citext, 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext, 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext, 'OI-83e223fa1b364ac8b20e396b21387758'::citext, 'OI-a6eb0ec674d242b793a26b259d15435f'::citext, 'OI-195dfbe207a64130b3bc686bfdabe051'::citext, 'OI-7ba86277cbce489694ba03c98e7d2059'::citext, 'OI-c7675935bd974244939ccac9181d9129'::citext, 'OI-64c958575289438bb86455ed81517df1'::citext, 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext, 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext, 'OI-42de43dda54a4a018c0038c0de241da1'::citext, 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext, 'OI-56e85efaaa5f42c0913fed3745687a23'::citext, 'OI-def2602379db49cfadf6c31d7dfc4872'::citext, 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext, 'OI-6f3333da01f349a3a17a5714a82530a6'::citext) Custom Plan for Case Insensitive --- 'Limit (cost=402.71..402.74 rows=12 width=52) (actual time=4.724..4.803 rows=48 loops=1)' ' Buffers: shared hit=139 read=53' ' -> Sort (cost=402.71..402.74 rows=12 width=52) (actual time=4.720..4.747 rows=48 loops=1)' ' Sort Key: c1' ' Sort Method: quicksort Memory: 31kB' ' Buffers: shared hit=139 read=53' ' -> Bitmap Heap Scan on t776 (cost=212.54..402.49 rows=12 width=52) (actual time=3.715..4.040 rows=48 loops=1)' ' Recheck Cond: ((c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::citext) OR (c179 = 'OI-44502144c7be49f4840d9d30c724f11b'::citext) OR (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext) OR (c179 = 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext) OR (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::citext) OR (c179 = 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext) OR (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::citext) OR (c179 = 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext) OR (c179 = 'OI-df0d9473d3934de29435d1c22fc9a269'::citext) OR (c179 = 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext) OR (c179 = 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext) OR (c179 = 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext) OR (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::citext) OR (c179 = 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext) OR (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext) OR (c179 = 'OI-4316868d400d450fb60bb620a89778f2'::citext) OR (c179 = 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext) OR (c179 = 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext) OR (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::citext) OR (c179 = 'OI-ecde76cbefd847ed9602a2c875529123'::citext) OR (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext) OR (c179 = 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext) OR (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext) OR (c179 = 'OI-81071273eacc44c4a46180be3a7d6a04'::citext) OR (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext) OR (c179 = 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext) OR (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::citext) OR (c179 = 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext) OR (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext) OR (c179 = 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext) OR (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext) OR (c179 = 'OI-78263146f1694c39935578c3fa4c6415'::citext) OR (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext) OR (c179 = 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext) OR (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::citext) OR (c179 = 'OI-a6eb0ec674d242b793a26b259d15435f'::citext) OR (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::citext) OR (c179 = 'OI-7ba86277cbce489694ba03c98e7d2059'::citext) OR (c179 = 'OI-c7675935bd974244939ccac9181d9129'::citext) OR (c179 = 'OI-64c958575289438bb86455ed81517df1'::citext) OR (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext) OR (c179 = 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext) OR (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext) OR (c179 = 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext) OR (c179 = 'OI-56e85efaaa5f42c0913fed3745687a23'::citext) OR (c179 = 'OI-def2602379db49cfadf6c31d7dfc4872'::citext) OR (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext) OR (c179 = 'OI-6f3333da01f349a3a17a5714a82530a6'::citext))' ' Filter: (((c400129100 <> 1) OR (c400129100 IS NULL)) AND (c400129200 = '0'::citext) AND (c400127400 = 'DATASET1M'::citext))' ' Heap Blocks: exact=39' ' Buffers: shared hit=131 read=53' ' -> BitmapOr (cost=212.54..212.54 rows=48 width=0) (actual time=3.690..3.690 rows=0 loops=1)' ' Buffers: shared hit=92 read=53' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.157..0.157 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::citext)' ' Buffers: shared read=3' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.163..0.163 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-44502144c7be49f4840d9d30c724f11b'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.075..0.075 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.101..0.101 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.071..0.071 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.073..0.073 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-df0d9473d3934de29435d1c22fc9a269'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.096..0.096 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.051..0.051 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext)' ' Buffers: shared hit=3' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext)' ' Buffers: shared hit=3' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.101..0.101 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.055..0.055 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-4316868d400d450fb60bb620a89778f2'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.063..0.063 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.080..0.080 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.072..0.072 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-ecde76cbefd847ed9602a2c875529123'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.071..0.071 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-81071273eacc44c4a46180be3a7d6a04'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.064..0.064 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.072..0.072 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.088..0.088 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.068..0.068 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.057..0.057 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.091..0.091 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.068..0.068 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-78263146f1694c39935578c3fa4c6415'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.071..0.071 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.083..0.083 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-a6eb0ec674d242b793a26b259d15435f'::citext)' ' Buffers: shared hit=1 read=2' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.073..0.073 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.051..0.051 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-7ba86277cbce489694ba03c98e7d2059'::citext)' ' Buffers: shared hit=3' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.079..0.079 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-c7675935bd974244939ccac9181d9129'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.081..0.081 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-64c958575289438bb86455ed81517df1'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.084..0.084 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.077..0.077 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.069..0.069 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext)' ' Buffers: shared hit=3 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.066..0.066 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-56e85efaaa5f42c0913fed3745687a23'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-def2602379db49cfadf6c31d7dfc4872'::citext)' ' Buffers: shared hit=3' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.070..0.070 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext)' ' Buffers: shared hit=2 read=1' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.067..0.067 rows=1 loops=1)' ' Index Cond: (c179 = 'OI-6f3333da01f349a3a17a5714a82530a6'::citext)' ' Buffers: shared hit=2 read=1' 'Execution time: 5.150 ms' Generic Plan for Case Insensitive --- 'Limit (cost=12.67..12.68 rows=1 width=52) (actual time=5531.555..5531.634 rows=48 loops=1)' ' Buffers: shared hit=54716 read=398' ' -> Sort (cost=12.67..12.68 rows=1 width=52) (actual time=5531.552..5531.580 rows=48 loops=1)' ' Sort Key: c1' ' Sort Method: quicksort Memory: 31kB' ' Buffers: shared hit=54716 read=398' ' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1187.686..5531.421 rows=48 loops=1)' ' Index Cond: ((c400129200 = $1) AND (c400127400 = $2))' ' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND ((c179 = $4) OR (c179 = $5) OR (c179 = $6) OR (c179 = $7) OR (c179 = $8) OR (c179 = $9) OR (c179 = $10) OR (c179 = $11) OR (c179 = $12) OR (c179 = $13) OR (c179 = $14) OR (c179 = $15) OR (c179 = $16) OR (c179 = $17) OR (c179 = $18) OR (c179 = $19) OR (c179 = $20) OR (c179 = $21) OR (c179 = $22) OR (c179 = $23) OR (c179 = $24) OR (c179 = $25) OR (c179 = $26) OR (c179 = $27) OR (c179 = $28) OR (c179 = $29) OR (c179 = $30) OR (c179 = $31) OR (c179 = $32) OR (c179 = $33) OR (c179 = $34) OR (c179 = $35) OR (c179 = $36) OR (c179 = $37) OR (c179 = $38) OR (c179 = $39) OR (c179 = $40) OR (c179 = $41) OR (c179 = $42) OR (c179 = $43) OR (c179 = $44) OR (c179 = $45) OR (c179 = $46) OR (c179 = $47) OR (c179 = $48) OR (c179 = $49) OR (c179 = $50) OR (c179 = $51)))' ' Rows Removed by Filter: 55322' ' Buffers: shared hit=54716 read=398' 'Execution time: 5531.741 ms' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Case Sensitive - PREPARE fastquery (text,text,int,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text) as SELECT T776.C179, T776.C1, T776.C400129200 FROM T776 WHERE ( (T776.C400129200 = $1) AND ( T776.C400127400 = $2 ) AND ( (T776.C400129100 <> $3) OR ( T776.C400129100 IS NULL ) ) AND ( (T776.C179 = $4) OR ( T776.C179 = $5 ) OR ( T776.C179 = $6 ) OR ( T776.C179 = $7 ) OR ( T776.C179 = $8 ) OR ( T776.C179 = $9 ) OR ( T776.C179 = $10 ) OR ( T776.C179 = $11 ) OR ( T776.C179 = $12 ) OR ( T776.C179 = $13 ) OR ( T776.C179 = $14 ) OR ( T776.C179 = $15 ) OR ( T776.C179 = $16 ) OR ( T776.C179 = $17 ) OR ( T776.C179 = $18 ) OR ( T776.C179 = $19 ) OR ( T776.C179 = $20 ) OR ( T776.C179 = $21 ) OR ( T776.C179 = $22 ) OR ( T776.C179 = $23 ) OR ( T776.C179 = $24 ) OR ( T776.C179 = $25 ) OR ( T776.C179 = $26 ) OR ( T776.C179 = $27 ) OR ( T776.C179 = $28 ) OR ( T776.C179 = $29 ) OR ( T776.C179 = $30 ) OR ( T776.C179 = $31 ) OR ( T776.C179 = $32 ) OR ( T776.C179 = $33 ) OR ( T776.C179 = $34 ) OR ( T776.C179 = $35 ) OR ( T776.C179 = $36 ) OR ( T776.C179 = $37 ) OR ( T776.C179 = $38 ) OR ( T776.C179 = $39 ) OR ( T776.C179 = $40 ) OR ( T776.C179 = $41 ) OR ( T776.C179 = $42 ) OR ( T776.C179 = $43 ) OR ( T776.C179 = $44 ) OR ( T776.C179 = $45 ) OR ( T776.C179 = $46 ) OR ( T776.C179 = $47 ) OR ( T776.C179 = $48 ) OR ( T776.C179 = $49 ) OR ( T776.C179 = $50 ) OR ( T776.C179 = $51 ) ) ) ORDER BY T776.C1 ASC LIMIT 2001 OFFSET 0; EXPLAIN analyze EXECUTE fastquery ('0','DATASET1M', 1,N'OI-941ed5dc3b644849afd6bae91ebf02d1','OI-476186266411406ba9967c732fc6f1f2','OI-d627a532701942129f531c74ab40e05b','OI-6d2c55fa269c47789130f05afc8ffa6d','OI-f1734c5368c4496c9a13035b8b236d13','OI-a63664f325144f958332044a4ea2705c','OI-70f148ef11e241409191faf63650a8a8','OI-c24bc2a9e24b4c8b8c9c11061a1bf631','OI-27ec4c51369d49958fc04ae9a6fe547f','OI-0555e41446ef420d93a78214f5253e1c','OI-95e0ca98affb4d5ebab38fe1990cf4be','OI-800e9fb833724a8585920f7a169556eb','OI-1c11e40c56904ecea9a78653f04bde84','OI-4b8f52e78d124ba89d7fde2b0fb6a720','OI-1d64f5df07ee490c88cdacabb5eb740a','OI-af68ae5b648f46ab926d9fafde6a5bb7','OI-5a0f26ba1d35460d953316496f7b7899','OI-3709034c00774804801227d21a5b1e41','OI-11fe926e91db4950b1c24159bb2022da','OI-836924722a304f8a86ff88783166e437','OI-c3a1738a5d384544b70dc3670831033f','OI-467d16d39a0e45dbbefdf20ec3c68b0c','OI-ceee9fa8436a4f72991883387074b744','OI-523324e70f8f4ae3b717b29a82776f33','OI-1a790b65e7c7458ba1567bd2c2ff35be','OI-4115e27566474081b0881ea8de0fcb88','OI-b9366dd534ae4d16a92e17abca8ae097','OI-3c3d9217564e4a82b43a230aa6e3f091','OI-8ca511ce33a84941868bd59b3e54b6b0','OI-77b1d7fa60ce4aa9899c4a56b6037cc6','OI-cd099418c1394100b7c14de9306521bd','OI-fc32fa20d0fb4e40bfad8c361889bcb6','OI-0e7ff2d492d5476b8d390456b4d619f0','OI-289fbe99682948ae86eb8e1fbf7e2350','OI-1e8ac9e7b1924505919c5e703838be54','OI-15672685a4ee4642a9f2f4926c8dace0','OI-1d6eb6a8fb0c437593d46099ef8544ed','OI-ba1326a7763240b19f0ac49934e815ac','OI-ce1e718ec2a844c383743755b976fc70','OI-454967f97851473baba213b03f4099d3','OI-699ac5def19744bf9ceee531b1c4b05d','OI-8f7140b0c06b482e8c8d9123cfe23d73','OI-295d7dc1291f45e1abf8354e735a191a','OI-813ad79d8ed14dff82a6ae0960c65515','OI-28d4d1da3a284f2e8ce5de08d8049819','OI-e0da6cbc49f44977b147cecf9da3c0c2','OI-2bf0a9c92a0543019fcefeb7b227dbf8','OI-e4fd3311fe7240019b6344ad0e357c4c') Custom Plan for Case Sensitive- 'Limit (cost=404.05..404.08 rows=12 width=70) (actual time=0.740..0.818 rows=48 loops=1)' ' -> Sort (cost=404.05..404.08 rows=12 width=70) (actual time=0.737..0.765 rows=48 loops=1)' ' Sort Key: c1' ' Sort Method: quicksort Memory: 31kB' ' -> Bitmap Heap Scan on t776 (cost=212.54..403.83 rows=12 width=70) (actual time=0.530..0.624 rows=48 loops=1)' ' Recheck Cond: (((c179)::text = 'OI-941ed5dc3b644849afd6bae91ebf02d1'::text) OR ((c179)::text = 'OI-476186266411406ba9967c732fc6f1f2'::text) OR ((c179)::text = 'OI-d627a532701942129f531c74ab40e05b'::text) OR ((c179)::text = 'OI-6d2c55fa269c47789130f05afc8ffa6d'::text) OR ((c179)::text = 'OI-f1734c5368c4496c9a13035b8b236d13'::text) OR ((c179)::text = 'OI-a63664f325144f958332044a4ea2705c'::text) OR ((c179)::text = 'OI-70f148ef11e241409191faf63650a8a8'::text) OR ((c179)::text = 'OI-c24bc2a9e24b4c8b8c9c11061a1bf631'::text) OR ((c179)::text = 'OI-27ec4c51369d49958fc04ae9a6fe547f'::text) OR ((c179)::text = 'OI-0555e41446ef420d93a78214f5253e1c'::text) OR ((c179)::text = 'OI-95e0ca98affb4d5ebab38fe1990cf4be'::text) OR ((c179)::text = 'OI-800e9fb833724a8585920f7a169556eb'::text) OR ((c179)::text = 'OI-1c11e40c56904ecea9a78653f04bde84'::text) OR ((c179)::text = 'OI-4b8f52e78d124ba89d7fde2b0fb6a720'::text) OR ((c179)::text = 'OI-1d64f5df07ee490c88cdacabb5eb740a'::text) OR ((c179)::text = 'OI-af68ae5b648f46ab926d9fafde6a5bb7'::text) OR ((c179)::text = 'OI-5a0f26ba1d35460d953316496f7b7899'::text) OR ((c179)::text = 'OI-3709034c00774804801227d21a5b1e41'::text) OR ((c179)::text = 'OI-11fe926e91db4950b1c24159bb2022da'::text) OR ((c179)::text = 'OI-836924722a304f8a86ff88783166e437'::text) OR ((c179)::text = 'OI-c3a1738a5d384544b70dc3670831033f'::text) OR ((c179)::text = 'OI-467d16d39a0e45dbbefdf20ec3c68b0c'::text) OR ((c179)::text = 'OI-ceee9fa8436a4f72991883387074b744'::text) OR ((c179)::text = 'OI-523324e70f8f4ae3b717b29a82776f33'::text) OR ((c179)::text = 'OI-1a790b65e7c7458ba1567bd2c2ff35be'::text) OR ((c179)::text = 'OI-4115e27566474081b0881ea8de0fcb88'::text) OR ((c179)::text = 'OI-b9366dd534ae4d16a92e17abca8ae097'::text) OR ((c179)::text = 'OI-3c3d9217564e4a82b43a230aa6e3f091'::text) OR ((c179)::text = 'OI-8ca511ce33a84941868bd59b3e54b6b0'::text) OR ((c179)::text = 'OI-77b1d7fa60ce4aa9899c4a56b6037cc6'::text) OR ((c179)::text = 'OI-cd099418c1394100b7c14de9306521bd'::text) OR ((c179)::text = 'OI-fc32fa20d0fb4e40bfad8c361889bcb6'::text) OR ((c179)::text = 'OI-0e7ff2d492d5476b8d390456b4d619f0'::text) OR ((c179)::text = 'OI-289fbe99682948ae86eb8e1fbf7e2350'::text) OR ((c179)::text = 'OI-1e8ac9e7b1924505919c5e703838be54'::text) OR ((c179)::text = 'OI-15672685a4ee4642a9f2f4926c8dace0'::text) OR ((c179)::text = 'OI-1d6eb6a8fb0c437593d46099ef8544ed'::text) OR ((c179)::text = 'OI-ba1326a7763240b19f0ac49934e815ac'::text) OR ((c179)::text = 'OI-ce1e718ec2a844c383743755b976fc70'::text) OR ((c179)::text = 'OI-454967f97851473baba213b03f4099d3'::text) OR ((c179)::text = 'OI-699ac5def19744bf9ceee531b1c4b05d'::text) OR ((c179)::text = 'OI-8f7140b0c06b482e8c8d9123cfe23d73'::text) OR ((c179)::text = 'OI-295d7dc1291f45e1abf8354e735a191a'::text) OR ((c179)::text = 'OI-813ad79d8ed14dff82a6ae0960c65515'::text) OR ((c179)::text = 'OI-28d4d1da3a284f2e8ce5de08d8049819'::text) OR ((c179)::text = 'OI-e0da6cbc49f44977b147cecf9da3c0c2'::text) OR ((c179)::text = 'OI-2bf0a9c92a0543019fcefeb7b227dbf8'::text) OR ((c179)::text = 'OI-e4fd3311fe7240019b6344ad0e357c4c'::text))' ' Filter: (((c400129100 <> 1) OR (c400129100 IS NULL)) AND ((c400129200)::text = '0'::text) AND ((c400127400)::text = 'DATASET1M'::text))' ' Heap Blocks: exact=41' ' -> BitmapOr (cost=212.54..212.54 rows=48 width=0) (actual time=0.516..0.516 rows=0 loops=1)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.023..0.023 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-941ed5dc3b644849afd6bae91ebf02d1'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.011..0.011 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-476186266411406ba9967c732fc6f1f2'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-d627a532701942129f531c74ab40e05b'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-6d2c55fa269c47789130f05afc8ffa6d'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-f1734c5368c4496c9a13035b8b236d13'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-a63664f325144f958332044a4ea2705c'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-70f148ef11e241409191faf63650a8a8'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-c24bc2a9e24b4c8b8c9c11061a1bf631'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-27ec4c51369d49958fc04ae9a6fe547f'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-0555e41446ef420d93a78214f5253e1c'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-95e0ca98affb4d5ebab38fe1990cf4be'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-800e9fb833724a8585920f7a169556eb'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-1c11e40c56904ecea9a78653f04bde84'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-4b8f52e78d124ba89d7fde2b0fb6a720'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-1d64f5df07ee490c88cdacabb5eb740a'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-af68ae5b648f46ab926d9fafde6a5bb7'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-5a0f26ba1d35460d953316496f7b7899'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-3709034c00774804801227d21a5b1e41'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-11fe926e91db4950b1c24159bb2022da'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-836924722a304f8a86ff88783166e437'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-c3a1738a5d384544b70dc3670831033f'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.043..0.043 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-467d16d39a0e45dbbefdf20ec3c68b0c'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-ceee9fa8436a4f72991883387074b744'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-523324e70f8f4ae3b717b29a82776f33'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-1a790b65e7c7458ba1567bd2c2ff35be'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-4115e27566474081b0881ea8de0fcb88'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-b9366dd534ae4d16a92e17abca8ae097'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-3c3d9217564e4a82b43a230aa6e3f091'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-8ca511ce33a84941868bd59b3e54b6b0'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-77b1d7fa60ce4aa9899c4a56b6037cc6'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-cd099418c1394100b7c14de9306521bd'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-fc32fa20d0fb4e40bfad8c361889bcb6'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-0e7ff2d492d5476b8d390456b4d619f0'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-289fbe99682948ae86eb8e1fbf7e2350'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-1e8ac9e7b1924505919c5e703838be54'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-15672685a4ee4642a9f2f4926c8dace0'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-1d6eb6a8fb0c437593d46099ef8544ed'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.010..0.010 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-ba1326a7763240b19f0ac49934e815ac'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-ce1e718ec2a844c383743755b976fc70'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-454967f97851473baba213b03f4099d3'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-699ac5def19744bf9ceee531b1c4b05d'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-8f7140b0c06b482e8c8d9123cfe23d73'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-295d7dc1291f45e1abf8354e735a191a'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-813ad79d8ed14dff82a6ae0960c65515'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-28d4d1da3a284f2e8ce5de08d8049819'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-e0da6cbc49f44977b147cecf9da3c0c2'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.009..0.009 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-2bf0a9c92a0543019fcefeb7b227dbf8'::text)' ' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual time=0.008..0.008 rows=1 loops=1)' ' Index Cond: ((c179)::text = 'OI-e4fd3311fe7240019b6344ad0e357c4c'::text)' 'Execution time: 1.013 ms' Generic Plan for Case Sensitive - 'Limit (cost=12.74..12.75 rows=1 width=70) (actual time=185.728..185.806 rows=48 loops=1)' ' -> Sort (cost=12.74..12.75 rows=1 width=70) (actual time=185.726..185.753 rows=48 loops=1)' ' Sort Key: c1' ' Sort Method: quicksort Memory: 31kB' ' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.73 rows=1 width=70) (actual time=39.277..185.650 rows=48 loops=1)' ' Index Cond: (((c400129200)::text = $1) AND ((c400127400)::text = $2))' ' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND (((c179)::text = $4) OR ((c179)::text = $5) OR ((c179)::text = $6) OR ((c179)::text = $7) OR ((c179)::text = $8) OR ((c179)::text = $9) OR ((c179)::text = $10) OR ((c179)::text = $11) OR ((c179)::text = $12) OR ((c179)::text = $13) OR ((c179)::text = $14) OR ((c179)::text = $15) OR ((c179)::text = $16) OR ((c179)::text = $17) OR ((c179)::text = $18) OR ((c179)::text = $19) OR ((c179)::text = $20) OR ((c179)::text = $21) OR ((c179)::text = $22) OR ((c179)::text = $23) OR ((c179)::text = $24) OR ((c179)::text = $25) OR ((c179)::text = $26) OR ((c179)::text = $27) OR ((c179)::text = $28) OR ((c179)::text = $29) OR ((c179)::text = $30) OR ((c179)::text = $31) OR ((c179)::text = $32) OR ((c179)::text = $33) OR ((c179)::text = $34) OR ((c179)::text = $35) OR ((c179)::text = $36) OR ((c179)::text = $37) OR ((c179)::text = $38) OR ((c179)::text = $39) OR ((c179)::text = $40) OR ((c179)::text = $41) OR ((c179)::text = $42) OR ((c179)::text = $43) OR ((c179)::text = $44) OR ((c179)::text = $45) OR ((c179)::text = $46) OR ((c179)::text = $47) OR ((c179)::text = $48) OR ((c179)::text = $49) OR ((c179)::text = $50) OR ((c179)::text = $51)))' ' Rows Removed by Filter: 55322' 'Execution time: 185.916 ms' -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -Thanks and Regards, Sameer Naik -----Original Message----- From: Tomas Vondra <tomas.vondra@2ndquadrant.com> Sent: Tuesday, May 21, 2019 3:47 AM To: Deepak Somaiya <deepsom@yahoo.com> Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; david@kineticode.com; Naik, Sameer <Sameer_Naik@bmc.com>; pgsql-performance@lists.postgresql.org Subject: [EXTERNAL] Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans On Mon, May 20, 2019 at 09:37:34PM +0000, Deepak Somaiya wrote: > wow this is interesting! >@Tom, Bruce, David - Experts >Any idea why would changing the datatype would cause so much degradation - this is even when plan remains the same ,data is same. >Deepak > On Friday, May 17, 2019, 2:36:05 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote: > > >Deepak, > >I changed the datatype from citext to text and now everything works fine. > >The data distribution is same, plan is same, yet there is a huge performance degradation when citext is used instead of text. > >However the business case requires case insensitive string handling. > >I am looking forward to some expert advice here when dealing with citext data type. > > It's generally a good idea to share explain analyze output for both versions of the query - both with citext and text. regards -- Tomas Vondra https://urldefense.proofpoint.com/v2/url?u=http-3A__www.2ndQuadrant.com&d=DwIDAw&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=K893err8oTutgRKCeLUAsHd_iqcPBdCmI71ID5BjsTk&m=3dYLVBgo4Y0o0EkCgQ-pKShXctMnCCJCaKme72rIPeI&s=XeEyBe6Oi1N5Bqgt9HnirKF_kBqs5QYEgNtxf8UZiyc&e= PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
Tom Lane <tgl@sss.pgh.pa.us> — 2019-05-23T17:17:30Z
"Naik, Sameer" <Sameer_Naik@bmc.com> writes: > On Mon, May 20, 2019 at 09:37:34PM +0000, Deepak Somaiya wrote: >> wow this is interesting! >> @Tom, Bruce, David - Experts >> Any idea why would changing the datatype would cause so much degradation - this is even when plan remains the same ,data is same. I see nothing very exciting here. text equality comparison reduces to a memcmp, while citext equality comparison is quite expensive, since it has to case-fold both inputs before it can memcmp them. For the given test case: > ' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual time=1187.686..5531.421 rows=48 loops=1)' > ' Index Cond: ((c400129200 = $1) AND (c400127400 = $2))' > ' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND ((c179 = $4) OR (c179 = $5) OR (c179 = $6) OR (c179 = $7) OR (c179 = $8) OR (c179 = $9) OR (c179 = $10) OR (c179 = $11) OR (c179 = $12) OR (c179 = $13) OR (c179 = $14) OR (c179 = $15) OR (c179 = $16) OR (c179 = $17) OR (c179 = $18) OR (c179 = $19) OR (c179 = $20) OR (c179 = $21) OR (c179 = $22) OR (c179 = $23) OR (c179 = $24) OR (c179 = $25) OR (c179 = $26) OR (c179 = $27) OR (c179 = $28) OR (c179 = $29) OR (c179 = $30) OR (c179 = $31) OR (c179 = $32) OR (c179 = $33) OR (c179 = $34) OR (c179 = $35) OR (c179 = $36) OR (c179 = $37) OR (c179 = $38) OR (c179 = $39) OR (c179 = $40) OR (c179 = $41) OR (c179 = $42) OR (c179 = $43) OR (c179 = $44) OR (c179 = $45) OR (c179 = $46) OR (c179 = $47) OR (c179 = $48) OR (c179 = $49) OR (c179 = $50) OR (c179 = $51)))' > ' Rows Removed by Filter: 55322' it's reasonable to suppose that not many of the rows are failing the c400129100 conditions, so that in order to decide that a row doesn't pass the filter, we are forced to perform each of the OR'd c179 comparisons. So this query did something like 48 * 55322 equality comparisons for c179. If the cost of a citexteq evaluation is around 2 microseconds, that'd fully explain the runtime differential. The OP didn't say what locale or encoding he's using. Maybe switching to some other settings would improve matters ... though if non-ASCII case folding is a business requirement, that likely won't go far. Or you could get rid of the need for the repetitive case-folding, say by storing lower(c179) in a separate column and doing plain text comparisons to pre-lowercased input values. regards, tom lane