MIME-Version: 1.0
References: <CAH2-Wz=6a7fGz2rALDX+xiFDuEaGQWpZ49xEaBUDKiPH8gcL+Q@mail.gmail.com>
 <CAH2-WzkehuhxyuA8quc7rRN3EtNXpiKsjPfO8mhb+0Dr2K0Dtg@mail.gmail.com>
 <CAH2-WzmymSyOt5Y2RGbm6cJXg18J_ttfqjdcpodHe6Gp23ConQ@mail.gmail.com>
 <CAH2-Wznv9_KGqHQ1vCW2pkiA6QskBGcx5NC_-UXnD6GEQasvAQ@mail.gmail.com>
 <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
 <64a2re223ajj4popowsyu4xekbuvvyfwkrihn5yzyrkwsmsuvp@2lls3tpww5dl>
 <a67mvhyi2q45eg4eimhpwdg6l3s3dmpahti2svffvmvzwmss27@r4nohusvndbq>
 <c19a18fc-ef2e-4d91-b7ba-576d1891315d@vondra.me> <il7jtfowpatrlg33qb5plj7v7pferes4ogerq5fdczszi4kokh@sbwvb2ukfgos>
 <52512325-b1f2-4fff-819e-f68122b2e427@vondra.me> <ws47e3wly6skt36b23zy5qfvcxzueo6od3uicunuodsqnxl7os@7v2qi7qkxzbz>
In-Reply-To: <ws47e3wly6skt36b23zy5qfvcxzueo6od3uicunuodsqnxl7os@7v2qi7qkxzbz>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 17 Feb 2026 12:16:23 -0500
Message-ID: <CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com>
Subject: Re: index prefetching
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Alexandre Felipe <o.alexandre.felipe@gmail.com>, 
	Thomas Munro <thomas.munro@gmail.com>, Nazir Bilal Yavuz <byavuz81@gmail.com>, 
	Robert Haas <robertmhaas@gmail.com>, Melanie Plageman <melanieplageman@gmail.com>, 
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>, Georgios <gkokolatos@protonmail.com>, 
	Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Content-Type: multipart/mixed; boundary="000000000000b4a4f6064b083c7a"
Archived-At: <https://www.postgresql.org/message-id/CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J%3DD_voWxZDg%40mail.gmail.com>
Precedence: bulk

--000000000000b4a4f6064b083c7a
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Feb 16, 2026 at 11:48=E2=80=AFAM Andres Freund <andres@anarazel.de>=
 wrote:
> Yes, It's hurting quite substantially. For well correlated index scans it
> prevents readahead from becoming aggressive enough even on a local low la=
tency
> SSD. Which means it'll not even be even remotely aggressive enough on a
> networked block store.

I agree that the current heuristics (which were invented recently) are
too conservative. I overfit the heuristics to my current set of
adversarial queries, as a stopgap measure.

> Note that there is pretty much *no* readhead, because the yields happen m=
ore
> frequently than a io_combine_limit sized IO can be formed.

ISTM that we need the yields to better cooperate with whatever's
happening on the read stream side.

> With the yielding logic disabled:

> The comment seems to say it's about avoiding to look very into the future=
 when
> using index only scans that just need a few heap lookups. Certainly an
> important goal.

The main motivation for yielding is to deal with things like merge
joins fed by at least one plain index scan, and plain scans for an
"ORDER BY .... LIMIT N" query. The LIMIT N is also addressed by the
"Use ExecSetTupleBound hint during index scans" patch, albeit
imperfectly. This is also important with index-only scans, though only
when heap fetches are actually required in the first place (most
index-only scans will never create a read stream in the first place).

I attach an example of where disabling the yield mechanism hurts
instead of helping, to give you a sense of the problems in this area.
Notice that the inner side of the merge join ("Index Scan using
prefetch_customers_pkey on prefetch_customers c") requires the same
number of buffer hits + buffer reads as master with yielding enabled
-- that allows the patch to shave off 7% of master's execution time.
Whereas without yielding, there's quite a lot more buffer hits (for
index page reads) + buffer misses (for heap page reads) -- which hurts
us. Without yielding, the patch takes about 13% longer to execute the
query.

Just to be clear, I'm not arguing that this is the right trade-off.
And I understand that it's just not feasible to completely prevent
such plan shapes from reading more data than strictly necessary -- a
certain amount of that seems like a "cost of doing business". But it
seems important that the added costs of speculatively reading later
batches/index leaf pages never exceeds some fixed threshold.

The non-yielding EXPLAIN ANALYZE has an inner-index scan that does
~60% more work than master/than the yielding variant of the patch.
It's not so much that the amount of extra work we'll perform is
excessive (though it is); what really concerns me is that the amount
of extra work is *indeterminate*. There's likely to be other queries
that are much slower still, at least in the absence of some kind of
yielding mechanism. And so ISTM that we need a better yield mechanism
-- one that is better attuned to the need to maintain an adequate
prefetch distance on the read stream side.

> One thing that does confuse me about the yielding logic is that it seems =
to
> actually put a cap on ever looking more than two batches ahead (with a bi=
t of
> fuzziness)? Why support more batches then (INDEX_SCAN_MAX_BATCHES =3D=3D =
128)?
> Isn't two batches too low for anything with some correlation on even remo=
tely
> higher latency storage?

FWIW that's not actually true; we *can* still use more than 2 batches.
My instrumentation confirms this. Though it seems to top out at about
4 batches total with the current yield logic/with my test suite
queries.

--=20
Peter Geoghegan

--000000000000b4a4f6064b083c7a
Content-Type: text/plain; charset="US-ASCII"; name="plans-yield-noyield.txt"
Content-Disposition: attachment; filename="plans-yield-noyield.txt"
Content-Transfer-Encoding: base64
Content-ID: <f_mlqufzpv0>
X-Attachment-Id: f_mlqufzpv0

QTE1OiByZWdyZXNzZWQgYW50aS1qb2luLCBpbmRleC1vbmx5CiAgbWFzdGVyICAgICAgICAgICAg
ICAgICAgICAgICAgIChtaW4pOiAgICAgICA5LjA0MSBtcyAoYXZnPTkuMTM0LCBtYXg9OS4yNjIp
CiAgcGF0Y2ggKyB5aWVsZCAgICAocHJlZmV0Y2g9b24pIChtaW4pOiAgICAgICA4LjQyOSBtcyAo
YXZnPTguNTIwLCBtYXg9OC42MzYpIFswLjkzMnggdnMgbWFzdGVyXQogIHBhdGNoICsgbm8geWll
bGQgKHByZWZldGNoPW9uKSAobWluKTogICAgICAxMC4yMjUgbXMgKGF2Zz0xMC4zMjMsIG1heD0x
MC4zOTQpIFsxLjEzMXggdnMgbWFzdGVyXQoKICBRdWVyeToKICAgIFNFTEVDVCBvLmN1c3RvbWVy
X2lkLCBvLm9yZGVyX2RhdGUKICAgIEZST00gcHJlZmV0Y2hfb3JkZXJzIG8KICAgIFdIRVJFIG8u
b3JkZXJfZGF0ZSBCRVRXRUVOICcyMDIzLTAyLTA5JyBBTkQgJzIwMjMtMDItMjMnCiAgICBBTkQg
by5jdXN0b21lcl9pZCBCRVRXRUVOIDM2MzA3IEFORCAzNzEyNgogICAgQU5EIE5PVCBFWElTVFMg
KAogICAgU0VMRUNUIDEgRlJPTSBwcmVmZXRjaF9jdXN0b21lcnMgYwogICAgV0hFUkUgYy5jdXN0
b21lcl9pZCA9IG8uY3VzdG9tZXJfaWQKICAgIEFORCBjLnJlZ2lvbl9pZCA9IDQKICAgICkKICAg
IE9SREVSIEJZIG8ub3JkZXJfZGF0ZQoKICBtYXN0ZXIgRVhQTEFJTiBBTkFMWVpFOgogICAgU29y
dCAoYWN0dWFsIHJvd3M9Nzg4Mi4wMCBsb29wcz0xKQogICAgICBTb3J0IEtleTogby5vcmRlcl9k
YXRlCiAgICAgIFNvcnQgTWV0aG9kOiBxdWlja3NvcnQgIE1lbW9yeTogMzc3a0IKICAgICAgQnVm
ZmVyczogc2hhcmVkIGhpdD0xMTczMiByZWFkPTIzNwogICAgICBJL08gVGltaW5nczogc2hhcmVk
IHJlYWQ9MS4xNDUKICAgICAgLT4gIE1lcmdlIEFudGkgSm9pbiAoYWN0dWFsIHJvd3M9Nzg4Mi4w
MCBsb29wcz0xKQogICAgICAgICAgICBNZXJnZSBDb25kOiAoby5jdXN0b21lcl9pZCA9IGMuY3Vz
dG9tZXJfaWQpCiAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MTE3MzIgcmVhZD0yMzcK
ICAgICAgICAgICAgSS9PIFRpbWluZ3M6IHNoYXJlZCByZWFkPTEuMTQ1CiAgICAgICAgICAgIC0+
ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgcHJlZmV0Y2hfb3JkZXJzX2N1c3RfZGF0ZV9pZHggb24g
cHJlZmV0Y2hfb3JkZXJzIG8gKGFjdHVhbCByb3dzPTg0NDUuMDAgbG9vcHM9MSkKICAgICAgICAg
ICAgICAgICAgSW5kZXggQ29uZDogKChjdXN0b21lcl9pZCA+PSAzNjMwNykgQU5EIChjdXN0b21l
cl9pZCA8PSAzNzEyNikgQU5EIChvcmRlcl9kYXRlID49ICcyMDIzLTAyLTA5Jzo6ZGF0ZSkgQU5E
IChvcmRlcl9kYXRlIDw9ICcyMDIzLTAyLTIzJzo6ZGF0ZSkpCiAgICAgICAgICAgICAgICAgIEhl
YXAgRmV0Y2hlczogMAogICAgICAgICAgICAgICAgICBJbmRleCBTZWFyY2hlczogNzcyCiAgICAg
ICAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MTE2MjkKICAgICAgICAgICAgLT4gIElu
ZGV4IFNjYW4gdXNpbmcgcHJlZmV0Y2hfY3VzdG9tZXJzX3BrZXkgb24gcHJlZmV0Y2hfY3VzdG9t
ZXJzIGMgKGFjdHVhbCByb3dzPTE4NTguMDAgbG9vcHM9MSkKICAgICAgICAgICAgICAgICAgRmls
dGVyOiAocmVnaW9uX2lkID0gNCkKICAgICAgICAgICAgICAgICAgUm93cyBSZW1vdmVkIGJ5IEZp
bHRlcjogMzUyODUKICAgICAgICAgICAgICAgICAgSW5kZXggU2VhcmNoZXM6IDEKICAgICAgICAg
ICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0xMDMgcmVhZD0yMzcKICAgICAgICAgICAgICAg
ICAgSS9PIFRpbWluZ3M6IHNoYXJlZCByZWFkPTEuMTQ1CiAgICBQbGFubmluZzoKICAgICAgQnVm
ZmVyczogc2hhcmVkIGhpdD0xNgogICAgUGxhbm5pbmcgVGltZTogMC4xNTUgbXMKICAgIFNlcmlh
bGl6YXRpb246IG91dHB1dD0xOTNrQiAgZm9ybWF0PXRleHQKICAgIEV4ZWN1dGlvbiBUaW1lOiA5
LjA0MSBtcwoKICBwYXRjaCArIHlpZWxkIChwcmVmZXRjaD1vbikgRVhQTEFJTiBBTkFMWVpFOgog
ICAgU29ydCAoYWN0dWFsIHJvd3M9Nzg4Mi4wMCBsb29wcz0xKQogICAgICBTb3J0IEtleTogby5v
cmRlcl9kYXRlCiAgICAgIFNvcnQgTWV0aG9kOiBxdWlja3NvcnQgIE1lbW9yeTogMzc3a0IKICAg
ICAgQnVmZmVyczogc2hhcmVkIGhpdD0xMTczMiByZWFkPTIzNwogICAgICBJL08gVGltaW5nczog
c2hhcmVkIHJlYWQ9MC44MjUKICAgICAgLT4gIE1lcmdlIEFudGkgSm9pbiAoYWN0dWFsIHJvd3M9
Nzg4Mi4wMCBsb29wcz0xKQogICAgICAgICAgICBNZXJnZSBDb25kOiAoby5jdXN0b21lcl9pZCA9
IGMuY3VzdG9tZXJfaWQpCiAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MTE3MzIgcmVh
ZD0yMzcKICAgICAgICAgICAgSS9PIFRpbWluZ3M6IHNoYXJlZCByZWFkPTAuODI1CiAgICAgICAg
ICAgIC0+ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgcHJlZmV0Y2hfb3JkZXJzX2N1c3RfZGF0ZV9p
ZHggb24gcHJlZmV0Y2hfb3JkZXJzIG8gKGFjdHVhbCByb3dzPTg0NDUuMDAgbG9vcHM9MSkKICAg
ICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKChjdXN0b21lcl9pZCA+PSAzNjMwNykgQU5EIChj
dXN0b21lcl9pZCA8PSAzNzEyNikgQU5EIChvcmRlcl9kYXRlID49ICcyMDIzLTAyLTA5Jzo6ZGF0
ZSkgQU5EIChvcmRlcl9kYXRlIDw9ICcyMDIzLTAyLTIzJzo6ZGF0ZSkpCiAgICAgICAgICAgICAg
ICAgIEhlYXAgRmV0Y2hlczogMAogICAgICAgICAgICAgICAgICBJbmRleCBTZWFyY2hlczogNzcy
CiAgICAgICAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MTE2MjkKICAgICAgICAgICAg
LT4gIEluZGV4IFNjYW4gdXNpbmcgcHJlZmV0Y2hfY3VzdG9tZXJzX3BrZXkgb24gcHJlZmV0Y2hf
Y3VzdG9tZXJzIGMgKGFjdHVhbCByb3dzPTE4NTguMDAgbG9vcHM9MSkKICAgICAgICAgICAgICAg
ICAgRmlsdGVyOiAocmVnaW9uX2lkID0gNCkKICAgICAgICAgICAgICAgICAgUm93cyBSZW1vdmVk
IGJ5IEZpbHRlcjogMzUyODUKICAgICAgICAgICAgICAgICAgSW5kZXggU2VhcmNoZXM6IDEKICAg
ICAgICAgICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0xMDMgcmVhZD0yMzcKICAgICAgICAg
ICAgICAgICAgSS9PIFRpbWluZ3M6IHNoYXJlZCByZWFkPTAuODI1CiAgICBQbGFubmluZzoKICAg
ICAgQnVmZmVyczogc2hhcmVkIGhpdD0xNgogICAgUGxhbm5pbmcgVGltZTogMC4xNjYgbXMKICAg
IFNlcmlhbGl6YXRpb246IG91dHB1dD0xOTNrQiAgZm9ybWF0PXRleHQKICAgIEV4ZWN1dGlvbiBU
aW1lOiA4LjQyOSBtcwoKICBwYXRjaCArIG5vIHlpZWxkIChwcmVmZXRjaD1vbikgRVhQTEFJTiBB
TkFMWVpFOgogICAgU29ydCAoYWN0dWFsIHJvd3M9Nzg4Mi4wMCBsb29wcz0xKQogICAgICBTb3J0
IEtleTogby5vcmRlcl9kYXRlCiAgICAgIFNvcnQgTWV0aG9kOiBxdWlja3NvcnQgIE1lbW9yeTog
Mzc3a0IKICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0xMTc5NSByZWFkPTM4NQogICAgICBJL08g
VGltaW5nczogc2hhcmVkIHJlYWQ9MS41MTcKICAgICAgLT4gIE1lcmdlIEFudGkgSm9pbiAoYWN0
dWFsIHJvd3M9Nzg4Mi4wMCBsb29wcz0xKQogICAgICAgICAgICBNZXJnZSBDb25kOiAoby5jdXN0
b21lcl9pZCA9IGMuY3VzdG9tZXJfaWQpCiAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9
MTE3OTUgcmVhZD0zODUKICAgICAgICAgICAgSS9PIFRpbWluZ3M6IHNoYXJlZCByZWFkPTEuNTE3
CiAgICAgICAgICAgIC0+ICBJbmRleCBPbmx5IFNjYW4gdXNpbmcgcHJlZmV0Y2hfb3JkZXJzX2N1
c3RfZGF0ZV9pZHggb24gcHJlZmV0Y2hfb3JkZXJzIG8gKGFjdHVhbCByb3dzPTg0NDUuMDAgbG9v
cHM9MSkKICAgICAgICAgICAgICAgICAgSW5kZXggQ29uZDogKChjdXN0b21lcl9pZCA+PSAzNjMw
NykgQU5EIChjdXN0b21lcl9pZCA8PSAzNzEyNikgQU5EIChvcmRlcl9kYXRlID49ICcyMDIzLTAy
LTA5Jzo6ZGF0ZSkgQU5EIChvcmRlcl9kYXRlIDw9ICcyMDIzLTAyLTIzJzo6ZGF0ZSkpCiAgICAg
ICAgICAgICAgICAgIEhlYXAgRmV0Y2hlczogMAogICAgICAgICAgICAgICAgICBJbmRleCBTZWFy
Y2hlczogNzcyCiAgICAgICAgICAgICAgICAgIEJ1ZmZlcnM6IHNoYXJlZCBoaXQ9MTE2MjkKICAg
ICAgICAgICAgLT4gIEluZGV4IFNjYW4gdXNpbmcgcHJlZmV0Y2hfY3VzdG9tZXJzX3BrZXkgb24g
cHJlZmV0Y2hfY3VzdG9tZXJzIGMgKGFjdHVhbCByb3dzPTE4NTguMDAgbG9vcHM9MSkKICAgICAg
ICAgICAgICAgICAgRmlsdGVyOiAocmVnaW9uX2lkID0gNCkKICAgICAgICAgICAgICAgICAgUm93
cyBSZW1vdmVkIGJ5IEZpbHRlcjogMzUyODUKICAgICAgICAgICAgICAgICAgSW5kZXggU2VhcmNo
ZXM6IDEKICAgICAgICAgICAgICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0xNjYgcmVhZD0zODUK
ICAgICAgICAgICAgICAgICAgSS9PIFRpbWluZ3M6IHNoYXJlZCByZWFkPTEuNTE3CiAgICBQbGFu
bmluZzoKICAgICAgQnVmZmVyczogc2hhcmVkIGhpdD0xNgogICAgUGxhbm5pbmcgVGltZTogMC4x
NDkgbXMKICAgIFNlcmlhbGl6YXRpb246IG91dHB1dD0xOTNrQiAgZm9ybWF0PXRleHQKICAgIEV4
ZWN1dGlvbiBUaW1lOiAxMC4yMjUgbXMK
--000000000000b4a4f6064b083c7a--