Generic Plans for Prepared Statement are 158155 times slower thanCustom Plans

Поиск

Список

Период

Сортировка

От	Naik, Sameer
Тема	Generic Plans for Prepared Statement are 158155 times slower thanCustom Plans
Дата	29 апреля 2019 г. 13:36:20
Msg-id	a77204aa4c9b45a8bc86fe511e254ff4@hou-exmbprd-03.adprod.bmc.com обсуждение исходный текст
Ответы	Re: Generic Plans for Prepared Statement are 158155 times slowerthan Custom Plans
Список	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"

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

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)

(

T776.C400129100 IS NULL

)

AND

(

(T776.C179 = $4)

(

T776.C179 = $5

)

(

T776.C179 = $6

)

(

T776.C179 = $7

)

(

T776.C179 = $8

)

(

T776.C179 = $9

)

(

T776.C179 = $10

)

(

T776.C179 = $11

)

(

T776.C179 = $12

)

(

T776.C179 = $13

)

(

T776.C179 = $14

)

(

T776.C179 = $15

)

(

T776.C179 = $16

)

(

T776.C179 = $17

)

(

T776.C179 = $18

)

(

T776.C179 = $19

)

(

T776.C179 = $20

)

(

T776.C179 = $21

)

(

T776.C179 = $22

)

(

T776.C179 = $23

)

(

T776.C179 = $24

)

(

T776.C179 = $25

)

(

T776.C179 = $26

)

(

T776.C179 = $27

)

(

T776.C179 = $28

)

(

T776.C179 = $29

)

(

T776.C179 = $30

)

(

T776.C179 = $31

)

(

T776.C179 = $32

)

(

T776.C179 = $33

)

(

T776.C179 = $34

)

(

T776.C179 = $35

)

(

T776.C179 = $36

)

(

T776.C179 = $37

)

(

T776.C179 = $38

)

(

T776.C179 = $39

)

(

T776.C179 = $40

)

(

T776.C179 = $41

)

(

T776.C179 = $42

)

(

T776.C179 = $43

)

(

T776.C179 = $44

)

(

T776.C179 = $45

)

(

T776.C179 = $46

)

(

T776.C179 = $47

)

(

T776.C179 = $48

)

(

T776.C179 = $49

)

(

T776.C179 = $50

)

(

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)

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

В списке pgsql-performance по дате отправления:

Предыдущее

От: Tomas Vondra
Дата: 28 апреля 2019 г., 17:19:01
Сообщение: Re: Out of Memory errors are frustrating as heck!

Следующее

От: Bruce Momjian
Дата: 29 апреля 2019 г., 16:36:07
Сообщение: Re: Generic Plans for Prepared Statement are 158155 times slowerthan Custom Plans

Вход в личный кабинет

Восстановление пароля

Подтверждение аккаунта

Изменение пароля

Generic Plans for Prepared Statement are 158155 times slower thanCustom Plans

Предыдущее

Следующее