Re: index prefetching

On Wed, Sep 3, 2025 at 4:06 PM Andres Freund <andres(at)anarazel(dot)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