Re: index prefetching

Hi,

On 2025-08-14 15:15:02 -0400, Peter Geoghegan wrote:
> On Thu, Aug 14, 2025 at 2:53 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> > I think this is just an indicator of being IO bound.
>
> Then why does the exact same pair of runs show "I/O Timings: shared
> read=194.629" for the sequential table backwards scan (with total
> execution time 1132.360 ms), versus "I/O Timings: shared read=352.88"
> (with total execution time 697.681 ms) for the random table backwards
> scan?
>
> Obviously it is hard to believe that the query with shared
> read=194.629 is one that is naturally much more I/O bound than another
> similar query that shows shared read=352.88. What "I/O Timings" shows
> more or less makes sense to me already -- it just doesn't begin to
> explain why *overall query execution* is much slower when scanning
> backwards sequentially.

Hm, that is somewhat curious.

I wonder if there's some wait time that's not being captured by "I/O
Timings". A first thing to do would be to just run strace --summary-only while
running the query, and see if there are syscall wait times that seem too long.

What effective_io_concurrency and io_max_concurrency setting are you using? If
there are no free IO handles that's currently not nicely reported (because
it's unclear how exactly to do so, see comment above pgaio_io_acquire_nb()).

> > Could you show iostat for both cases?
>
> iostat has lots of options. Can you be more specific?

iostat -xmy /path/to/block/device

I'd like to see the difference in average IO size (rareq-sz), queue depth
(aqu-sz) and completion time (r_await) between the fast and slow cases.

Greetings,

Andres Freund