I did some more tests. I've made an SQL dump of the table. Then used
head/tail commands to cut the data part. Then used shuf command to
shuffle rows, and then joined the pieces back and restored the table
back into DB.
Before:
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
effective_io_concurrency=0 Execution time: 1455.336 ms
effective_io_concurrency=1 Execution time: 8365.070 ms
effective_io_concurrency=2 Execution time: 4791.961 ms
effective_io_concurrency=4 Execution time: 4113.713 ms
effective_io_concurrency=8 Execution time: 1584.862 ms
effective_io_concurrency=16 Execution time: 1533.096 ms
effective_io_concurrency=8 Execution time: 1494.494 ms
effective_io_concurrency=4 Execution time: 3235.892 ms
effective_io_concurrency=2 Execution time: 4624.334 ms
effective_io_concurrency=1 Execution time: 7831.310 ms
effective_io_concurrency=0 Execution time: 1422.203 ms
After:
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
{6861090,18316007,2361004,11880097,5079470,9859942,13776329,12687163,3793362,18312052,15912971,9928864,10179242,9307499,2737986,13911147,5337329,12582498,3019085,4631617}
effective_io_concurrency=0 Execution time: 71321.723 ms
effective_io_concurrency=1 Execution time: 180230.742 ms
effective_io_concurrency=2 Execution time: 98635.566 ms
effective_io_concurrency=4 Execution time: 91464.375 ms
effective_io_concurrency=8 Execution time: 91048.939 ms
effective_io_concurrency=16 Execution time: 97682.475 ms
effective_io_concurrency=8 Execution time: 91262.404 ms
effective_io_concurrency=4 Execution time: 90945.560 ms
effective_io_concurrency=2 Execution time: 97019.504 ms
effective_io_concurrency=1 Execution time: 180331.474 ms
effective_io_concurrency=0 Execution time: 71469.484 ms
The numbers are not directly comparable with the previous tests, because
this time I used scale factor 200.
Regards,
Vitaliy
On 2018-02-01 20:39, Claudio Freire wrote:
On Wed, Jan 31, 2018 at 11:21 PM, hzzhangjiazhi
<hzzhangjiazhi@xxxxxxxxxxxxxxxx> wrote:
HI
I think this parameter will be usefull when the storage using RAID
stripe , otherwise turn up this parameter is meaningless when only has one
device。
Not at all. Especially on EBS, where keeping a relatively full queue
is necessary to get max thoughput out of the drive.
Problem is, if you're scanning a highly correlated index, the
mechanism is counterproductive. I had worked on some POC patches for
correcting that, I guess I could work something out, but it's
low-priority for me. Especially since it's actually a kernel "bug" (or
shortcoming), that could be fixed in the kernel rather than worked
around by postgres.
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1454.570..1454.570 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=447.877..447.877 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.782 ms
Execution time: 1455.336 ms
(11 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=8364.272..8364.272 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=448.043..448.043 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.036 ms
Execution time: 8365.070 ms
(11 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4791.196..4791.196 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=433.928..433.928 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.847 ms
Execution time: 4791.961 ms
(11 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4112.928..4112.928 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=470.873..470.873 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.694 ms
Execution time: 4113.713 ms
(11 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1584.067..1584.067 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=451.587..451.587 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.421 ms
Execution time: 1584.862 ms
(11 rows)
effective_io_concurrency=16
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1532.343..1532.343 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=429.148..429.148 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.802 ms
Execution time: 1533.096 ms
(11 rows)
effective_io_concurrency=8
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1493.721..1493.721 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=427.864..427.864 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.565 ms
Execution time: 1494.494 ms
(11 rows)
effective_io_concurrency=4
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=3235.104..3235.104 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=435.009..435.009 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.289 ms
Execution time: 3235.892 ms
(11 rows)
effective_io_concurrency=2
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=4623.565..4623.565 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=425.661..425.661 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.529 ms
Execution time: 4624.334 ms
(11 rows)
effective_io_concurrency=1
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=7830.530..7830.530 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=435.078..435.078 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.361 ms
Execution time: 7831.310 ms
(11 rows)
effective_io_concurrency=0
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=12838.24..357966.26 rows=1 width=97) (actual time=1421.417..1421.417 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=16378
Buffers: shared hit=2 read=19109
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..12838.24 rows=986230 width=0) (actual time=470.542..470.542 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.907 ms
Execution time: 1422.203 ms
(11 rows)
effective_io_concurrency=0
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=71320.201..71320.201 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=620.222..620.222 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 25.095 ms
Execution time: 71321.723 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=180229.423..180229.423 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=626.718..626.718 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 14.123 ms
Execution time: 180230.742 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=98634.125..98634.125 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=620.905..620.905 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 22.936 ms
Execution time: 98635.566 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91463.095..91463.095 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=643.029..643.029 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 13.676 ms
Execution time: 91464.375 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91047.653..91047.653 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=614.786..614.786 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 15.338 ms
Execution time: 91048.939 ms
(12 rows)
effective_io_concurrency=16
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=97680.861..97680.861 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=616.763..616.763 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.479 ms
Execution time: 97682.475 ms
(12 rows)
effective_io_concurrency=8
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=91260.958..91260.958 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=596.291..596.291 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.734 ms
Execution time: 91262.404 ms
(12 rows)
effective_io_concurrency=4
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=90944.102..90944.102 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=597.324..597.324 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.300 ms
Execution time: 90945.560 ms
(12 rows)
effective_io_concurrency=2
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=97018.175..97018.175 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=645.782..645.782 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 13.976 ms
Execution time: 97019.504 ms
(12 rows)
effective_io_concurrency=1
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=180330.061..180330.061 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=610.894..610.894 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 16.558 ms
Execution time: 180331.474 ms
(12 rows)
effective_io_concurrency=0
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on pgbench_accounts (cost=13070.16..358509.72 rows=1 width=97) (actual time=71467.974..71467.974 rows=0 loops=1)
Recheck Cond: ((aid >= 1000) AND (aid <= 1000000))
Rows Removed by Index Recheck: 11433521
Filter: (abalance <> 0)
Rows Removed by Filter: 999001
Heap Blocks: exact=115112 lossy=198588
Buffers: shared hit=3 read=316430
-> Bitmap Index Scan on pgbench_accounts_pkey (cost=0.00..13070.16 rows=1004032 width=0) (actual time=778.875..778.875 rows=999001 loops=1)
Index Cond: ((aid >= 1000) AND (aid <= 1000000))
Buffers: shared hit=2 read=2731
Planning time: 42.094 ms
Execution time: 71469.484 ms
(12 rows)
