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>, Nazir Bilal Yavuz <byavuz81@gmail.com>, Thomas Munro <thomas.munro@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-07-23T00:39:35Z
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 8:08 PM Andres Freund <andres@anarazel.de> wrote:
> My response was specific to Tomas' comment that for many queries, which tend
> to be more complicated than the toys we are using here, there will be CPU
> costs in the query.

Got it. That makes sense.

>                     cheaper query       expensive query
> simple readahead    8723.209 ms         10615.232 ms
> complex readahead   5069.438 ms          8018.347 ms
>
> Obviously the CPU overhead in this example didn't completely eliminate the IO
> bottleneck, but sure reduced the difference.

That's a reasonable distinction, of course.

> If your assumption is that real queries are more CPU intensive that the toy
> stuff above, e.g. due to joins etc, you can see why the really attained IO
> depth is lower.

Right.

Perhaps I was just repeating myself. Tomas seemed to be suggesting
that cases where we'll actually get a decent and completely worthwhile
improvement with the complex patch would be naturally rare, due in
part to these effects with CPU overhead. I don't think that that's
true at all.

> Btw, something with the batching is off with the complex patch.  I was
> wondering why I was not seing 100% CPU usage while also not seeing very deep
> queues - and I get deeper queues and better times with a lowered
> INDEX_SCAN_MAX_BATCHES and worse with a higher one.

I'm not at all surprised that there'd be bugs like that. I don't know
about Tomas, but I've given almost no thought to
INDEX_SCAN_MAX_BATCHES specifically just yet.

-- 
Peter Geoghegan