Hi,
On 2026-02-24 13:13:25 -0500, Peter Geoghegan wrote:
> > Plausible. It could be that we could get away with controlling the rampup
> > to
> > be slower in potentially problematic cases, without needing the yielding,
> > but
> > not sure.
>
> Attached is v11, which makes the read stream yielding mechanism better
> cooperate with index prefetching, so as to avoid interefering with
> io_combine_limit. This should deal with the odd performance that you
> complained about. See
> v11-0006-Introduce-read_stream_-pause-resume-yield.patch (and the
> later prefetching patch
> v11-0007-Add-heapam-index-scan-I-O-prefetching.patch) for details.
>
> The whole idea of measuring "batch distance" is gone in this version,
> though we do still only consider whether now is a good time to yield
> at "batch boundaries". We always refuse yield on the first few batches
> of the scan, so the idea of caring about batch boundaries is still
> there, albeit in a much more limited form.
I'm planning to do some reviewing in the next days. In preparation I just
retried a benchmark and saw some odd results. After a while I was able to
reproduce even with a simpler setup:
-c shared_buffers=2GB -c debug_io_direct=data -c io_method=io_uring
pgbench -i -q -s 100 --fillfactor=90
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│
QUERY PLAN
│
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.43..441511.11 rows=10000045 width=97) (actual time=0.308..6101.837
rows=10000000.00 loops=1) │
│ Index Searches: 1
│
│ Buffers: shared hit=27325 read=181819
│
│ I/O Timings: shared read=4538.003
│
│ Planning Time: 0.041 ms
│
│ Execution Time: 6433.192 ms
│
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
pgbench -i -q -s 100 --fillfactor=50
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│
QUERY PLAN
│
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.43..593022.41 rows=9999798 width=97) (actual time=0.131..3973.698
rows=10000000.00 loops=1) │
│ Index Searches: 1
│
│ Buffers: shared hit=19239 read=341420
│
│ I/O Timings: shared read=1752.057
│
│ Planning:
│
│ Buffers: shared hit=42 read=15
│
│ Planning Time: 1.668 ms
│
│ Execution Time: 4308.182 ms
│
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
pgbench -i -q -s 100 --fillfactor=25
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│
QUERY PLAN
│
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.43..926358.51 rows=10000005 width=97) (actual time=0.112..3259.362
rows=10000000.00 loops=1) │
│ Index Searches: 1
│
│ Buffers: shared hit=9610 read=684382
│
│ I/O Timings: shared read=242.259
│
│ Planning:
│
│ Buffers: shared hit=18
│
│ Planning Time: 0.097 ms
│
│ Execution Time: 3594.782 ms
│
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Note how the increase in scanned heap pages actually *decreases* the overall
time rather substantially.
It's quite visible, both in iostat, and a query like
SELECT pid, target_desc, off, length FROM pg_aios \watch 0.5
that for the first query has basically no IO concurrency, the second has very
intermittent IO concurrency and the third one has nice IO concurrency.
If I disable the yield logic, the fillfactor=90 case is good:
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│
QUERY PLAN
│
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using pgbench_accounts_pkey on pgbench_accounts
(cost=0.43..441511.11 rows=10000045 width=97) (actual time=0.470..1662.331
rows=10000000.00 loops=1) │
│ Index Searches: 1
│
│ Buffers: shared hit=27325 read=181819
│
│ I/O Timings: shared read=21.113
│
│ Planning Time: 0.043 ms
│
│ Execution Time: 1995.723 ms
│
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
Of course this is a silly query, but you'd also see that with a mergejoin or
such.
Greetings,
Andres Freund
