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

On Tue, Oct 23, 2012 at 10:54 AM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
>> Indeed not. I also looked into prefetching for pure index scans for
>> b-trees (and extension to use async io).
>> http://archives.postgresql.org/message-id/BLU0-SMTP31709961D846CCF4F5EB4C2A3930%40phx.gbl
>
> Yes, I've seen that, though I thought it was only an improvement on
> PrefetchBuffer. That patch would interact quite nicely with mine.
>
> I'm now trying to prefetch heap tuples, and I got to a really nice
> place where I get an extra 30% speedup even on forward scans, but the
> patch is rather green now for a review.
>
>> I am not where I have a proper setup this week but will reply at greater
>> length next week.
>
> Great - will go on improving the patch in the meanwhile ;-)

Ok, this is the best I could come up with, without some real test hardware.

The only improvement I see in single-disk scenarios:
* Huge speedup of back-sequential index-only scans
* Marginal speedup on forward index-only scans (5% or less)
* No discernible difference in heap-including scans (even with heap
prefetch), but I'm pretty sure a real RAID setup would change this
* No change in pgbench (so I guess no regression for small transactions)

If I manage to get my hands on test hardware, I'll post results. But
we just had to bring some machines offline in our testing datacenter,
which effectively shrank my options, rather than expanding them. I
don't see that improving soon, so I'll post the patch and hope someone
else tests.

PS: should I add it to the commit fest? should we compare notes with
John Limby's patch first?