Re: index prefetching

On 8/15/25 01:05, Peter Geoghegan wrote:
> 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.
>

Good. I admit I lost track of which the various regressions may affect
existing plans, and which are specific to the prefetch patch.

> 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).
>

True. That's weird.

> 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.
>

Hopefully. Let's see.

regards

--
Tomas Vondra