Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Thomas Munro <thomas.munro@gmail.com>
Cc: Tomas Vondra <tomas@vondra.me>, Nazir Bilal Yavuz <byavuz81@gmail.com>, Andres Freund <andres@anarazel.de>, 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-06T20:41:00Z
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.

On Tue, Aug 5, 2025 at 7:31 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> There must be a similar opportunity for parallel index scans.  It has
> that "seize the scan" concept where parallel workers do one-at-a-time
> locked linked list leapfrog.

True. More generally, flexibility to reorder work would be useful there.

The structure of parallel B-tree scans is one where each worker
performs its own "independent" index scan. The workers each only
return tuples from those leaf pages that they themselves manage to
read. That isn't particularly efficient, since we'll usually have to
merge the "independent" index scan tuples together once again using a
GatherMerge.

In principle, we could avoid a GatherMerge by keeping track of the
logical order of leaf pages at some higher level, and outputting
tuples in that same order -- which isn't a million miles from what the
batch interface that Tomas wrote already does. Imagine an enhanced
version of that design where the current read_stream callback wholly
farms out the work of reading leaf pages to parallel workers. Once we
decouple the index page reading from the heap access, we might be able
to invent the idea of "task specialization", where some workers more
or less exclusively read leaf pages, and other workers more or less
exclusively perform related heap accesses.

> Basically, the stuff that we can't fix with "precise" I/O
> streaming as I like to call it, where it might still be interesting to
> think about opportunities to do fuzzier speculative lookahead.  I'll
> start a new thread.

That sounds interesting. I worry that we won't ever be able to get
away without some fallback that behaves roughly like OS readahead.

-- 
Peter Geoghegan