| From: | "Naik, Sameer" <Sameer_Naik(at)bmc(dot)com> |
|---|---|
| To: | "pgsql-performance(at)lists(dot)postgresql(dot)org" <pgsql-performance(at)lists(dot)postgresql(dot)org> |
| Subject: | Generic Plans for Prepared Statement are 158155 times slower than Custom Plans |
| Date: | 2019-04-29 10:36:20 |
| Message-ID: | a77204aa4c9b45a8bc86fe511e254ff4@hou-exmbprd-03.adprod.bmc.com |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bruce Momjian | 2019-04-29 13:36:07 | Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans |
| Previous Message | Tomas Vondra | 2019-04-28 14:19:01 | Re: Out of Memory errors are frustrating as heck! |