Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>
Cc: Thomas Munro <thomas.munro@gmail.com>, Andres Freund <andres@anarazel.de>, Nazir Bilal Yavuz <byavuz81@gmail.com>, 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-28T23:15:11Z
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 28, 2025 at 7:01 PM Tomas Vondra <tomas@vondra.me> wrote:
> I'm not sure how to determine what concurrency it "wants". All I know is
> that for "warm" runs [1], the basic index prefetch patch uses distance
> ~2.0 on average, and is ~2x slower than master. And with the patches the
> distance is ~270, and it's 30% slower than master. (IIRC there's about
> 30% misses, so 270 is fairly high. Can't check now, the machine is
> running other tests.)

Is it possible that the increased distance only accidentally
ameliorates the IPC issues that you're seeing with method=worker? I
mentioned already that it makes things a bit slower with io_uring, for
the same test case. I mean, if you use io_uring then things work out
strictly worse with that extra patch...so something doesn't seem
right.

I notice that the test case in question manages to merge plenty of
reads together with other pending reads, within read_stream_look_ahead
(I added something to our working branch that'll show that information
in EXPLAIN ANALYZE). My wild guess is that an increased distance could
interact with that, somewhat masking the IPC problems with
method=worker.

Could that explain it? It seems possible that the distance is already
roughly optimal, without your patch (or Thomas' similar read stream
patch). It may be that we just aren't converging on "no prefetch"
behavior when we ought to, given such a low distance. If this theory
of mine was correct, it would reconcile the big differences we see
between "worker vs io_uring" with your patch + test case.

-- 
Peter Geoghegan