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-11T20:49:22Z
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 Thu, Aug 7, 2025 at 1:25 AM Thomas Munro <thomas.munro@gmail.com> wrote:
>  * you could make a stream that pulls leaf pages from higher level
> internal pages on demand (if you want to avoid the flow control
> problems that come from trying to choose a batch size up front before
> you know you'll even need it by using a pull approach), or just notice
> that it looks sequential and install a block range producer, and if
> that doesn't match the next page pointers by the time you get there
> then you destroy it and switch strategies, or something

I was hoping that we wouldn't ever have to teach index scans to
prefetch leaf pages like this. It is pretty complicated, primarily
because it completely breaks with the idea of the scan having to
access pages in some fixed order. (Whereas if we're just prefetching
heap pages, then there is a fixed order, which makes maintaining
prefetch distance relatively straightforward and index AM neutral.)

It's also awkward to make such a scheme work, especially when there's
any uncertainty about how many leaf pages will ultimately be read/how
much work to do speculatively. There might not be that many relevant
leaf pages (level 0 pages) whose block numbers are conveniently
available as prefetchable downlinks/block numbers to the right of the
downlink we use to descend to the first leaf page to be read (our
initial downlink might be positioned towards the end of the relevant
internal page at level 1). I guess we could re-read the internal page
only when prefetching later leaf pages starts to look like a good
idea, but that's another complicated code path to maintain.

-- 
Peter Geoghegan