Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Andres Freund <andres@anarazel.de>
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-23T00:29:53Z
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, Jul 22, 2025 at 5:11 PM Andres Freund <andres@anarazel.de> wrote:
> On 2025-07-18 23:25:38 -0400, Peter Geoghegan wrote:
> > > 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 :/

That makes sense.

Currently, the complex patch holds on to leaf page buffer pins until
btfreebatch is called for the relevant batch -- no matter what. This
is actually a short term workaround. I removed
_bt_drop_lock_and_maybe_pin from nbtree (the thing added by commit
2ed5b87f), without adding back an equivalent function that can work
across all index AMs. That shouldn't be hard.

Once I do that, then plain index scans with MVCC snapshots should
never actually have to hold on to buffer pins. I'm not sure if that
makes the underlying resource management problem any easier to address
-- but at least we won't *actually* hold on to any extra leaf page
buffer pins most of the time (once I make this fix).

> > 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?

That might be true, but if we're reading leaf pages then we're not
returning tuples to the scan -- even when, in principle, we could
return at least a few more right away. That's the kind of trade-off
I'm concerned about here.

-- 
Peter Geoghegan