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

On Thu, Oct 18, 2012 at 7:42 PM, Claudio Freire <klaussfreire@gmail.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.