Horton

Re: index prefetching

Andres Freund <andres@anarazel.de>

View thread
From: Andres Freund <andres@anarazel.de>
To: Peter Geoghegan <pg@bowt.ie>
Cc: Tomas Vondra <tomas@vondra.me>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Thomas Munro <thomas.munro@gmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-22T21:11:04Z
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-07-18 23:25:38 -0400, Peter Geoghegan wrote:
> On Fri, Jul 18, 2025 at 10:47 PM Andres Freund <andres@anarazel.de> wrote:
> > > (Within an index AM, there is a 1:1 correspondence between batches and leaf
> > > pages, and batches need to hold on to a leaf page buffer pin for a
> > > time. None of this should really matter to the table AM.)
> >
> > To some degree the table AM will need to care about the index level batching -
> > we have to be careful about how many pages we keep pinned overall. Which is
> > something that both the table and the index AM have some influence over.
> 
> Can't they operate independently?

I'm somewhat doubtful. Read stream is careful to limit how many things it
pins, lest we get errors about having too many buffers pinned. Somehow the
number of pins held within the index needs to be limited too, and how much
that needs to be limited depends on how many buffers are pinned in the read
stream :/


> > > At a high level, the table AM (and/or its read stream) asks for so
> > > many heap blocks/TIDs. Occasionally, index AM implementation details
> > > (i.e. the fact that many index leaf pages have to be read to get very
> > > few TIDs) will result in that request not being honored. The interface
> > > that the table AM uses must therefore occasionally answer "I'm sorry,
> > > I can only reasonably give you so many TIDs at this time". When that
> > > happens, the table AM has to make do. That can be very temporary, or
> > > it can happen again and again, depending on implementation details
> > > known only to the index AM side (though typically it'll never happen
> > > even once).
> >
> > I think that requirement will make things more complicated. Why do we need to
> > have it?
> 
> What if it turns out that there is a large run of contiguous leaf
> pages that contain no more than 2 or 3 matching index tuples?

I think that's actually likely a case where you want *deeper* prefetching, as
it makes it more likely that the table tuples are on different pages, i.e. you
need a lot more in-flight IOs to avoid stalling on IO.


> What if there's no matches across many leaf pages?

We don't need to keep leaf nodes without matches pinned in that case, so I
don't think there's really an issue?

Greetings,

Andres Freund