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

On Mon, Oct 29, 2012 at 7:07 PM, Cédric Villemain
<cedric@2ndquadrant.com> wrote:
>> 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.
>
> Well, informing linux hackers may help.

I agree. I'm a bit hesitant to subscribe to yet another mailing list,
but I happen to agree.

>> > 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.
>
> I though of the opposite way: how do other kernels handle the backwards
> prefetch.

From what I saw (while reasearching that statement above), BSD's
read-ahead and fadvise implementations are way behind linux's.
Functional, though. I haven't been able to find the code responsible
for readahead in FreeBSD yet to confirm whether they have anything
supporting back-sequential patterns.

>> > 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.
>
> We're speaking of PostgreSQL 9.3, running cutting edge PostgreSQL and old
> kernel in end 2013... Maybe it won't be so latest-in-the-future at this time.

Good point.