Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@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-07-25T00:44:05Z
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, Jul 24, 2025 at 7:52 PM Tomas Vondra <tomas@vondra.me> wrote:
> Yeah, I forgot about that. Should be fixed in the v2. Admittedly I don't
> know that much about nbtree internals, so this is mostly copy pasting
> from verify_nbtree.

As long as the scan only moves to the right (never the left), and as
long as you don't forget about P_IGNORE() pages, everything should be
fairly straightforward. You don't really need to understand things
like page deletion, and you'll never need to hold more than a single
buffer lock at a time, provided you stick to the happy path.

I've taken a quick look at v2, and it looks fine to me. It's
acceptable for the purpose that you have in mind, at least.

> Yeah, probably. And we'll probably test on such uniform data sets, or at
> least we we'll start with those. But at some point I'd like to test with
> some of these "weird" indexes too, if only to test how well the prefetch
> heuristics adjusts the distance.

That makes perfect sense. I was just providing context.

> I have a very good reason why I didn't do it that way. I was lazy. But
> v2 should be doing that, I think.

I respect that. That's why I framed my feedback as "it'll be less
effort to just do it than to explain why you haven't done so".  :-)

> Yeah, this interface seems useful. I suppose it'll be handy when looking
> at an index scan, to get stats from the currently loaded batches. In
> principle you get that from v3 by filtering, but it might be slow on
> large indexes. I'll try doing that in v3.

Cool.

-- 
Peter Geoghegan