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>, Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, Thomas Munro <thomas.munro@gmail.com>, Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Date: 2025-07-16T21:01:16Z
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, Jul 16, 2025 at 4:46 PM Andres Freund <andres@anarazel.de> wrote:
> Currently the API wouldn't easily allow the table AM to do batched TID lookups
> - if you have a query that looks at a lot of table tuples in the same buffer
> consecutively, we spend a lot of time locking/unlocking said buffer.  We also
> spend a lot of time dispatching from nodeIndexscan.c to tableam in such
> queries.
>
> I'm not suggesting to increase the scope to handle that, but it might be worth
> keeping in mind.
>
> I think the potential gains here are really substantial.

I agree. I've actually discussed this possibility with Tomas a few
times, though not recently. It's really common for TIDs that appear on
a leaf page to be slightly out of order due to minor heap
fragmentation. Even minor fragmentation can significantly increase
pin/buffer lock traffic right now.

I think that it makes a lot of sense for the general design to open up
possibilities such as this.

> I see some slowdown for well-cached queries with the patch, I've not dug into
> why.

I saw less than a 5% regression in pgbench SELECT with the "complex"
patch with 32 clients. My guess is that it's due to the less efficient
memory allocation with batching. Obviously this isn't acceptable, but
I'm not particularly concerned about it right now. I was actually
pleased to see that there wasn't a much larger regression there.

-- 
Peter Geoghegan