Problem is, if you're scanning a highly correlated index, the mechanism is counterproductive.
I would not expect this to make much of a difference on a table which is perfectly correlated with the index. You would have to create an accounts table which is randomly ordered to have a meaningful benchmark of the eic parameter.
If I read the postgres source code correctly, then the pages are sorted in tbm_begin_iterate() before being iterated, so I don't think correlation of index should matter. The tests on shuffled records show the same trend in execution time for different eic values.
I did some more tests, this time on DigitalOcean/SSD. I also tried different kernel versions (3.13 and 4.4). I've run each test several times.
Ubuntu 16.04.3 LTSLinux ubuntu-s-2vcpu-4gb-ams3-01 4.4.0-112-generic #135-Ubuntu SMP Fri Jan 19 11:48:36 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
select array_agg(aid) from (select aid from pgbench_accounts order by ctid limit 20)_;
array_agg
------------------------------------------------------
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
(1 row)
effective_io_concurrency=0 Execution time: 3910.770 ms
effective_io_concurrency=1 Execution time: 10754.483 ms
effective_io_concurrency=2 Execution time: 5347.845 ms
effective_io_concurrency=4 Execution time: 5737.166 ms
effective_io_concurrency=8 Execution time: 4904.962 ms
effective_io_concurrency=16 Execution time: 4947.941 ms
effective_io_concurrency=8 Execution time: 4737.117 ms
effective_io_concurrency=4 Execution time: 4749.065 ms
effective_io_concurrency=2 Execution time: 5031.390 ms
effective_io_concurrency=1 Execution time: 10117.927 ms
effective_io_concurrency=0 Execution time: 3769.260 ms
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
array_agg
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{14845391,12121312,18579380,9075771,7602183,762831,8485877,1035607,4451695,4686093,1925254,3462677,9634221,14144638,17894662,8247722,17996891,14842493,13832379,2052647}
(1 row)
effective_io_concurrency=0 Execution time: 6801.229 ms
effective_io_concurrency=1 Execution time: 14217.719 ms
effective_io_concurrency=2 Execution time: 9126.216 ms
effective_io_concurrency=4 Execution time: 8797.717 ms
effective_io_concurrency=8 Execution time: 8759.317 ms
effective_io_concurrency=16 Execution time: 8431.835 ms
effective_io_concurrency=8 Execution time: 9387.119 ms
effective_io_concurrency=4 Execution time: 9064.808 ms
effective_io_concurrency=2 Execution time: 9359.062 ms
effective_io_concurrency=1 Execution time: 16639.386 ms
effective_io_concurrency=0 Execution time: 6560.935 ms
Ubuntu 14.04.5 LTS
Linux ubuntu-s-2vcpu-4gb-ams3-02 3.13.0-139-generic #188-Ubuntu SMP Tue
Jan 9 14:43:09 UTC 2018 x86_64 x86_64 x86_64 GNU/Linux
PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
select array_agg(aid) from (select aid from pgbench_accounts order by ctid limit 20)_;
array_agg
------------------------------------------------------
{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20}
(1 row)
effective_io_concurrency=0 Execution time: 3760.865 ms
effective_io_concurrency=1 Execution time: 11092.846 ms
effective_io_concurrency=2 Execution time: 4933.662 ms
effective_io_concurrency=4 Execution time: 4733.713 ms
effective_io_concurrency=8 Execution time: 4860.886 ms
effective_io_concurrency=16 Execution time: 5063.696 ms
effective_io_concurrency=8 Execution time: 4670.155 ms
effective_io_concurrency=4 Execution time: 5049.901 ms
effective_io_concurrency=2 Execution time: 4785.219 ms
effective_io_concurrency=1 Execution time: 11106.143 ms
effective_io_concurrency=0 Execution time: 3779.058 ms
select array_agg(aid) from (select aid from pgbench_accounts order by
ctid limit 20)_;
array_agg
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
{8089611,788082,3477731,10034640,9256860,15432349,2412452,10087114,10386959,7199759,17253672,7798185,160908,1960920,13287370,14970792,18578221,13892448,3532901,3560583}
(1 row)
effective_io_concurrency=0 Execution time: 6243.600 ms
effective_io_concurrency=1 Execution time: 14613.348 ms
effective_io_concurrency=2 Execution time: 8250.552 ms
effective_io_concurrency=4 Execution time: 8286.333 ms
effective_io_concurrency=8 Execution time: 8167.817 ms
effective_io_concurrency=16 Execution time: 8193.186 ms
effective_io_concurrency=8 Execution time: 8206.614 ms
effective_io_concurrency=4 Execution time: 8375.153 ms
effective_io_concurrency=2 Execution time: 8354.106 ms
effective_io_concurrency=1 Execution time: 14139.712 ms
effective_io_concurrency=0 Execution time: 6409.229 ms
Looks like this behavior is not caused by, and does not depend on:
- variable performance in the cloud
- order of rows in the table
- whether the disk is EBS (backed by SSD or HDD), or ordinary SSD
- kernel version
Does this mean that the default setting for eic on Linux is just
inadequate for how the modern kernels behave? Or am I missing something
else in the tests?
Regards, Vitaliy
<<attachment: test-digitalocean-ubuntu14.zip>>
<<attachment: test-digitalocean-ubuntu16.zip>>
