Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: 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>, Thomas Munro <thomas.munro@gmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-16T13:36:48Z
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 Wed, Jul 16, 2025 at 4:40 AM Tomas Vondra <tomas@vondra.me> wrote:
> But the thing I don't really understand it the "cyclic" dataset (for
> example). And the "simple" patch performs really badly here. This data
> set is designed to not work for prefetching, it's pretty much an
> adversary case. There's ~100 TIDs from 100 pages for each key value, and
> once you read the 100 pages you'll hit them many times for following
> values. Prefetching is pointless, and skipping duplicate blocks can't
> help, because the blocks are not effective.
>
> But how come the "complex" patch does so much better? It can't really
> benefit from prefetching TID from the next leaf - not this much. Yet it
> does a bit better than master. I'm looking at this since yesterday, and
> it makes no sense to me. Per "perf trace" it actually does 2x many
> fadvise calls compared to the "simple" patch (which is strange on it's
> own, I think), yet it's apparently so much faster?

The "simple" patch has _bt_readpage reset the read stream. That
doesn't make any sense to me. Though it does explain why the "complex"
patch does so many more fadvise calls.

Another issue with the "simple" patch: it adds 2 bool fields to
"BTScanPosItem". That increases its size considerably. We're very
sensitive to the size of this struct (I think that you know about this
already). Bloating it like this will blow up our memory usage, since
right now we allocate MaxTIDsPerBTreePage/1358 such structs for
so->currPos (and so->markPos). Wasting all that memory on alignment
padding is probably going to have consequences beyond memory bloat.

-- 
Peter Geoghegan