Horton

Re: index prefetching

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Thomas Munro <thomas.munro@gmail.com>, Tomas Vondra <tomas@vondra.me>, Nazir Bilal Yavuz <byavuz81@gmail.com>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-08-12T22:42:06Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. aio: io_uring: Trigger async processing for large IOs

  2. read stream: Split decision about look ahead for AIO and combining

  3. read_stream: Only increase read-ahead distance when waiting for IO

  4. read_stream: Prevent distance from decaying too quickly

  5. Reduce ExecSeqScan* code size using pg_assume()

  6. Fix rare bug in read_stream.c's split IO handling.

  7. Fix multiranges to behave more like dependent types.

  8. Add EXPLAIN (MEMORY) to report planner memory consumption

  9. Optimize nbtree backward scan boundary cases.

  10. Increment xactCompletionCount during subtransaction abort.

  11. Add nbtree Valgrind buffer lock checks.

  12. Add nbtree high key "continuescan" optimization.

  13. Reduce pinning and buffer content locking for btree scans.

  14. Teach btree to handle ScalarArrayOpExpr quals natively.

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