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

On Mon, Oct 29, 2012 at 4:17 PM, Cédric Villemain
<cedric(at)2ndquadrant(dot)com> wrote:
>> 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 the gain is visible mostly for the backward and not for other access
> patterns I suggest to check the work done in backward-prefecthing in linux.
>
> http://thread.gmane.org/gmane.linux.kernel.mm/73837 for example

That patch seems very similar (but in kernel terms) to this patch, so
I imagine it would also do the job.

But it also looks forgotten. Bringing it back to life would mean
building the latest kernel with that patch included, replicating the
benchmarks I ran here, sans pg patch, but with patched kernel, and
reporting the (hopefully equally dramatic) performance improvements in
the kernel ML. That would take me quite some time (not used to playing
with kernels, though it wouldn't be my first time either), though it
might be worth the effort.

> I don't know how others (BSD, windows, ...) handle this case.

I don't even think windows supports posix_fadvise, but if async_io is
used (as hinted by the link Lumby posted), it would probably also work
in windows.

BSD probably supports it the same way linux does.

> Maybe the strategy to use our own prefetch is better, then I would like to use
> it also in places where we used to hack to make linux understand that we will
> benefits from prefetching.

It would at least benefit those installations without the
latest-in-the-future kernel-with-backwards-readahead.

To which places are you referring to, btw?