>> Would a bitmap scan over expression index ((aid%1000)=0) do a sparse bitmap
>> scan?
> If you have a minimally correlated index (ie: totally random order),
> and suppose you have N tuples per page, you need to select less (much
> less) than 1/Nth of the table.
>
I've done a test with a sparse bitmap scan. The positive effect of
effective_io_concurrency is visible in that case.
In the test, I'm creating a table with 100k rows, 10 tuples per page.
Then I create an index on expression ((id%100)=0), and then query the
table using a bitmap scan over this index. Before each query, I also
restart postgresql service and clear OS caches, to make all reads happen
from disk.
create table test as select generate_series(1, 100000) id, repeat('x',
750) val;
create index sparse_idx on test (((id%100)=0));
explain (analyze, buffers) select * from test where ((id%100)=0) and val
!= '';
effective_io_concurrency=0 Execution time: 3258.220 ms
effective_io_concurrency=1 Execution time: 3345.689 ms
effective_io_concurrency=2 Execution time: 2516.558 ms
effective_io_concurrency=4 Execution time: 1816.150 ms
effective_io_concurrency=8 Execution time: 1083.018 ms
effective_io_concurrency=16 Execution time: 2349.064 ms
effective_io_concurrency=32 Execution time: 771.776 ms
effective_io_concurrency=64 Execution time: 1536.146 ms
effective_io_concurrency=128 Execution time: 560.471 ms
effective_io_concurrency=256 Execution time: 404.113 ms
effective_io_concurrency=512 Execution time: 318.271 ms
effective_io_concurrency=1000 Execution time: 411.978 ms
effective_io_concurrency=0 Execution time: 3655.124 ms
effective_io_concurrency=1 Execution time: 3337.614 ms
effective_io_concurrency=2 Execution time: 2914.609 ms
effective_io_concurrency=4 Execution time: 2133.285 ms
effective_io_concurrency=8 Execution time: 1326.740 ms
effective_io_concurrency=16 Execution time: 1765.848 ms
effective_io_concurrency=32 Execution time: 583.176 ms
effective_io_concurrency=64 Execution time: 541.667 ms
effective_io_concurrency=128 Execution time: 362.409 ms
effective_io_concurrency=256 Execution time: 446.026 ms
effective_io_concurrency=512 Execution time: 416.469 ms
effective_io_concurrency=1000 Execution time: 301.295 ms
effective_io_concurrency=0 Execution time: 4611.075 ms
effective_io_concurrency=1 Execution time: 3583.286 ms
effective_io_concurrency=2 Execution time: 2404.817 ms
effective_io_concurrency=4 Execution time: 1602.766 ms
effective_io_concurrency=8 Execution time: 1811.409 ms
effective_io_concurrency=16 Execution time: 1688.752 ms
effective_io_concurrency=32 Execution time: 613.454 ms
effective_io_concurrency=64 Execution time: 686.325 ms
effective_io_concurrency=128 Execution time: 425.590 ms
effective_io_concurrency=256 Execution time: 1394.318 ms
effective_io_concurrency=512 Execution time: 1579.458 ms
effective_io_concurrency=1000 Execution time: 414.184 ms
Regards,
Vitaliy
