Re: index prefetching

On Thu, Aug 14, 2025 at 6:24 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> FWIW I'm not claiming this explains all odd things we're investigating
> in this thread, it's more a confirmation that the scan direction may
> matter if it translates to direction at the device level. I don't think
> it can explain the strange stuff with the "random" data sets constructed
> Peter.

The weird performance characteristics of that one backwards scan are
now believed to be due to the WaitIO issue that Andres described about
an hour ago. That issue seems unlikely to only affect backwards
scans/reverse-sequential heap I/O.

I accept that backwards scans are likely to be significantly slower
than forwards scans on most/all SSDs. But that in itself doesn't
explain why the same issue didn't cause the equivalent sequential
forward scan to also be a lot slower. Actually, it probably *did*
cause that forwards scan to be *somewhat* slower -- just not by enough
to immediately jump out at me (not enough to make the forwards scan
much slower than a scan that does wholly random I/O, which is
obviously absurd).

My guess is that once we fix the underlying problem, we'll see
improved performance for many different types of queries. Not as big
of a benefit as the one that the broken query will get, but still
enough to matter.

--
Peter Geoghegan