Re: index prefetching

Peter Geoghegan <pg@bowt.ie>

From: Peter Geoghegan <pg@bowt.ie>

To: Andres Freund <andres@anarazel.de>

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

Date: 2025-09-04T00:28:24Z

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, Sep 3, 2025 at 8:16 PM Andres Freund <andres@anarazel.de> wrote:
> > I don't see that level of improvement with DIO. For me it's 6054.921
> > ms with prefetching, 8766.287 ms without it.
>
> I guess your SSD has lower latency than mine...

It's nothing special: a 4 year old Samsung 980 pro.

> This actually might be the thing to tackle to avoid this and other similar
> regressions: If we were able to isssue combined IOs for interspersed patterns
> like we have in this query, we'd easily win back the overhead. And it'd make
> DIO much much better.

That sounds very plausible to me. I don't think it's at all unusual
for index scans to do this (that particular aspect of the test case
query wasn't unrealistic). In general this seems important to me.

> I don't quite know if this is best done as an optional feature for read
> streams, a layer atop read stream or something dedicated.

My guess is that it would work best as an optional feature for read
streams. A flag like READ_STREAM_REPEAT_READS that's passed to
read_stream_begin_relation might work best.

> For now I'll go back to working on read stream test infrastructure. That's the
> prerequisite for testing the "don't synchronously wait for in-progress IO"
> improvement.

"don't synchronously wait for in-progress IO" is also very important
to this project. Thanks for your help with that.

> And if we want to have more complicated merging, that also seems
> like something much easier to develop with some testing infra.

Great.

-- 
Peter Geoghegan