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>, 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-03T20:25:56Z
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, Sep 3, 2025 at 4:06 PM Andres Freund <andres@anarazel.de> wrote:
> The issue to me is that this kind of query actually *can* substantially
> benefit from prefetching, no?

As far as I can tell, not really, no.

> Afaict the performance without prefetching is
> rather atrocious as soon as a) storage has a tad higher latency or b) DIO is
> used.

I don't know that storage latency matters, when (without DIO) we're
doing so well from readahead.

> Indeed: With DIO, readahead provides a ~2.6x improvement for the query at hand.

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 can kind of accept the idea that in some sense readahead shouldn't
count too much, since the future is DIO. But it's not like aggressive
prefetching matches the performance of buffered I/O + readahead. Not
for me, at any rate. I don't know why.

> I continue to be worried that we're optimizing for queries that have no
> real-world relevance.

I'm not at all surprised that we're spending so much time on weird
queries. For one thing, the real world queries are already much
improved. For another, in order to accept a trade-off like this, we
have to actually know what it is we're accepting. And how easy/hard it
is to do better (we may very well be able to fix this problem at no
great cost in complexity).

> This just doesn't strike me as a particularly realistic combination of
> factors?

I agree. I just don't think that we've done enough work on this to
justify accepting it as a cost of doing business. We might well do
that at some point in the near future.

> I suspect we could more than eat back the loss in performance by doing batched
> heap_hot_search_buffer()...

Maybe, but I don't think that we're all that likely to get that done for 19.

-- 
Peter Geoghegan