Horton

Re: index prefetching

Thomas Munro <thomas.munro@gmail.com>

View thread
From: Thomas Munro <thomas.munro@gmail.com>
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Peter Geoghegan <pg@bowt.ie>, 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-28T21:50:57Z
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 Fri, Aug 29, 2025 at 7:52 AM Andres Freund <andres@anarazel.de> wrote:
> On 2025-08-28 19:08:40 +0200, Tomas Vondra wrote:
> > From the 2x regression (compared to master) it might seem like that, but
> > even with the increased distance it's still slower than master (by 25%). So
> > maybe the "error" is to use AIO in these cases, instead of just switching to
> > I/O done by the backend.
>
> If it's slower at a higher distance, we're missing something.

Enough io_workers?  What kind of I/O concurrency does it want?  Does
wait_event show any backends doing synchronous IO?  How many does [1]
want to run for that test workload and does it help?

FWIW there's a very simple canned latency test in a SQL function in
the first message in that thread (0005-XXX-read_buffer_loop.patch),
just on the off-chance that it's useful as a starting point for other
ideas.  There I was interested in IPC overheads, latch collapsing and
other effects, so I was deliberately stalling on/evicting a single
block repeatedly without any readahead distance, so I wasn't letting
the stream "hide" IPC overheads.

[1] https://www.postgresql.org/message-id/flat/CA%2BhUKG%2Bm4xV0LMoH2c%3DoRAdEXuCnh%2BtGBTWa7uFeFMGgTLAw%2BQ%40mail.gmail.com