Scripts used for the measurements in the reply (bash + psql, Linux).

Order: build.sh (two installs from one master commit), setup.sh (one data
directory, 8M rows), blockseq.sql (the block sequence each index hands to the
read stream), slowdisk.sh (a block device with a fixed per-read delay),
bench.sh (one run per build and scenario), bench_latency.sh and
bench_ioconf.sh (the loops over read delay and I/O settings).

Paths assume ~/pgpf; port 5690; sudo is needed only by slowdisk.sh.


======== build.sh ========

#!/bin/bash
# Build two PostgreSQL installs from the same master commit: without and
# with the v36 index prefetching series.  -O2, no assertions, so timings
# are comparable with the ones posted on the thread.
set -eu
BASE=${BASE:-4545cee303c}
SRC=$HOME/Proyectos/postgresql
W=$HOME/pgpf
build() {  # name worktree
  local name=$1 tree=$2
  cd $tree
  # --with-liburing was added after the first measurements (io_method=worker
  # results do not depend on it); it makes io_method=io_uring available
  ./configure --prefix=$W/i-$name --without-icu --with-liburing CFLAGS='-O2' > $W/configure-$name.log 2>&1
  # without --enable-depend a changed pg_config.h rebuilds nothing: start clean
  make -s clean > /dev/null 2>&1
  make -j"$(nproc)" -s > $W/make-$name.log 2>&1
  make -s install > $W/install-$name.log 2>&1
  make -C contrib/pg_prewarm -s install >> $W/install-$name.log 2>&1
  make -C contrib/pg_buffercache -s install >> $W/install-$name.log 2>&1
  echo "$name built: $($W/i-$name/bin/postgres --version)"
}
V36=${V36:-$(cd "$(dirname "$0")" && pwd)/v36}   # the ten v36-*.patch files from the thread
[ -d $W/src-base ] || git -C $SRC worktree add -q --detach $W/src-base $BASE
if [ ! -d $W/src-v36 ]; then
  git -C $SRC worktree add -q --detach $W/src-v36 $BASE
  git -C $W/src-v36 am -q $V36/*.patch
fi
build base $W/src-base
build v36 $W/src-v36
echo BUILD-DONE


======== setup.sh ========

#!/bin/bash
# One data directory for all three builds (same catversion): the data is
# identical and only the server binary changes between runs.
#
# t.a  is a permutation of id: index order unrelated to heap order.
# t.b  is id plus a deterministic jitter of +/-50 rows: nearly correlated,
#      so consecutive index entries keep returning to the same few heap
#      pages (A, B, A, B), which the callback's "same block twice in a row"
#      check does not collapse.
set -eu
W=$HOME/pgpf
PG=$W/i-base/bin
D=$W/data
PORT=5690
rm -rf $D
$PG/initdb -D $D -U postgres >/dev/null
cat >> $D/postgresql.conf <<EOF
port = $PORT
unix_socket_directories = '/tmp'
shared_buffers = 512MB
track_io_timing = on
max_parallel_workers_per_gather = 0
jit = off
autovacuum = off
EOF
$PG/pg_ctl -D $D -l $D/log -w start >/dev/null
$PG/psql -h /tmp -p $PORT -U postgres -XAq <<'SQL'
CREATE EXTENSION pg_prewarm;
CREATE TABLE t (id int, a int, b int, filler text);
INSERT INTO t
SELECT i,
       (i::bigint * 2654435761 % 8000000)::int,           -- permutation of 0..N-1
       i + ((i::bigint * 7919) % 101)::int - 50,           -- +/-50 row jitter
       repeat('x', 120)
FROM generate_series(1, 8000000) i;
CREATE INDEX t_a ON t (a);
CREATE INDEX t_b ON t (b);
CREATE INDEX t_id ON t (id);                              -- same heap range, no jitter
VACUUM (FREEZE, ANALYZE) t;
SELECT 'heap pages ' || pg_relation_size('t') / 8192,
       'size ' || pg_size_pretty(pg_relation_size('t')),
       'corr a ' || round(correlation::numeric, 3)
  FROM pg_stats WHERE tablename = 't' AND attname = 'a';
SELECT 'corr b ' || round(correlation::numeric, 3) FROM pg_stats WHERE tablename = 't' AND attname = 'b';
SQL
$PG/pg_ctl -D $D -m fast -w stop >/dev/null
echo SETUP-DONE


======== blockseq.sql ========

-- The sequence of heap block numbers an index scan hands to the read stream,
-- computed from the data, for the two indexes over the same heap range.
-- nbtree returns equal keys in heap TID order, so (key, ctid) is index order.
-- "handed to the stream": the callback skips a block equal to the one just
-- before it, so only changes of block number count.
-- "breaks": a change to a block that is not the next one, i.e. where two
-- consecutive requests cannot be combined into one I/O.
WITH scan AS (
  SELECT 'id' AS idx, (ctid::text::point)[0]::bigint AS blk,
         row_number() OVER (ORDER BY id, ctid) AS n
    FROM t WHERE id BETWEEN 1000000 AND 1399999
  UNION ALL
  SELECT 'b', (ctid::text::point)[0]::bigint,
         row_number() OVER (ORDER BY b, ctid)
    FROM t WHERE b BETWEEN 1000000 AND 1399999
), seq AS (
  SELECT idx, blk, lag(blk) OVER (PARTITION BY idx ORDER BY n) AS prev FROM scan
)
SELECT idx,
       count(DISTINCT blk)                                          AS distinct_blocks,
       count(*) FILTER (WHERE prev IS NULL OR blk <> prev)          AS handed_to_stream,
       count(*) FILTER (WHERE blk <> prev AND blk <> prev + 1)      AS breaks
  FROM seq GROUP BY idx ORDER BY idx DESC;

SELECT attname, round(correlation::numeric, 4) AS correlation
  FROM pg_stats WHERE tablename = 't' AND attname IN ('id', 'a', 'b') ORDER BY attname;
SELECT pg_relation_size('t') / 8192 AS heap_pages;


======== slowdisk.sh ========

#!/bin/bash
# A block device with a fixed per-read latency, to stand in for a cloud
# volume (EBS gp3 reads take roughly 0.5-1 ms; a local NVMe ~0.05 ms).
#
#   file (nocow, so btrfs neither compresses nor caches it separately)
#     -> loop device with direct I/O (no second page cache below ext4)
#     -> dm-delay (every read delayed by READ_MS, writes not delayed)
#     -> ext4, mounted at $MNT
#
# dm-delay delays each bio by a timer, so concurrent reads overlap: this
# models latency, not a throughput cap, which is what prefetching hides.
# dm-delay only takes whole milliseconds.
#
# usage: slowdisk.sh up | down | setdelay <ms> | status
set -eu
W=$HOME/pgpf
IMG=$W/slowdisk.img
MNT=$W/slow
NAME=pgpf_slow
SIZE=6G

loopdev() { losetup -j "$IMG" -O NAME -n | head -1; }

table() {  # read_ms
  local dev sectors
  dev=$(loopdev)
  sectors=$(sudo blockdev --getsz "$dev")
  echo "0 $sectors delay $dev 0 $1 $dev 0 0"
}

case ${1:-status} in
  up)
    sudo modprobe dm-delay
    if [ ! -e "$IMG" ]; then
      touch "$IMG"; chattr +C "$IMG"      # must be set while the file is empty
      fallocate -l $SIZE "$IMG"
    fi
    [ -n "$(loopdev)" ] || sudo losetup --direct-io=on -f "$IMG"
    sudo dmsetup create $NAME --table "$(table 0)"
    sudo blkid /dev/mapper/$NAME >/dev/null || sudo mkfs.ext4 -q /dev/mapper/$NAME
    mkdir -p "$MNT"
    sudo mount -o noatime /dev/mapper/$NAME "$MNT"
    sudo chown "$(id -u):$(id -g)" "$MNT"
    ;;
  setdelay)
    # swap the table in place; the filesystem stays mounted
    sudo dmsetup suspend $NAME
    sudo dmsetup load $NAME --table "$(table "$2")"
    sudo dmsetup resume $NAME
    ;;
  down)
    mountpoint -q "$MNT" && sudo umount "$MNT"
    sudo dmsetup remove $NAME 2>/dev/null || true
    dev=$(loopdev); [ -z "$dev" ] || sudo losetup -d "$dev"
    ;;
  status)
    sudo dmsetup table $NAME 2>/dev/null || echo "no $NAME"
    losetup -j "$IMG" || true
    ;;
esac


======== bench.sh ========

#!/bin/bash
# Plain index scans with I/O, three builds from one master commit:
#   base      master (4545cee303c)
#   v36       master + v36 series (heap prefetching with READ_STREAM_DEFAULT)
#   v36full   v36 with that one call changed to READ_STREAM_FULL
#
# Method:
#   - one data directory, only the server binary changes
#   - build order rotated every round, so cache warm-up is not attributed
#     to any one build
#   - before every run: server stopped, every data file evicted from the
#     kernel page cache (posix_fadvise DONTNEED), server started inside a
#     memory cgroup so the table cannot live in the host page cache
#
# Scenarios:
#   cold_uncorr  t.a, index order unrelated to heap order, 20k rows
#   cold_near    t.b, nearly correlated (+/-50 row jitter), 400k rows
#   cold_corr    t.id, the same 400k rows and heap range, no jitter
#   warm25_uncorr  t.a after loading the index and 20% of the heap pages
#                  (8 of every 40 blocks) into shared_buffers, so hits and
#                  misses interleave along the scan
#
# Environment:
#   SCENARIOS, BUILDS  which to run
#   D                  data directory (default ~/pgpf/data)
#   PGOPTS             extra server options, e.g. "-c io_method=io_uring"
#   STATDEV            a /sys/block name: also report the read requests that
#                      reached that device during the query
#   IOPS, IODEV        cgroup read IOPS cap; NOT enforced on btrfs here
#
# usage: bench.sh [rounds] [MemoryMax]
set -u
R=${1:-3}
MEM=${2:-900M}
W=$HOME/pgpf
D=${D:-$W/data}   # D=$W/slow/data runs on the delayed device (slowdisk.sh)
PORT=5690
OUT=${OUT:-$(dirname "$0")/results.txt}

evict() {
  python3 - "$D/base" <<'PY'
import os, sys, pathlib
for f in pathlib.Path(sys.argv[1]).rglob("*"):
    if f.is_file():
        try:
            fd = os.open(f, os.O_RDONLY); os.fsync(fd)
            os.posix_fadvise(fd, 0, 0, os.POSIX_FADV_DONTNEED); os.close(fd)
        except OSError:
            pass
PY
}

run() {  # build scenario
  local b=$1 s=$2 PG=$W/i-$1/bin col lo hi opts
  $PG/pg_ctl -D $D -m fast -w stop >/dev/null 2>&1
  sleep 1; evict
  # IOPS=<n> caps read IOPS on the data device, like a provisioned cloud volume
  systemd-run --user --scope -q -p MemoryMax=$MEM -p MemoryHigh=$MEM --unit=pgpf-$$-$RANDOM \
    ${IOPS:+-p "IOReadIOPSMax=${IODEV:-/dev/nvme0n1} $IOPS"} \
    $PG/pg_ctl -D $D -l $D/log ${PGOPTS:+-o "$PGOPTS"} -w start >/dev/null 2>&1
  q() { $PG/psql -h /tmp -p $PORT -U postgres -XAtq -c "$1"; }
  case $s in
    cold_uncorr)   col=a; lo=1000000; hi=1019999 ;;
    cold_near)     col=b; lo=1000000; hi=1399999 ;;
    cold_corr)     col=id; lo=1000000; hi=1399999 ;;   # same heap region as cold_near, no jitter
    warm25_uncorr) col=a; lo=1000000; hi=1019999
                   q "SELECT pg_prewarm('t_a')" >/dev/null
                   q "SELECT sum(pg_prewarm('t', 'buffer', 'main', g, g + 7))
                        FROM generate_series(0, pg_relation_size('t') / 8192 - 8, 40) g" >/dev/null ;;
  esac
  opts="ANALYZE, BUFFERS"; [ $b != base ] && opts="$opts, IO"
  # STATDEV=<name in /sys/block> adds what reached that device during the query:
  # read requests completed, requests merged by the block layer, kB per request
  local st0 st1 line
  [ -n "${STATDEV:-}" ] && st0=$(cat /sys/block/$STATDEV/stat)
  line=$($PG/psql -h /tmp -p $PORT -U postgres -XAtq <<SQL | awk -v b=$b -v s=$s '
      /Execution Time/ { sub(/.*Execution Time: /, ""); sub(/ ms/, ""); t = $0 }
      /Buffers: shared/ && !buf { buf = $0; sub(/^ *Buffers: /, "", buf) }
      /Prefetch:/ { pf = $0; sub(/^ */, "", pf) }
      /I\/O:/ { io = $0; sub(/^ */, "", io) }
      END { printf "%-14s %-8s %10s ms | %s | %s | %s", s, b, t, buf, pf, io }'
SET enable_seqscan = off; SET enable_bitmapscan = off; SET enable_indexonlyscan = off;
EXPLAIN ($opts) SELECT sum(length(filler)) FROM t WHERE $col BETWEEN $lo AND $hi;
SQL
)
  if [ -n "${STATDEV:-}" ]; then
    st1=$(cat /sys/block/$STATDEV/stat)
    line="$line | dev: $(echo $st0 $st1 | awk '{ r = $18 - $1; m = $19 - $2; kb = ($20 - $3) / 2
        printf "reads=%d merged=%d kB/read=%.1f", r, m, r ? kb / r : 0 }')"
  fi
  echo "$line"
}

SCENARIOS=${SCENARIOS:-"cold_uncorr cold_near warm25_uncorr"}
BUILDS=${BUILDS:-"base v36 v36full"}
: > $OUT
for s in $SCENARIOS; do
  for r in $(seq 1 $R); do
    set -- $BUILDS
    # rotate the build order by one position every round
    for _ in $(seq 2 $r); do set -- "${@:2}" "$1"; done
    for b in "$@"; do run $b $s | tee -a $OUT; done
  done
done
$W/i-base/bin/pg_ctl -D $D -m fast -w stop >/dev/null 2>&1
echo BENCH-DONE


======== bench_latency.sh ========

#!/bin/bash
# Run bench.sh on the delayed device (slowdisk.sh) at several per-read
# latencies. One results file per latency.
# usage: bench_latency.sh [rounds] ["ms list"]
set -eu
B=$(cd "$(dirname "$0")" && pwd)
R=${1:-3}
export D=$HOME/pgpf/slow/data
export SCENARIOS=${SCENARIOS:-"cold_uncorr cold_near cold_corr"}
export BUILDS=${BUILDS:-"base v36"}
for ms in ${2:-0 1 2}; do
  bash $B/slowdisk.sh setdelay $ms
  echo "== read delay ${ms} ms"
  OUT=$B/results_delay${ms}ms.txt bash $B/bench.sh $R
done
echo LATENCY-DONE


======== bench_ioconf.sh ========

#!/bin/bash
# Nearly correlated scan at 2 ms read latency, varying the settings that bound
# how many reads can be in flight: io_workers (io_method=worker) and
# effective_io_concurrency. One results file per setting.
# usage: bench_ioconf.sh [rounds]
set -eu
B=$(cd "$(dirname "$0")" && pwd)
R=${1:-5}
export D=$HOME/pgpf/slow/data
export STATDEV=$(basename "$(readlink -f /dev/mapper/pgpf_slow)")
export SCENARIOS=${SCENARIOS:-cold_near}
export BUILDS="base v36"
bash $B/slowdisk.sh setdelay 2
# PG19 sizes the I/O worker pool dynamically: io_min_workers (default 2) at
# start, one more every io_worker_launch_interval (100ms) up to io_max_workers (8)
CONFS=${CONFS:-"io_min_workers=2 io_max_workers=8 effective_io_concurrency=16
io_min_workers=8 io_max_workers=8 effective_io_concurrency=16
io_min_workers=32 io_max_workers=32 effective_io_concurrency=16
io_min_workers=32 io_max_workers=32 effective_io_concurrency=64"}
while IFS= read -r conf; do
  tag=$(echo "$conf" | sed -e 's/io_min_workers=\([0-9]*\) io_max_workers=\([0-9]*\)/w\1-\2/' \
                          -e 's/io_method=//' -e 's/ effective_io_concurrency=/_eic/')
  export PGOPTS=$(printf -- '-c %s ' $conf)
  echo "== $conf"
  OUT=$B/results_2ms_$tag.txt bash $B/bench.sh $R < /dev/null
done <<< "$CONFS"
echo IOCONF-DONE