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>, Thomas Munro <thomas.munro@gmail.com>, 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-14T01:51:43Z
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, Aug 13, 2025 at 8:59 PM Tomas Vondra <tomas@vondra.me> wrote:
> I investigated this from a different angle, by tracing the I/O request
> generated. using perf-trace. And the patterns are massively different.

I tried a similar approach myself, using a variety of tools. That
didn't get me very far.

> So, Q1 ASC gets to combine the I/O into nice large chunks. But the DESC
> queries end up doing a stream of 8K requests. The Q2 ASC gets to do 16KB
> reads in about half the cases, but the rest is still 8KB.

My randomized version of the forwards scan is about as fast (maybe
even slightly faster) than your original version on my workstation, in
spite of the fact that EXPLAIN ANALYZE reports that the randomized
version does indeed have about a 3x higher "I/O Timings: shared read".
So I tend to doubt that low-level instrumentation will be all that
helpful with debugging the issue.

I suppose that it *might* be helpful if you can use it to spot some
kind of pattern -- a pattern that hints at the real underlying issue.
To me the issue feels like a priority inversion problem. Maybe
slow-ish I/O can lead to very very slow query execution time, due to
some kind of second order effect (possibly an issue on the read stream
side). If that's what this is then the problem still won't be that
there was slow-ish I/O, or that we couldn't successfully combine I/Os
in whatever way. After all, we surely won't be able to combine I/Os
with the randomized version of the queries that I described to the
list this evening -- and yet those are still very fast in terms of
overall execution time (somehow, they are about as fast as the
original variant, that will manage to combine I/Os, in spite of the
obvious disadvantage of requiring random I/O for the heap accesses).

-- 
Peter Geoghegan