Re: index prefetching

On Tue, Jul 22, 2025 at 8:08 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> My response was specific to Tomas' comment that for many queries, which tend
> to be more complicated than the toys we are using here, there will be CPU
> costs in the query.

Got it. That makes sense.

> cheaper query expensive query
> simple readahead 8723.209 ms 10615.232 ms
> complex readahead 5069.438 ms 8018.347 ms
>
> Obviously the CPU overhead in this example didn't completely eliminate the IO
> bottleneck, but sure reduced the difference.

That's a reasonable distinction, of course.

> If your assumption is that real queries are more CPU intensive that the toy
> stuff above, e.g. due to joins etc, you can see why the really attained IO
> depth is lower.

Right.

Perhaps I was just repeating myself. Tomas seemed to be suggesting
that cases where we'll actually get a decent and completely worthwhile
improvement with the complex patch would be naturally rare, due in
part to these effects with CPU overhead. I don't think that that's
true at all.

> Btw, something with the batching is off with the complex patch. I was
> wondering why I was not seing 100% CPU usage while also not seeing very deep
> queues - and I get deeper queues and better times with a lowered
> INDEX_SCAN_MAX_BATCHES and worse with a higher one.

I'm not at all surprised that there'd be bugs like that. I don't know
about Tomas, but I've given almost no thought to
INDEX_SCAN_MAX_BATCHES specifically just yet.

--
Peter Geoghegan