Re: index prefetching

Hi,

On 2025-08-12 17:22:20 -0400, Peter Geoghegan wrote:
> Doesn't look like Linux will do this, if what my local testing shows is anything
> to go on.

Yes, matches my experiments outside of postgres too.

> I'm a bit surprised by this (I also thought that OS readahead on linux
> was quite sophisticated).

It's mildly sophisticated in detecting various *forward scan* patterns. There
just isn't anything for backward scans - presumably because there's not
actually much that generates backward reads of files...

> The premise of my original complaint was that big inconsistencies in performance
> shouldn't happen between similar forwards and backwards scans (at least not with
> direct I/O). I now have serious doubts about that premise, since it looks like
> OS readahead remains a big factor with direct I/O. Did I just miss something
> obvious?

There is absolutely no OS level readahead with direct IO (there can be
*merging* of neighboring IOs though, if they're submitted close enough
together).

However that doesn't mean that your storage hardware can't have its own set of
heuristics for faster access - afaict several NVMes I have access to have
shorter IO times for forward scans than for backward scans.

Besides actual IO times, there also is the issue that the page level access
might be differently efficient, the order in which tuples are accessed also
plays a role in how efficient memory level prefetching is.

OS level readahead is visible in some form in iostat - you get bigger reads or
multiple in-flight IOs.

Greetings,

Andres Freund