Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

From: Peter Geoghegan <pg@bowt.ie>

To: Andres Freund <andres@anarazel.de>

Cc: Konstantin Knizhnik <knizhnik@garret.ru>, Tomas Vondra <tomas@vondra.me>, 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>, Dilip Kumar <dilipbalaut@gmail.com>

Date: 2025-12-17T19:53: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 →

aio: io_uring: Trigger async processing for large IOs
- a9ee66881744 19 (unreleased) landed
read stream: Split decision about look ahead for AIO and combining
- 8ca147d582a5 19 (unreleased) landed
read_stream: Only increase read-ahead distance when waiting for IO
- f63ca3379025 19 (unreleased) landed
read_stream: Prevent distance from decaying too quickly
- 6e36930f9aaf 19 (unreleased) landed
Reduce ExecSeqScan* code size using pg_assume()
- b227b0bb4e03 19 (unreleased) cited
Fix rare bug in read_stream.c's split IO handling.
- b421223172a2 19 (unreleased) cited
Fix multiranges to behave more like dependent types.
- 3e8235ba4f9c 17.0 cited
Add EXPLAIN (MEMORY) to report planner memory consumption
- 5de890e3610d 17.0 cited
Optimize nbtree backward scan boundary cases.
- c9c0589fda0e 17.0 cited
Increment xactCompletionCount during subtransaction abort.
- 90c885cdab8b 14.0 cited
Add nbtree Valgrind buffer lock checks.
- 4a70f829d86c 14.0 cited
Add nbtree high key "continuescan" optimization.
- 29b64d1de7c7 12.0 cited
Reduce pinning and buffer content locking for btree scans.
- 2ed5b87f96d4 9.5.0 cited
Teach btree to handle ScalarArrayOpExpr quals natively.
- 9e8da0f75731 9.2.0 cited

On Wed, Dec 17, 2025 at 2:30 PM Andres Freund <andres@anarazel.de> wrote:
> Note that due to the tuple size and fillfactor in Konstantin's workload, there
> will be one tuple per page... That should allow for some prefetching.

I don't see how, unless he also set leaf fillfactor very low (though
probably not even then, with LIMIT 1, since you still get a few index
tuples on each leaf page).

As Tomas points out, this particularly heuristic probably has some
problems. I'm not claiming that this is the ideal behavior. Just that
it would seem to make it almost impossible for prefetching to ever
show benefits, with a workload such as this (in particular with LIMIT
1 it seems quite impossible).

-- 
Peter Geoghegan