Re: index prefetching

On Thu, Aug 14, 2025 at 2:53 PM Andres Freund <andres@anarazel.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.

>   I'd see what changes if you temporarily reduce
>   /sys/block/nvme6n1/queue/max_sectors_kb to a smaller size.

I reduced max_sectors_kb from 128 to 8. That had no significant effect.

> Could you show iostat for both cases?

iostat has lots of options. Can you be more specific?

--
Peter Geoghegan