Re: index prefetching

Hi,

On 2025-08-29 01:00:58 +0200, Tomas Vondra wrote:
> I'm not sure how to determine what concurrency it "wants". All I know is
> that for "warm" runs [1], the basic index prefetch patch uses distance
> ~2.0 on average, and is ~2x slower than master. And with the patches the
> distance is ~270, and it's 30% slower than master. (IIRC there's about
> 30% misses, so 270 is fairly high. Can't check now, the machine is
> running other tests.)

There got to be something wrong here, I don't see a reason why at any
meaningful distance it'd be slower.

What set of patches do I need to repro the issue?

And what are the complete set of pieces to load the data?
https://postgr.es/m/293a4735-79a4-499c-9a36-870ee9286281%40vondra.me
has the query, but afaict not enough information to infer init.sql

> Not sure about wait events, but I don't think any backends are doing
> sychnronous I/O. There's only that one query running, and it's using AIO
> (except for the index, which is still read synchronously).
>
> Likewise, I don't think there's insufficient number of workers. I've
> tried with 3 and 12 workers, and there's virtually no difference between
> those. IIRC when watching "top", I've never seen more than 1 or maybe 2
> workers active (using CPU).

That doesn't say much - if the they are doing IO, they're not on CPU...

Greetings,

Andres Freund