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
<<attachment: sparse.zip>>
