Horton

Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

View thread
From: Peter Geoghegan <pg@bowt.ie>
To: Tomas Vondra <tomas@vondra.me>, Andres Freund <andres@anarazel.de>
Cc: 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-14T00:20: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, Aug 13, 2025 at 7:51 PM Peter Geoghegan <pg@bowt.ie> wrote:
> Apparently random I/O is twice as fast as sequential I/O in descending order! In
> fact, this test case creates the appearance of random I/O being at least
> slightly faster than sequential I/O for pages read in _ascending_ order!
>
> Obviously something doesn't add up here.

Minor clarification: If EXPLAIN ANALYZE is to be believed, "I/O
Timings" is in fact higher with the randomized "t" table variant of
the test case, compared to what I showed yesterday with the original
sequential "t" version of the table, exactly as expected. (When I said
"Apparently random I/O is twice as fast as sequential I/O in
descending order!", I was just joking, of course.)

It seems reasonable to suppose that the actual problem has something
to do with synchronization overhead of some kind or other. Or, perhaps
it's due to some kind of major inefficiency in the patch -- perhaps
the patch can sometimes waste many CPU cycles on who-knows-what, at
least in cases like the original/slow backwards scan case.

-- 
Peter Geoghegan