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