Re: index prefetching

On Wed, Aug 13, 2025 at 5:19 PM Tomas Vondra <tomas(at)vondra(dot)me> wrote:
> It's also not very surprising this happens with backwards scans more.
> The I/O is apparently much slower (due to missing OS prefetch), so we're
> much more likely to hit the I/O limits (max_ios and various other limits
> in read_stream_start_pending_read).

But there's no OS prefetch with direct I/O. At most, there might be
some kind of readahead implemented in the SSD's firmware.

Even assuming that the SSD issue is relevant, I can't help but suspect
that something is off here. To recap from yesterday, the forwards scan
showed "I/O Timings: shared read=45.313" and "Execution Time: 330.379
ms" on my system, while the equivalent backwards scan showed "I/O
Timings: shared read=194.774" and "Execution Time: 1236.655 ms". Does
that kind of disparity *really* make sense with a modern NVME SSD such
as this (I use a Samsung 980 pro), in the context of a scan that can
use aggressive prefetching? Are we really, truly operating at the
limits of what is possible with this hardware, for this backwards
scan?

What if I use a ramdisk for this? That'll be much faster, no matter
the scan order. Should I expect this step to make the effect with
duplicates being produced by read_stream_look_ahead to just go away,
regardless of the scan direction in use?

--
Peter Geoghegan