Re: index prefetching

On Mon, Sep 14, 2026 at 2:08 PM Peter Geoghegan <pg(at)bowt(dot)ie> wrote:
> The remaining problems all relate to prefetching itself,
> particularly issues with the read stream's heuristics that certain
> index scans run into. I have a WIP patch that fixes some of these
> problems by deduplicating nearby read stream block requests, but that
> isn't quite ready to post yet.

In case it helps when testing that patch, here is a small,
deterministic case of that kind, measured with v36 on a device with
added read latency. The data has the shape of Tomas's low "fuzz" data
sets [1]; what is new here is the latency and the counters of each
run.

Data: 8M rows, 163,266 heap pages. Two indexes over the same 400k
rows and the same heap range:

t_id: on id, no jitter
t_b: on b = id plus a deterministic jitter of +/-50 rows

pg_stats reports correlation 1.0000 for both columns. The callback
skips a block only when it equals the previous one
(heapam_indexscan.c, v36-0006), so the block numbers each index hands
to the read stream, computed from the data, are:

t_id: 8,164 distinct blocks, 8,164 block numbers
t_b: 8,166 distinct blocks, 136,837 block numbers,
127,380 of them not contiguous with the previous one

The 128,671 repeats match master's "shared hit=128671" for the same
scan.

Plain index scans with a cold cache, master 4545cee303c against the
same commit plus v36. The latency comes from dm-delay under ext4: an
8 kB O_DIRECT read takes 2.27 ms. Both scans read the same 9,262
blocks. Median of 5 runs, ms:

io_method=worker, eic 16 (the defaults)
t_id: master 653, v36 318
t_b: master 693, v36 804
io_method=io_uring, eic 16
t_id: master 647, v36 207
t_b: master 698, v36 681
io_method=io_uring, eic 64
t_id: master 656, v36 202
t_b: master 687, v36 730

So:

- With the default io_method=worker, v36 is slower than master on
t_b: 1.16x here, and 1.24x in a separate run of 10 rounds (median
715 vs 890 ms; v36 was slower in all 10 rounds).
- With io_uring the regression goes away, but so does the gain: t_b
runs at master's speed, while t_id, which reads the same blocks,
is 3.1x faster.

The worker result does not come from the size of the worker pool.
With io_min_workers = io_max_workers = 8 or 32, and with eic 64, v36
stays 1.11x to 1.16x slower than master on t_b.

What EXPLAIN (IO) reports for t_b with v36 (range over the runs):

worker, eic 16: I/O count 21,510-24,845, waits 486-544
io_uring, eic 16: I/O count 64,898-65,087, waits 400-422
io_uring, eic 64: I/O count 106,357-106,766, waits 416-472

For t_id it is always 514 I/Os of 15.88 blocks, with 8-11 waits
under io_uring and 120-173 under worker.

The device itself saw almost the same reads in every case: 690
requests of about 110 kB for master and for v36 with io_uring, and
777-812 slightly smaller ones for v36 with worker. So, as
Andres said [2], no second read is started for a block that is
already being read. The repeats cost time inside the stream, not on
the device.

My reading of read_stream.c, which I have not measured separately:
io_count goes up every time StartReadBuffers() returns need_wait, and
that also happens when the block's read is already in progress from
an earlier request of the same stream. Such a request takes one of
the max_ios slots ("in-progress=15.97" with eic 16, 63.8 with eic 64)
without starting a read, so few distinct reads are in flight. A
larger eic lets more repeats into the window, not more reads. If that
is right, it is the part that deduplicating nearby requests would
remove.

Other results, for completeness:

- On the local NVMe without added latency, t_b showed no clear time
difference (the ranges overlap), which is why I added the delay.
- On an uncorrelated index (20k rows, 20,057 blocks), v36 was 2-3x
faster than master on the local NVMe, and 7-8x faster with 1 or
2 ms of latency (3.2-3.7 s against 23.4-24.7 s at 1 ms; 5.6 s
against 43.6-45.4 s at 2 ms).

Not measured: real cloud storage (the delay device adds latency
only, in whole milliseconds, with no IOPS or throughput limit), and
jitter widths other than +/-50 rows.

Attached are the scripts (the builds, the data, the block sequence,
the delay device and the runs) and the raw output of every run. That
includes one set that is not valid, an IOPS cap that btrfs did not
enforce, which is marked as such.

[1] https://www.postgresql.org/message-id/152ea782-5bd4-4435-b021-0ab2da61e63d@vondra.me
[2] https://www.postgresql.org/message-id/tpzzzmjo22mon54wi3443rl55jvdk6jqckkxmr32vbtqv2xigk@vu3ykzppyjsc

--
Manu