Re: effective_io_concurrency on EBS/gp2 - Mailing list pgsql-performance

Anyway, there are still some strange things happening when 
effective_io_concurrency is non-zero.

I've found that the real reason for the poor Bitmap Scan performance was 
related not only with sparsity of the rows/pages to be rechecked, but 
also with the value of starting ID from which the scan begins:

create table test as select generate_series(1, 100000) id, repeat('x', 
90) val;
alter table test add constraint test_pkey primary key (id);

select count(*) tup_per_page from test group by (ctid::text::point)[0] 
order by count(*) desc limit 5;
  tup_per_page
--------------
            65
            65
            65
            65
            65
(5 rows)

select * from test where id between X and 100000 and val != ''


effective_io_concurrency=0; id between 0 and 100000; Execution time: 
524.671 ms
effective_io_concurrency=1; id between 0 and 100000; Execution time: 
420.000 ms
effective_io_concurrency=0; id between 0 and 100000; Execution time: 
441.813 ms
effective_io_concurrency=1; id between 0 and 100000; Execution time: 
498.591 ms
effective_io_concurrency=0; id between 0 and 100000; Execution time: 
662.838 ms
effective_io_concurrency=1; id between 0 and 100000; Execution time: 
431.503 ms

effective_io_concurrency=0; id between 10 and 100000; Execution time: 
1210.436 ms
effective_io_concurrency=1; id between 10 and 100000; Execution time: 
1056.646 ms
effective_io_concurrency=0; id between 10 and 100000; Execution time: 
578.102 ms
effective_io_concurrency=1; id between 10 and 100000; Execution time: 
396.996 ms
effective_io_concurrency=0; id between 10 and 100000; Execution time: 
598.842 ms
effective_io_concurrency=1; id between 10 and 100000; Execution time: 
555.258 ms

effective_io_concurrency=0; id between 50 and 100000; Execution time: 
4017.999 ms
effective_io_concurrency=1; id between 50 and 100000; Execution time: 
383.694 ms
effective_io_concurrency=0; id between 50 and 100000; Execution time: 
535.686 ms
effective_io_concurrency=1; id between 50 and 100000; Execution time: 
570.221 ms
effective_io_concurrency=0; id between 50 and 100000; Execution time: 
852.960 ms
effective_io_concurrency=1; id between 50 and 100000; Execution time: 
656.097 ms

effective_io_concurrency=0; id between 64 and 100000; Execution time: 
385.628 ms
effective_io_concurrency=1; id between 64 and 100000; Execution time: 
712.261 ms
effective_io_concurrency=0; id between 64 and 100000; Execution time: 
1610.618 ms
effective_io_concurrency=1; id between 64 and 100000; Execution time: 
438.211 ms
effective_io_concurrency=0; id between 64 and 100000; Execution time: 
393.341 ms
effective_io_concurrency=1; id between 64 and 100000; Execution time: 
744.768 ms

effective_io_concurrency=0; id between 65 and 100000; Execution time: 
846.759 ms
effective_io_concurrency=1; id between 65 and 100000; Execution time: 
514.668 ms
effective_io_concurrency=0; id between 65 and 100000; Execution time: 
536.640 ms
effective_io_concurrency=1; id between 65 and 100000; Execution time: 
461.966 ms
effective_io_concurrency=0; id between 65 and 100000; Execution time: 
1810.677 ms
effective_io_concurrency=1; id between 65 and 100000; Execution time: 
545.359 ms

effective_io_concurrency=0; id between 66 and 100000; Execution time: 
663.920 ms
effective_io_concurrency=1; id between 66 and 100000; Execution time: 
5571.118 ms
effective_io_concurrency=0; id between 66 and 100000; Execution time: 
683.056 ms
effective_io_concurrency=1; id between 66 and 100000; Execution time: 
5883.359 ms
effective_io_concurrency=0; id between 66 and 100000; Execution time: 
472.809 ms
effective_io_concurrency=1; id between 66 and 100000; Execution time: 
5461.794 ms

effective_io_concurrency=0; id between 100 and 100000; Execution time: 
647.292 ms
effective_io_concurrency=1; id between 100 and 100000; Execution time: 
7810.344 ms
effective_io_concurrency=0; id between 100 and 100000; Execution time: 
773.750 ms
effective_io_concurrency=1; id between 100 and 100000; Execution time: 
5637.014 ms
effective_io_concurrency=0; id between 100 and 100000; Execution time: 
726.111 ms
effective_io_concurrency=1; id between 100 and 100000; Execution time: 
7740.607 ms

effective_io_concurrency=0; id between 200 and 100000; Execution time: 
549.281 ms
effective_io_concurrency=1; id between 200 and 100000; Execution time: 
5032.522 ms
effective_io_concurrency=0; id between 200 and 100000; Execution time: 
692.631 ms
effective_io_concurrency=1; id between 200 and 100000; Execution time: 
5138.669 ms
effective_io_concurrency=0; id between 200 and 100000; Execution time: 
793.342 ms
effective_io_concurrency=1; id between 200 and 100000; Execution time: 
5375.822 ms

effective_io_concurrency=0; id between 1000 and 100000; Execution time: 
596.754 ms
effective_io_concurrency=1; id between 1000 and 100000; Execution time: 
5278.683 ms
effective_io_concurrency=0; id between 1000 and 100000; Execution time: 
638.706 ms
effective_io_concurrency=1; id between 1000 and 100000; Execution time: 
5404.002 ms
effective_io_concurrency=0; id between 1000 and 100000; Execution time: 
730.667 ms
effective_io_concurrency=1; id between 1000 and 100000; Execution time: 
5761.312 ms

effective_io_concurrency=0; id between 2000 and 100000; Execution time: 
656.086 ms
effective_io_concurrency=1; id between 2000 and 100000; Execution time: 
6156.003 ms
effective_io_concurrency=0; id between 2000 and 100000; Execution time: 
768.288 ms
effective_io_concurrency=1; id between 2000 and 100000; Execution time: 
4917.423 ms
effective_io_concurrency=0; id between 2000 and 100000; Execution time: 
500.931 ms
effective_io_concurrency=1; id between 2000 and 100000; Execution time: 
5659.255 ms

effective_io_concurrency=0; id between 5000 and 100000; Execution time: 
755.440 ms
effective_io_concurrency=1; id between 5000 and 100000; Execution time: 
5141.671 ms
effective_io_concurrency=0; id between 5000 and 100000; Execution time: 
542.174 ms
effective_io_concurrency=1; id between 5000 and 100000; Execution time: 
6074.953 ms
effective_io_concurrency=0; id between 5000 and 100000; Execution time: 
570.615 ms
effective_io_concurrency=1; id between 5000 and 100000; Execution time: 
6922.402 ms

effective_io_concurrency=0; id between 10000 and 100000; Execution time: 
469.544 ms
effective_io_concurrency=1; id between 10000 and 100000; Execution time: 
6083.361 ms
effective_io_concurrency=0; id between 10000 and 100000; Execution time: 
706.078 ms
effective_io_concurrency=1; id between 10000 and 100000; Execution time: 
4069.171 ms
effective_io_concurrency=0; id between 10000 and 100000; Execution time: 
526.792 ms
effective_io_concurrency=1; id between 10000 and 100000; Execution time: 
5289.984 ms

effective_io_concurrency=0; id between 20000 and 100000; Execution time: 
435.503 ms
effective_io_concurrency=1; id between 20000 and 100000; Execution time: 
5460.730 ms
effective_io_concurrency=0; id between 20000 and 100000; Execution time: 
454.323 ms
effective_io_concurrency=1; id between 20000 and 100000; Execution time: 
4163.030 ms
effective_io_concurrency=0; id between 20000 and 100000; Execution time: 
674.382 ms
effective_io_concurrency=1; id between 20000 and 100000; Execution time: 
3703.045 ms

effective_io_concurrency=0; id between 50000 and 100000; Execution time: 
226.094 ms
effective_io_concurrency=1; id between 50000 and 100000; Execution time: 
2584.720 ms
effective_io_concurrency=0; id between 50000 and 100000; Execution time: 
1431.037 ms
effective_io_concurrency=1; id between 50000 and 100000; Execution time: 
2651.834 ms
effective_io_concurrency=0; id between 50000 and 100000; Execution time: 
345.194 ms
effective_io_concurrency=1; id between 50000 and 100000; Execution time: 
2328.844 ms

effective_io_concurrency=0; id between 75000 and 100000; Execution time: 
120.121 ms
effective_io_concurrency=1; id between 75000 and 100000; Execution time: 
2125.927 ms
effective_io_concurrency=0; id between 75000 and 100000; Execution time: 
115.865 ms
effective_io_concurrency=1; id between 75000 and 100000; Execution time: 
1616.534 ms
effective_io_concurrency=0; id between 75000 and 100000; Execution time: 
138.005 ms
effective_io_concurrency=1; id between 75000 and 100000; Execution time: 
1651.880 ms

effective_io_concurrency=0; id between 90000 and 100000; Execution time: 
66.322 ms
effective_io_concurrency=1; id between 90000 and 100000; Execution time: 
443.317 ms
effective_io_concurrency=0; id between 90000 and 100000; Execution time: 
53.138 ms
effective_io_concurrency=1; id between 90000 and 100000; Execution time: 
566.945 ms
effective_io_concurrency=0; id between 90000 and 100000; Execution time: 
57.441 ms
effective_io_concurrency=1; id between 90000 and 100000; Execution time: 
525.749 ms

For some reason, with dense bitmap scans, when Bitmap Heap Scan / 
Recheck starts not from the first page of the table, the 
effective_io_concurrency=0 consistently and significantly outperforms 
effective_io_concurrency=1.

Regards,
Vitaliy