Re: [PATCH] Prefetch index pages for B-Tree index scans

On Thu, Oct 18, 2012 at 7:42 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> Fun. That didn't take long.
>
> With the attached anti-sequential scan patch, and effective_io_concurrency=8:
>
>
> QUERY PLAN
> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> GroupAggregate (cost=0.00..4149039.04 rows=90257289 width=4) (actual
> time=26.964..84299.789 rows=90000001 loops=1)
> -> Index Only Scan Backward using pgbench_accounts_pkey on
> pgbench_accounts (cost=0.00..2795179.71 rows=90257289 width=4)
> (actual time=26.955..62761.774 rows=90000001 loops=1)
> Index Cond: ((aid >= 10000000) AND (aid <= 200000000))
> Heap Fetches: 0
> Total runtime: 87170.355 ms
> I/O thoughput 22MB/s (twice as fast)
> I/O utilization 95% (I was expecting 100% but... hey... good enough)
>
> With e_i_c=24, it gets to 100% utilization and 30MB/s (that's 3 times
> faster). So, I'd like to know what you think, but maybe for
> back-sequential scans, prefetch should be set to a multiple (ie: x24)
> of e_i_c, in order to exploit read request merges.

Earlier patch had a regression for heap-including scans backwards with
RAID, so I made the back-sequential optimization index-only-only and
now I can find no regression. Make check runs fine, btw.

I hope I'm not talking to myself.