RE: Re: Generic Plans for Prepared Statement are 158155 times slower thanCustom Plans
| От | Naik, Sameer |
|---|---|
| Тема | RE: Re: Generic Plans for Prepared Statement are 158155 times slower thanCustom Plans |
| Дата | |
| Msg-id | 33f1f723e93b4b93b1d48933ac3ef434@hou-exmbprd-03.adprod.bmc.com обсуждение исходный текст |
| Ответ на | Re: Generic Plans for Prepared Statement are 158155 times slowerthan Custom Plans (Tomas Vondra <tomas.vondra@2ndquadrant.com>) |
| Ответы |
Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
|
| Список | pgsql-performance |
@Tom, Bruce, David
>> It's generally a good idea to share explain analyze output for both versions of the query - both with citext and
text.
Below are the queries and explain plan output(custom plan and generic plan) for both versions (with citext and text)
Case Insensitive -
PREPARE slowQuery
(citext,citext,int,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext,citext)
as
SELECT
T776.C179,
T776.C1
FROM
T776
WHERE
(
(T776.C400129200 = $1)
AND
(
T776.C400127400 = $2
)
AND
(
(T776.C400129100 <> $3)
OR
(
T776.C400129100 IS NULL
)
)
AND
(
(T776.C179 = $4)
OR
(
T776.C179 = $5
)
OR
(
T776.C179 = $6
)
OR
(
T776.C179 = $7
)
OR
(
T776.C179 = $8
)
OR
(
T776.C179 = $9
)
OR
(
T776.C179 = $10
)
OR
(
T776.C179 = $11
)
OR
(
T776.C179 = $12
)
OR
(
T776.C179 = $13
)
OR
(
T776.C179 = $14
)
OR
(
T776.C179 = $15
)
OR
(
T776.C179 = $16
)
OR
(
T776.C179 = $17
)
OR
(
T776.C179 = $18
)
OR
(
T776.C179 = $19
)
OR
(
T776.C179 = $20
)
OR
(
T776.C179 = $21
)
OR
(
T776.C179 = $22
)
OR
(
T776.C179 = $23
)
OR
(
T776.C179 = $24
)
OR
(
T776.C179 = $25
)
OR
(
T776.C179 = $26
)
OR
(
T776.C179 = $27
)
OR
(
T776.C179 = $28
)
OR
(
T776.C179 = $29
)
OR
(
T776.C179 = $30
)
OR
(
T776.C179 = $31
)
OR
(
T776.C179 = $32
)
OR
(
T776.C179 = $33
)
OR
(
T776.C179 = $34
)
OR
(
T776.C179 = $35
)
OR
(
T776.C179 = $36
)
OR
(
T776.C179 = $37
)
OR
(
T776.C179 = $38
)
OR
(
T776.C179 = $39
)
OR
(
T776.C179 = $40
)
OR
(
T776.C179 = $41
)
OR
(
T776.C179 = $42
)
OR
(
T776.C179 = $43
)
OR
(
T776.C179 = $44
)
OR
(
T776.C179 = $45
)
OR
(
T776.C179 = $46
)
OR
(
T776.C179 = $47
)
OR
(
T776.C179 = $48
)
OR
(
T776.C179 = $49
)
OR
(
T776.C179 = $50
)
OR
(
T776.C179 = $51
)
)
)
ORDER BY
T776.C1 ASC LIMIT 2001 OFFSET 0
select count(*) from T776 where C400129200='0'
Explain (analyze,buffers) Execute slowQuery('0'::citext,'DATASET1M'::citext,
1,'OI-d791e838d0354ea59aa1c04622b7c8be'::citext,'OI-44502144c7be49f4840d9d30c724f11b'::citext,
'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext,'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext,
'OI-9239a9fa93c9459387d564940c0b4289'::citext,'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext,
'OI-8e365fa8461043a69950a638d3f3830a'::citext,'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext,
'OI-df0d9473d3934de29435d1c22fc9a269'::citext,'OI-bd704daa55d24f12a54da6d5df68d05c'::citext,
'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext,'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext,
'OI-d0c049f6459e4174bb4e2ea025104298'::citext,'OI-f5fca0c13c454a04939b6f6a4871d647'::citext,
'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext,'OI-4316868d400d450fb60bb620a89778f2'::citext,
'OI-4abdb84db1414bd1abbb66f2a35de267'::citext,'OI-fbb28f59448d44adb65c1145b94e23fc'::citext,
'OI-02577caeab904f37b6d13bb761805e02'::citext,'OI-ecde76cbefd847ed9602a2c875529123'::citext,
'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext,'OI-55cf16be8f6e43aba7813d7dd898432c'::citext,
'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext,'OI-81071273eacc44c4a46180be3a7d6a04'::citext,
'OI-74cf5387522b4a238483b258f3b0bb7a'::citext,'OI-0ed0ff8956a84c598226f7e71f37f012'::citext,
'OI-7fc180b8d2944391b41ed90d70915357'::citext,'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext,
'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext,'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext,
'OI-8f298f3c25c24f28943dd8cd98df748f'::citext,'OI-78263146f1694c39935578c3fa4c6415'::citext,
'OI-ce1c830ed02540a58c3aaea265fa52af'::citext,'OI-8dd73d417cf84827bc3708a362c7ee40'::citext,
'OI-83e223fa1b364ac8b20e396b21387758'::citext,'OI-a6eb0ec674d242b793a26b259d15435f'::citext,
'OI-195dfbe207a64130b3bc686bfdabe051'::citext,'OI-7ba86277cbce489694ba03c98e7d2059'::citext,
'OI-c7675935bd974244939ccac9181d9129'::citext,'OI-64c958575289438bb86455ed81517df1'::citext,
'OI-05e14b018be14c4ea60f977f91b3fe04'::citext,'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext,
'OI-42de43dda54a4a018c0038c0de241da1'::citext,'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext,
'OI-56e85efaaa5f42c0913fed3745687a23'::citext,'OI-def2602379db49cfadf6c31d7dfc4872'::citext,
'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext,'OI-6f3333da01f349a3a17a5714a82530a6'::citext)
Custom Plan for Case Insensitive ---
'Limit (cost=402.71..402.74 rows=12 width=52) (actual time=4.724..4.803 rows=48 loops=1)'
' Buffers: shared hit=139 read=53'
' -> Sort (cost=402.71..402.74 rows=12 width=52) (actual time=4.720..4.747 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' Buffers: shared hit=139 read=53'
' -> Bitmap Heap Scan on t776 (cost=212.54..402.49 rows=12 width=52) (actual time=3.715..4.040 rows=48
loops=1)'
' Recheck Cond: ((c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::citext) OR (c179 =
'OI-44502144c7be49f4840d9d30c724f11b'::citext)OR (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext) OR (c179 =
'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext)OR (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::citext) OR (c179 =
'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext)OR (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::citext) OR (c179 =
'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext)OR (c179 = 'OI-df0d9473d3934de29435d1c22fc9a269'::citext) OR (c179 =
'OI-bd704daa55d24f12a54da6d5df68d05c'::citext)OR (c179 = 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext) OR (c179 =
'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext)OR (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::citext) OR (c179 =
'OI-f5fca0c13c454a04939b6f6a4871d647'::citext)OR (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext) OR (c179 =
'OI-4316868d400d450fb60bb620a89778f2'::citext)OR (c179 = 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext) OR (c179 =
'OI-fbb28f59448d44adb65c1145b94e23fc'::citext)OR (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::citext) OR (c179 =
'OI-ecde76cbefd847ed9602a2c875529123'::citext)OR (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext) OR (c179 =
'OI-55cf16be8f6e43aba7813d7dd898432c'::citext)OR (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext) OR (c179 =
'OI-81071273eacc44c4a46180be3a7d6a04'::citext)OR (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext) OR (c179 =
'OI-0ed0ff8956a84c598226f7e71f37f012'::citext)OR (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::citext) OR (c179 =
'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext)OR (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext) OR (c179 =
'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext)OR (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext) OR (c179 =
'OI-78263146f1694c39935578c3fa4c6415'::citext)OR (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext) OR (c179 =
'OI-8dd73d417cf84827bc3708a362c7ee40'::citext)OR (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::citext) OR (c179 =
'OI-a6eb0ec674d242b793a26b259d15435f'::citext)OR (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::citext) OR (c179 =
'OI-7ba86277cbce489694ba03c98e7d2059'::citext)OR (c179 = 'OI-c7675935bd974244939ccac9181d9129'::citext) OR (c179 =
'OI-64c958575289438bb86455ed81517df1'::citext)OR (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext) OR (c179 =
'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext)OR (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext) OR (c179 =
'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext)OR (c179 = 'OI-56e85efaaa5f42c0913fed3745687a23'::citext) OR (c179 =
'OI-def2602379db49cfadf6c31d7dfc4872'::citext)OR (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext) OR (c179 =
'OI-6f3333da01f349a3a17a5714a82530a6'::citext))'
' Filter: (((c400129100 <> 1) OR (c400129100 IS NULL)) AND (c400129200 = '0'::citext) AND (c400127400 =
'DATASET1M'::citext))'
' Heap Blocks: exact=39'
' Buffers: shared hit=131 read=53'
' -> BitmapOr (cost=212.54..212.54 rows=48 width=0) (actual time=3.690..3.690 rows=0 loops=1)'
' Buffers: shared hit=92 read=53'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.157..0.157rows=1 loops=1)'
' Index Cond: (c179 = 'OI-d791e838d0354ea59aa1c04622b7c8be'::citext)'
' Buffers: shared read=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.163..0.163rows=1 loops=1)'
' Index Cond: (c179 = 'OI-44502144c7be49f4840d9d30c724f11b'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.075..0.075rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4c4f9f3bb1a344f294612cfeb1ac6838'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.077..0.077rows=1 loops=1)'
' Index Cond: (c179 = 'OI-dd23d23ea6ca459ab6fc3256682df66a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.091..0.091rows=1 loops=1)'
' Index Cond: (c179 = 'OI-9239a9fa93c9459387d564940c0b4289'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.101..0.101rows=1 loops=1)'
' Index Cond: (c179 = 'OI-f268ba1f12014f07b1b34fd9050aa92d'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.071..0.071rows=1 loops=1)'
' Index Cond: (c179 = 'OI-8e365fa8461043a69950a638d3f3830a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.067..0.067rows=1 loops=1)'
' Index Cond: (c179 = 'OI-da2e9a38f45b41e9baea8c35b45577dc'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.073..0.073rows=1 loops=1)'
' Index Cond: (c179 = 'OI-df0d9473d3934de29435d1c22fc9a269'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.096..0.096rows=1 loops=1)'
' Index Cond: (c179 = 'OI-bd704daa55d24f12a54da6d5df68d05c'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.051..0.051rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4bed7c372fd44b2e96dd4bce44e2ab79'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.050..0.050rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4c0afdbbcb394670b8d93e39aa403e86'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.070..0.070rows=1 loops=1)'
' Index Cond: (c179 = 'OI-d0c049f6459e4174bb4e2ea025104298'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.101..0.101rows=1 loops=1)'
' Index Cond: (c179 = 'OI-f5fca0c13c454a04939b6f6a4871d647'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.055..0.055rows=1 loops=1)'
' Index Cond: (c179 = 'OI-fb0e56e0b896448cbd3adff8212b3ddc'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.066..0.066rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4316868d400d450fb60bb620a89778f2'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.069..0.069rows=1 loops=1)'
' Index Cond: (c179 = 'OI-4abdb84db1414bd1abbb66f2a35de267'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.063..0.063rows=1 loops=1)'
' Index Cond: (c179 = 'OI-fbb28f59448d44adb65c1145b94e23fc'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.080..0.080rows=1 loops=1)'
' Index Cond: (c179 = 'OI-02577caeab904f37b6d13bb761805e02'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.072..0.072rows=1 loops=1)'
' Index Cond: (c179 = 'OI-ecde76cbefd847ed9602a2c875529123'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.071..0.071rows=1 loops=1)'
' Index Cond: (c179 = 'OI-7b6e946f4e074cf6a8cd2fcec864cc3e'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.069..0.069rows=1 loops=1)'
' Index Cond: (c179 = 'OI-55cf16be8f6e43aba7813d7dd898432c'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.070..0.070rows=1 loops=1)'
' Index Cond: (c179 = 'OI-e1903455cdc14ce1a8f05a43ee452a7f'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.066..0.066rows=1 loops=1)'
' Index Cond: (c179 = 'OI-81071273eacc44c4a46180be3a7d6a04'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.066..0.066rows=1 loops=1)'
' Index Cond: (c179 = 'OI-74cf5387522b4a238483b258f3b0bb7a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.064..0.064rows=1 loops=1)'
' Index Cond: (c179 = 'OI-0ed0ff8956a84c598226f7e71f37f012'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.072..0.072rows=1 loops=1)'
' Index Cond: (c179 = 'OI-7fc180b8d2944391b41ed90d70915357'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.088..0.088rows=1 loops=1)'
' Index Cond: (c179 = 'OI-1f9e9cc0d2c4481199f98c898abf8b1b'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.068..0.068rows=1 loops=1)'
' Index Cond: (c179 = 'OI-5dfbe9c70fe64a4080052f1d36ad654a'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.057..0.057rows=1 loops=1)'
' Index Cond: (c179 = 'OI-ff83ae4d7a5a4906b97f2f78122324e4'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.091..0.091rows=1 loops=1)'
' Index Cond: (c179 = 'OI-8f298f3c25c24f28943dd8cd98df748f'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.068..0.068rows=1 loops=1)'
' Index Cond: (c179 = 'OI-78263146f1694c39935578c3fa4c6415'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.071..0.071rows=1 loops=1)'
' Index Cond: (c179 = 'OI-ce1c830ed02540a58c3aaea265fa52af'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.069..0.069rows=1 loops=1)'
' Index Cond: (c179 = 'OI-8dd73d417cf84827bc3708a362c7ee40'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.070..0.070rows=1 loops=1)'
' Index Cond: (c179 = 'OI-83e223fa1b364ac8b20e396b21387758'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.083..0.083rows=1 loops=1)'
' Index Cond: (c179 = 'OI-a6eb0ec674d242b793a26b259d15435f'::citext)'
' Buffers: shared hit=1 read=2'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.073..0.073rows=1 loops=1)'
' Index Cond: (c179 = 'OI-195dfbe207a64130b3bc686bfdabe051'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.051..0.051rows=1 loops=1)'
' Index Cond: (c179 = 'OI-7ba86277cbce489694ba03c98e7d2059'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.079..0.079rows=1 loops=1)'
' Index Cond: (c179 = 'OI-c7675935bd974244939ccac9181d9129'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.081..0.081rows=1 loops=1)'
' Index Cond: (c179 = 'OI-64c958575289438bb86455ed81517df1'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.084..0.084rows=1 loops=1)'
' Index Cond: (c179 = 'OI-05e14b018be14c4ea60f977f91b3fe04'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.077..0.077rows=1 loops=1)'
' Index Cond: (c179 = 'OI-462d7db8d54541b996bbc977e3f4e6ec'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.069..0.069rows=1 loops=1)'
' Index Cond: (c179 = 'OI-42de43dda54a4a018c0038c0de241da1'::citext)'
' Buffers: shared hit=3 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.067..0.067rows=1 loops=1)'
' Index Cond: (c179 = 'OI-e31f38e2a95e44bfa8b71ee1d31a66fa'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.066..0.066rows=1 loops=1)'
' Index Cond: (c179 = 'OI-56e85efaaa5f42c0913fed3745687a23'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.050..0.050rows=1 loops=1)'
' Index Cond: (c179 = 'OI-def2602379db49cfadf6c31d7dfc4872'::citext)'
' Buffers: shared hit=3'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.070..0.070rows=1 loops=1)'
' Index Cond: (c179 = 'OI-d81dc80af7af4ad8a8383e9834207e0b'::citext)'
' Buffers: shared hit=2 read=1'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.067..0.067rows=1 loops=1)'
' Index Cond: (c179 = 'OI-6f3333da01f349a3a17a5714a82530a6'::citext)'
' Buffers: shared hit=2 read=1'
'Execution time: 5.150 ms'
Generic Plan for Case Insensitive ---
'Limit (cost=12.67..12.68 rows=1 width=52) (actual time=5531.555..5531.634 rows=48 loops=1)'
' Buffers: shared hit=54716 read=398'
' -> Sort (cost=12.67..12.68 rows=1 width=52) (actual time=5531.552..5531.580 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' Buffers: shared hit=54716 read=398'
' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.66 rows=1 width=52) (actual
time=1187.686..5531.421rows=48 loops=1)'
' Index Cond: ((c400129200 = $1) AND (c400127400 = $2))'
' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND ((c179 = $4) OR (c179 = $5) OR (c179 = $6) OR
(c179= $7) OR (c179 = $8) OR (c179 = $9) OR (c179 = $10) OR (c179 = $11) OR (c179 = $12) OR (c179 = $13) OR (c179 =
$14)OR (c179 = $15) OR (c179 = $16) OR (c179 = $17) OR (c179 = $18) OR (c179 = $19) OR (c179 = $20) OR (c179 = $21) OR
(c179= $22) OR (c179 = $23) OR (c179 = $24) OR (c179 = $25) OR (c179 = $26) OR (c179 = $27) OR (c179 = $28) OR (c179 =
$29)OR (c179 = $30) OR (c179 = $31) OR (c179 = $32) OR (c179 = $33) OR (c179 = $34) OR (c179 = $35) OR (c179 = $36) OR
(c179= $37) OR (c179 = $38) OR (c179 = $39) OR (c179 = $40) OR (c179 = $41) OR (c179 = $42) OR (c179 = $43) OR (c179 =
$44)OR (c179 = $45) OR (c179 = $46) OR (c179 = $47) OR (c179 = $48) OR (c179 = $49) OR (c179 = $50) OR (c179 = $51)))'
' Rows Removed by Filter: 55322'
' Buffers: shared hit=54716 read=398'
'Execution time: 5531.741 ms'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Case Sensitive -
PREPARE fastquery
(text,text,int,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text,text)
as
SELECT
T776.C179,
T776.C1,
T776.C400129200
FROM
T776
WHERE
(
(T776.C400129200 = $1)
AND
(
T776.C400127400 = $2
)
AND
(
(T776.C400129100 <> $3)
OR
(
T776.C400129100 IS NULL
)
)
AND
(
(T776.C179 = $4)
OR
(
T776.C179 = $5
)
OR
(
T776.C179 = $6
)
OR
(
T776.C179 = $7
)
OR
(
T776.C179 = $8
)
OR
(
T776.C179 = $9
)
OR
(
T776.C179 = $10
)
OR
(
T776.C179 = $11
)
OR
(
T776.C179 = $12
)
OR
(
T776.C179 = $13
)
OR
(
T776.C179 = $14
)
OR
(
T776.C179 = $15
)
OR
(
T776.C179 = $16
)
OR
(
T776.C179 = $17
)
OR
(
T776.C179 = $18
)
OR
(
T776.C179 = $19
)
OR
(
T776.C179 = $20
)
OR
(
T776.C179 = $21
)
OR
(
T776.C179 = $22
)
OR
(
T776.C179 = $23
)
OR
(
T776.C179 = $24
)
OR
(
T776.C179 = $25
)
OR
(
T776.C179 = $26
)
OR
(
T776.C179 = $27
)
OR
(
T776.C179 = $28
)
OR
(
T776.C179 = $29
)
OR
(
T776.C179 = $30
)
OR
(
T776.C179 = $31
)
OR
(
T776.C179 = $32
)
OR
(
T776.C179 = $33
)
OR
(
T776.C179 = $34
)
OR
(
T776.C179 = $35
)
OR
(
T776.C179 = $36
)
OR
(
T776.C179 = $37
)
OR
(
T776.C179 = $38
)
OR
(
T776.C179 = $39
)
OR
(
T776.C179 = $40
)
OR
(
T776.C179 = $41
)
OR
(
T776.C179 = $42
)
OR
(
T776.C179 = $43
)
OR
(
T776.C179 = $44
)
OR
(
T776.C179 = $45
)
OR
(
T776.C179 = $46
)
OR
(
T776.C179 = $47
)
OR
(
T776.C179 = $48
)
OR
(
T776.C179 = $49
)
OR
(
T776.C179 = $50
)
OR
(
T776.C179 = $51
)
)
)
ORDER BY
T776.C1 ASC LIMIT 2001 OFFSET 0;
EXPLAIN analyze EXECUTE fastquery ('0','DATASET1M',
1,N'OI-941ed5dc3b644849afd6bae91ebf02d1','OI-476186266411406ba9967c732fc6f1f2','OI-d627a532701942129f531c74ab40e05b','OI-6d2c55fa269c47789130f05afc8ffa6d','OI-f1734c5368c4496c9a13035b8b236d13','OI-a63664f325144f958332044a4ea2705c','OI-70f148ef11e241409191faf63650a8a8','OI-c24bc2a9e24b4c8b8c9c11061a1bf631','OI-27ec4c51369d49958fc04ae9a6fe547f','OI-0555e41446ef420d93a78214f5253e1c','OI-95e0ca98affb4d5ebab38fe1990cf4be','OI-800e9fb833724a8585920f7a169556eb','OI-1c11e40c56904ecea9a78653f04bde84','OI-4b8f52e78d124ba89d7fde2b0fb6a720','OI-1d64f5df07ee490c88cdacabb5eb740a','OI-af68ae5b648f46ab926d9fafde6a5bb7','OI-5a0f26ba1d35460d953316496f7b7899','OI-3709034c00774804801227d21a5b1e41','OI-11fe926e91db4950b1c24159bb2022da','OI-836924722a304f8a86ff88783166e437','OI-c3a1738a5d384544b70dc3670831033f','OI-467d16d39a0e45dbbefdf20ec3c68b0c','OI-ceee9fa8436a4f72991883387074b744','OI-523324e70f8f4ae3b717b29a82776f33','OI-1a790b65e7c7458ba1567bd2c2ff35be','OI-4115e27566474081b0881ea8de0fcb88','OI-b9366dd534ae4d16a92e17abca8ae097','OI-3c3d9217564e4a82b43a230aa6e3f091','OI-8ca511ce33a84941868bd59b3e54b6b0','OI-77b1d7fa60ce4aa9899c4a56b6037cc6','OI-cd099418c1394100b7c14de9306521bd','OI-fc32fa20d0fb4e40bfad8c361889bcb6','OI-0e7ff2d492d5476b8d390456b4d619f0','OI-289fbe99682948ae86eb8e1fbf7e2350','OI-1e8ac9e7b1924505919c5e703838be54','OI-15672685a4ee4642a9f2f4926c8dace0','OI-1d6eb6a8fb0c437593d46099ef8544ed','OI-ba1326a7763240b19f0ac49934e815ac','OI-ce1e718ec2a844c383743755b976fc70','OI-454967f97851473baba213b03f4099d3','OI-699ac5def19744bf9ceee531b1c4b05d','OI-8f7140b0c06b482e8c8d9123cfe23d73','OI-295d7dc1291f45e1abf8354e735a191a','OI-813ad79d8ed14dff82a6ae0960c65515','OI-28d4d1da3a284f2e8ce5de08d8049819','OI-e0da6cbc49f44977b147cecf9da3c0c2','OI-2bf0a9c92a0543019fcefeb7b227dbf8','OI-e4fd3311fe7240019b6344ad0e357c4c')
Custom Plan for Case Sensitive-
'Limit (cost=404.05..404.08 rows=12 width=70) (actual time=0.740..0.818 rows=48 loops=1)'
' -> Sort (cost=404.05..404.08 rows=12 width=70) (actual time=0.737..0.765 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' -> Bitmap Heap Scan on t776 (cost=212.54..403.83 rows=12 width=70) (actual time=0.530..0.624 rows=48
loops=1)'
' Recheck Cond: (((c179)::text = 'OI-941ed5dc3b644849afd6bae91ebf02d1'::text) OR ((c179)::text =
'OI-476186266411406ba9967c732fc6f1f2'::text)OR ((c179)::text = 'OI-d627a532701942129f531c74ab40e05b'::text) OR
((c179)::text= 'OI-6d2c55fa269c47789130f05afc8ffa6d'::text) OR ((c179)::text =
'OI-f1734c5368c4496c9a13035b8b236d13'::text)OR ((c179)::text = 'OI-a63664f325144f958332044a4ea2705c'::text) OR
((c179)::text= 'OI-70f148ef11e241409191faf63650a8a8'::text) OR ((c179)::text =
'OI-c24bc2a9e24b4c8b8c9c11061a1bf631'::text)OR ((c179)::text = 'OI-27ec4c51369d49958fc04ae9a6fe547f'::text) OR
((c179)::text= 'OI-0555e41446ef420d93a78214f5253e1c'::text) OR ((c179)::text =
'OI-95e0ca98affb4d5ebab38fe1990cf4be'::text)OR ((c179)::text = 'OI-800e9fb833724a8585920f7a169556eb'::text) OR
((c179)::text= 'OI-1c11e40c56904ecea9a78653f04bde84'::text) OR ((c179)::text =
'OI-4b8f52e78d124ba89d7fde2b0fb6a720'::text)OR ((c179)::text = 'OI-1d64f5df07ee490c88cdacabb5eb740a'::text) OR
((c179)::text= 'OI-af68ae5b648f46ab926d9fafde6a5bb7'::text) OR ((c179)::text =
'OI-5a0f26ba1d35460d953316496f7b7899'::text)OR ((c179)::text = 'OI-3709034c00774804801227d21a5b1e41'::text) OR
((c179)::text= 'OI-11fe926e91db4950b1c24159bb2022da'::text) OR ((c179)::text =
'OI-836924722a304f8a86ff88783166e437'::text)OR ((c179)::text = 'OI-c3a1738a5d384544b70dc3670831033f'::text) OR
((c179)::text= 'OI-467d16d39a0e45dbbefdf20ec3c68b0c'::text) OR ((c179)::text =
'OI-ceee9fa8436a4f72991883387074b744'::text)OR ((c179)::text = 'OI-523324e70f8f4ae3b717b29a82776f33'::text) OR
((c179)::text= 'OI-1a790b65e7c7458ba1567bd2c2ff35be'::text) OR ((c179)::text =
'OI-4115e27566474081b0881ea8de0fcb88'::text)OR ((c179)::text = 'OI-b9366dd534ae4d16a92e17abca8ae097'::text) OR
((c179)::text= 'OI-3c3d9217564e4a82b43a230aa6e3f091'::text) OR ((c179)::text =
'OI-8ca511ce33a84941868bd59b3e54b6b0'::text)OR ((c179)::text = 'OI-77b1d7fa60ce4aa9899c4a56b6037cc6'::text) OR
((c179)::text= 'OI-cd099418c1394100b7c14de9306521bd'::text) OR ((c179)::text =
'OI-fc32fa20d0fb4e40bfad8c361889bcb6'::text)OR ((c179)::text = 'OI-0e7ff2d492d5476b8d390456b4d619f0'::text) OR
((c179)::text= 'OI-289fbe99682948ae86eb8e1fbf7e2350'::text) OR ((c179)::text =
'OI-1e8ac9e7b1924505919c5e703838be54'::text)OR ((c179)::text = 'OI-15672685a4ee4642a9f2f4926c8dace0'::text) OR
((c179)::text= 'OI-1d6eb6a8fb0c437593d46099ef8544ed'::text) OR ((c179)::text =
'OI-ba1326a7763240b19f0ac49934e815ac'::text)OR ((c179)::text = 'OI-ce1e718ec2a844c383743755b976fc70'::text) OR
((c179)::text= 'OI-454967f97851473baba213b03f4099d3'::text) OR ((c179)::text =
'OI-699ac5def19744bf9ceee531b1c4b05d'::text)OR ((c179)::text = 'OI-8f7140b0c06b482e8c8d9123cfe23d73'::text) OR
((c179)::text= 'OI-295d7dc1291f45e1abf8354e735a191a'::text) OR ((c179)::text =
'OI-813ad79d8ed14dff82a6ae0960c65515'::text)OR ((c179)::text = 'OI-28d4d1da3a284f2e8ce5de08d8049819'::text) OR
((c179)::text= 'OI-e0da6cbc49f44977b147cecf9da3c0c2'::text) OR ((c179)::text =
'OI-2bf0a9c92a0543019fcefeb7b227dbf8'::text)OR ((c179)::text = 'OI-e4fd3311fe7240019b6344ad0e357c4c'::text))'
' Filter: (((c400129100 <> 1) OR (c400129100 IS NULL)) AND ((c400129200)::text = '0'::text) AND
((c400127400)::text= 'DATASET1M'::text))'
' Heap Blocks: exact=41'
' -> BitmapOr (cost=212.54..212.54 rows=48 width=0) (actual time=0.516..0.516 rows=0 loops=1)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.023..0.023rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-941ed5dc3b644849afd6bae91ebf02d1'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.011..0.011rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-476186266411406ba9967c732fc6f1f2'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.010..0.010rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-d627a532701942129f531c74ab40e05b'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-6d2c55fa269c47789130f05afc8ffa6d'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-f1734c5368c4496c9a13035b8b236d13'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-a63664f325144f958332044a4ea2705c'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-70f148ef11e241409191faf63650a8a8'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-c24bc2a9e24b4c8b8c9c11061a1bf631'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-27ec4c51369d49958fc04ae9a6fe547f'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-0555e41446ef420d93a78214f5253e1c'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-95e0ca98affb4d5ebab38fe1990cf4be'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-800e9fb833724a8585920f7a169556eb'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1c11e40c56904ecea9a78653f04bde84'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-4b8f52e78d124ba89d7fde2b0fb6a720'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1d64f5df07ee490c88cdacabb5eb740a'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-af68ae5b648f46ab926d9fafde6a5bb7'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-5a0f26ba1d35460d953316496f7b7899'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-3709034c00774804801227d21a5b1e41'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-11fe926e91db4950b1c24159bb2022da'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-836924722a304f8a86ff88783166e437'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-c3a1738a5d384544b70dc3670831033f'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.043..0.043rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-467d16d39a0e45dbbefdf20ec3c68b0c'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.010..0.010rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-ceee9fa8436a4f72991883387074b744'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-523324e70f8f4ae3b717b29a82776f33'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1a790b65e7c7458ba1567bd2c2ff35be'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-4115e27566474081b0881ea8de0fcb88'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-b9366dd534ae4d16a92e17abca8ae097'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-3c3d9217564e4a82b43a230aa6e3f091'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-8ca511ce33a84941868bd59b3e54b6b0'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-77b1d7fa60ce4aa9899c4a56b6037cc6'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-cd099418c1394100b7c14de9306521bd'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-fc32fa20d0fb4e40bfad8c361889bcb6'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-0e7ff2d492d5476b8d390456b4d619f0'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-289fbe99682948ae86eb8e1fbf7e2350'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1e8ac9e7b1924505919c5e703838be54'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-15672685a4ee4642a9f2f4926c8dace0'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-1d6eb6a8fb0c437593d46099ef8544ed'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.010..0.010rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-ba1326a7763240b19f0ac49934e815ac'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-ce1e718ec2a844c383743755b976fc70'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-454967f97851473baba213b03f4099d3'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-699ac5def19744bf9ceee531b1c4b05d'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-8f7140b0c06b482e8c8d9123cfe23d73'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-295d7dc1291f45e1abf8354e735a191a'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-813ad79d8ed14dff82a6ae0960c65515'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-28d4d1da3a284f2e8ce5de08d8049819'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-e0da6cbc49f44977b147cecf9da3c0c2'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.009..0.009rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-2bf0a9c92a0543019fcefeb7b227dbf8'::text)'
' -> Bitmap Index Scan on i776_0_179_t776 (cost=0.00..4.43 rows=1 width=0) (actual
time=0.008..0.008rows=1 loops=1)'
' Index Cond: ((c179)::text = 'OI-e4fd3311fe7240019b6344ad0e357c4c'::text)'
'Execution time: 1.013 ms'
Generic Plan for Case Sensitive -
'Limit (cost=12.74..12.75 rows=1 width=70) (actual time=185.728..185.806 rows=48 loops=1)'
' -> Sort (cost=12.74..12.75 rows=1 width=70) (actual time=185.726..185.753 rows=48 loops=1)'
' Sort Key: c1'
' Sort Method: quicksort Memory: 31kB'
' -> Index Scan using i776_0_400129200_t776 on t776 (cost=0.42..12.73 rows=1 width=70) (actual
time=39.277..185.650rows=48 loops=1)'
' Index Cond: (((c400129200)::text = $1) AND ((c400127400)::text = $2))'
' Filter: (((c400129100 <> $3) OR (c400129100 IS NULL)) AND (((c179)::text = $4) OR ((c179)::text = $5) OR
((c179)::text= $6) OR ((c179)::text = $7) OR ((c179)::text = $8) OR ((c179)::text = $9) OR ((c179)::text = $10) OR
((c179)::text= $11) OR ((c179)::text = $12) OR ((c179)::text = $13) OR ((c179)::text = $14) OR ((c179)::text = $15) OR
((c179)::text= $16) OR ((c179)::text = $17) OR ((c179)::text = $18) OR ((c179)::text = $19) OR ((c179)::text = $20) OR
((c179)::text= $21) OR ((c179)::text = $22) OR ((c179)::text = $23) OR ((c179)::text = $24) OR ((c179)::text = $25) OR
((c179)::text= $26) OR ((c179)::text = $27) OR ((c179)::text = $28) OR ((c179)::text = $29) OR ((c179)::text = $30) OR
((c179)::text= $31) OR ((c179)::text = $32) OR ((c179)::text = $33) OR ((c179)::text = $34) OR ((c179)::text = $35) OR
((c179)::text= $36) OR ((c179)::text = $37) OR ((c179)::text = $38) OR ((c179)::text = $39) OR ((c179)::text = $40) OR
((c179)::text= $41) OR ((c179)::text = $42) OR ((c179)::text = $43) OR ((c179)::text = $44) OR ((c179)::text = $45) OR
((c179)::text= $46) OR ((c179)::text = $47) OR ((c179)::text = $48) OR ((c179)::text = $49) OR ((c179)::text = $50) OR
((c179)::text= $51)))'
' Rows Removed by Filter: 55322'
'Execution time: 185.916 ms'
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-Thanks and Regards,
Sameer Naik
-----Original Message-----
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Sent: Tuesday, May 21, 2019 3:47 AM
To: Deepak Somaiya <deepsom@yahoo.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; Bruce Momjian <bruce@momjian.us>; david@kineticode.com; Naik, Sameer
<Sameer_Naik@bmc.com>;pgsql-performance@lists.postgresql.org
Subject: [EXTERNAL] Re: Generic Plans for Prepared Statement are 158155 times slower than Custom Plans
On Mon, May 20, 2019 at 09:37:34PM +0000, Deepak Somaiya wrote:
> wow this is interesting!
>@Tom, Bruce, David - Experts
>Any idea why would changing the datatype would cause so much degradation - this is even when plan remains the same
,datais same.
>Deepak
> On Friday, May 17, 2019, 2:36:05 AM PDT, Naik, Sameer <Sameer_Naik@bmc.com> wrote:
>
>
>Deepak,
>
>I changed the datatype from citext to text and now everything works fine.
>
>The data distribution is same, plan is same, yet there is a huge performance degradation when citext is used instead
oftext.
>
>However the business case requires case insensitive string handling.
>
>I am looking forward to some expert advice here when dealing with citext data type.
>
>
It's generally a good idea to share explain analyze output for both versions of the query - both with citext and text.
regards
--
Tomas Vondra
https://urldefense.proofpoint.com/v2/url?u=http-3A__www.2ndQuadrant.com&d=DwIDAw&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=K893err8oTutgRKCeLUAsHd_iqcPBdCmI71ID5BjsTk&m=3dYLVBgo4Y0o0EkCgQ-pKShXctMnCCJCaKme72rIPeI&s=XeEyBe6Oi1N5Bqgt9HnirKF_kBqs5QYEgNtxf8UZiyc&e=
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
В списке pgsql-performance по дате отправления:
Предыдущее
От: George NeunerДата:
Сообщение: Re: Use Postgres as a column store by creating one table per column
Следующее
От: Mariel CherkasskyДата:
Сообщение: Fwd: upgrade to PG11 on secondary fails (no initdb was launched)