MIME-Version: 1.0
References: <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>
 <CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com> <64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep>
In-Reply-To: <64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep>
From: Peter Geoghegan <pg@bowt.ie>
Date: Tue, 17 Feb 2026 15:16:06 -0500
Message-ID: <CAH2-Wz=GQaLKy3qaq1iYuKZOKt_2YVx13_wxcTf+xcLMEO4qyg@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="00000000000074b58d064b0abf03"
Archived-At: <https://www.postgresql.org/message-id/CAH2-Wz%3DGQaLKy3qaq1iYuKZOKt_2YVx13_wxcTf%2BxcLMEO4qyg%40mail.gmail.com>
Precedence: bulk

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

On Tue, Feb 17, 2026 at 2:27=E2=80=AFPM Andres Freund <andres@anarazel.de> =
wrote:
> On 2026-02-17 12:16:23 -0500, Peter Geoghegan wrote:
> > On Mon, Feb 16, 2026 at 11:48=E2=80=AFAM Andres Freund <andres@anarazel=
.de> wrote:
> > 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.
>
> Are you doing any testing on higher latency storage?  I found it to be qu=
ite
> valuable to use dm_delay to have a disk with reproducible (i.e. not cloud=
)
> higher latency (i.e. not just a local SSD).

I sometimes use dm_delay (with the minimum 1ms delay) when testing,
but don't do so regularly. Just because it's inconvenient to do so
(perhaps not a great reason).

> Low latency NVMe can reduce the
> penalty of not enough readahead so much that it's hard to spot problems..=
.

I'll keep that in mind.

> > ISTM that we need the yields to better cooperate with whatever's
> > happening on the read stream side.
>
> Plausible.  It could be that we could get away with controlling the rampu=
p to
> be slower in potentially problematic cases, without needing the yielding,=
 but
> not sure.
>
> If that doesn't work, it might just be sufficient to increase the number =
of
> batches that trigger yields as the scan goes on (perhaps by taking the nu=
mber
> of already "consumed" batches into account).

It could make sense to take the number of consumed batches into
account. In general, I think the best approach will be one that
combines multiple complementary strategies.

Passing down a LIMIT N hint has proven to be a good idea -- and it
doesn't really require applying any information related to the read
stream. That's enough to prevent problems in the really extreme cases
(e.g., nested loop antijoins with a LIMIT 1 on the inner side). The
problematic merge join I showed you is a not-so-extreme case, which
makes it trickier. ISTM that taking into consideration the number of
"consumed" batches will not help that particular merge join query,
precisely because it's not-so-extreme: the inner index scan consumes
plenty of batches, but is nevertheless significantly regressed (at
least when we don't yield at all).

> To evaluate the amount of wasted work, it could be useful to make the rea=
d
> stream stats page spit out the amount of "unconsumed" IOs at the end of t=
he
> scan.

That would make sense. You can already tell when that's happened by
comparing the details shown by EXPLAIN ANALYZE against the same query
execution on master, but that approach is inconvenient. Automating my
microbenchmarks has proven to be important with this project. There's
quite a few competing considerations, and it's too easy to improve one
query at the cost of regressing another.

> > 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.
>
> Would be good to document why the yielding exists more extensively in the
> comment above it...

I agree that the comments aren't fully worked out. Mostly because my
understanding of what's going on here isn't fully worked out.

> > 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.
>
> What data/schema is that? Looks kinda but not really TPC-H like.

Attached SQL script generates the same test data. There is a dimension
table, which might make it similar to TPC-H, though that wasn't really
intentional.

> I assume that there are no mark & restores in the query, given that presu=
mably
> the inner side is unique?

Right; this particular query doesn't use mark and restore.

I do have another test query that does use mark and restore (a
self-join + range conditions), but so far that doesn't seem to be too
much of a problem. We have to reset the read stream when we restore a
mark, which creates noticeable overhead. But (it seems) usually not
enough added overhead for it to really matter.

FWIW when the inner side of a merge join is an index-only scan, and we
have to mark + restore a lot, the patch is quite a lot faster than
master -- even when everything is cached. We don't have to repeatedly
do the same VM lookups on the inner side (we can just use our local VM
cache instead).

--=20
Peter Geoghegan

--00000000000074b58d064b0abf03
Content-Type: application/octet-stream; name="generate_benchmark_data.sql"
Content-Disposition: attachment; filename="generate_benchmark_data.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_mlr0zctu0>
X-Attachment-Id: f_mlr0zctu0

LS0gR2VuZXJhdGUgZGF0YSBmb3IgdGhlIGRlZmF1bHQgYmVuY2htYXJrIHN1aXRlIGluIHByZWZl
dGNoX2JlbmNobWFyay5weS4KClx0aW1pbmcgb24KCi0tIERyb3AgZXhpc3RpbmcgdGFibGVzCkRS
T1AgVEFCTEUgSUYgRVhJU1RTIHByZWZldGNoX29yZGVycyBDQVNDQURFOwpEUk9QIFRBQkxFIElG
IEVYSVNUUyBwcmVmZXRjaF9jdXN0b21lcnMgQ0FTQ0FERTsKRFJPUCBUQUJMRSBJRiBFWElTVFMg
cHJlZmV0Y2hfcHJvZHVjdHMgQ0FTQ0FERTsKRFJPUCBUQUJMRSBJRiBFWElTVFMgcHJlZmV0Y2hf
c2VxdWVudGlhbCBDQVNDQURFOwpEUk9QIFRBQkxFIElGIEVYSVNUUyBwcmVmZXRjaF9zcGFyc2Ug
Q0FTQ0FERTsKCi0tIE1haW4gZmFjdCB0YWJsZTogfjUwTSByb3dzIHdpdGggbG93IGZpbGxmYWN0
b3IKQ1JFQVRFIFVOTE9HR0VEIFRBQkxFIHByZWZldGNoX29yZGVycyAoCiAgb3JkZXJfaWQgYmln
aW50LAogIGN1c3RvbWVyX2lkIGludCwKICBwcm9kdWN0X2lkIGludCwKICBvcmRlcl9kYXRlIGRh
dGUsCiAgcmVnaW9uX2lkIGludCwKICBhbW91bnQgbnVtZXJpYygxMCwyKQopIFdJVEggKGZpbGxm
YWN0b3IgPSA0MCk7CgotLSBEaW1lbnNpb24gdGFibGVzCkNSRUFURSBVTkxPR0dFRCBUQUJMRSBw
cmVmZXRjaF9jdXN0b21lcnMgKAogIGN1c3RvbWVyX2lkIGludCBQUklNQVJZIEtFWSwKICByZWdp
b25faWQgaW50LAogIGN1c3RvbWVyX25hbWUgdGV4dAopOwoKQ1JFQVRFIFVOTE9HR0VEIFRBQkxF
IHByZWZldGNoX3Byb2R1Y3RzICgKICBwcm9kdWN0X2lkIGludCBQUklNQVJZIEtFWSwKICBjYXRl
Z29yeV9pZCBpbnQsCiAgcHJvZHVjdF9uYW1lIHRleHQKKTsKCi0tIExvYWQgY3VzdG9tZXJzICgx
MDBLKQpJTlNFUlQgSU5UTyBwcmVmZXRjaF9jdXN0b21lcnMgKGN1c3RvbWVyX2lkLCByZWdpb25f
aWQsIGN1c3RvbWVyX25hbWUpClNFTEVDVCBpLCAoaSAlIDIwKSArIDEsICdDdXN0b21lcl8nIHx8
IGkKRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgMTAwMDAwKSBpOwoKLS0gTG9hZCBwcm9kdWN0cyAo
MTBLKQpJTlNFUlQgSU5UTyBwcmVmZXRjaF9wcm9kdWN0cyAocHJvZHVjdF9pZCwgY2F0ZWdvcnlf
aWQsIHByb2R1Y3RfbmFtZSkKU0VMRUNUIGksIChpICUgNTApICsgMSwgJ1Byb2R1Y3RfJyB8fCBp
CkZST00gZ2VuZXJhdGVfc2VyaWVzKDEsIDEwMDAwKSBpOwoKLS0gU2V0IGRldGVybWluaXN0aWMg
c2VlZApTRUxFQ1Qgc2V0c2VlZCgwLjUpOwoKLS0gTG9hZCBvcmRlcnMgd2l0aCBjb250cm9sbGVk
IHNjYXR0ZXIgcGF0dGVybgpJTlNFUlQgSU5UTyBwcmVmZXRjaF9vcmRlcnMgKG9yZGVyX2lkLCBj
dXN0b21lcl9pZCwgcHJvZHVjdF9pZCwgb3JkZXJfZGF0ZSwgcmVnaW9uX2lkLCBhbW91bnQpClNF
TEVDVAogIHJvd19udW1iZXIoKSBvdmVyICgpIGFzIG9yZGVyX2lkLAogIGN1c3RvbWVyX2lkLAog
IHByb2R1Y3RfaWQsCiAgb3JkZXJfZGF0ZSwKICAoY3VzdG9tZXJfaWQgJSAyMCkgKyAxIGFzIHJl
Z2lvbl9pZCwKICAocmFuZG9tKCkgKiAxMDAwKTo6bnVtZXJpYygxMCwyKSBhcyBhbW91bnQKRlJP
TSAoCiAgU0VMRUNUCiAgICAoKGcuaSAtIDEpICUgMTAwMDAwKSArIDEgYXMgY3VzdG9tZXJfaWQs
CiAgICAoKGcuaSAtIDEpICUgMTAwMDApICsgMSBhcyBwcm9kdWN0X2lkLAogICAgJzIwMjMtMDEt
MDEnOjpkYXRlICsgKChnLmkgLSAxKSAlIDczMCkgYXMgb3JkZXJfZGF0ZQogIEZST00gZ2VuZXJh
dGVfc2VyaWVzKDEsIDUwMDAwMDAwKSBnKGkpCiAgT1JERVIgQlkgKGcuaSAvIDMyKSArIChyYW5k
b20oKSAqIDQgLSAyKTo6aW50Cikgc3ViOwoKLS0gQ3JlYXRlIGluZGV4ZXMKQ1JFQVRFIElOREVY
IHByZWZldGNoX29yZGVyc19jdXN0X2RhdGVfaWR4CiAgT04gcHJlZmV0Y2hfb3JkZXJzKGN1c3Rv
bWVyX2lkLCBvcmRlcl9kYXRlKQogIFdJVEggKGRlZHVwbGljYXRlX2l0ZW1zPW9mZik7CgpDUkVB
VEUgSU5ERVggcHJlZmV0Y2hfb3JkZXJzX2RhdGVfaWR4CiAgT04gcHJlZmV0Y2hfb3JkZXJzKG9y
ZGVyX2RhdGUpCiAgV0lUSCAoZGVkdXBsaWNhdGVfaXRlbXM9b2ZmKTsKCkNSRUFURSBJTkRFWCBw
cmVmZXRjaF9vcmRlcnNfcHJvZF9pZHgKICBPTiBwcmVmZXRjaF9vcmRlcnMocHJvZHVjdF9pZCkK
ICBXSVRIIChkZWR1cGxpY2F0ZV9pdGVtcz1vZmYpOwoKQ1JFQVRFIElOREVYIHByZWZldGNoX29y
ZGVyc19pZF9pZHgKICBPTiBwcmVmZXRjaF9vcmRlcnMob3JkZXJfaWQpCiAgV0lUSCAoZGVkdXBs
aWNhdGVfaXRlbXM9b2ZmKTsKCi0tIFZBQ1VVTSBGUkVFWkUgQU5BTFlaRQpWQUNVVU0gRlJFRVpF
IEFOQUxZWkUgcHJlZmV0Y2hfb3JkZXJzOwpWQUNVVU0gRlJFRVpFIEFOQUxZWkUgcHJlZmV0Y2hf
Y3VzdG9tZXJzOwpWQUNVVU0gRlJFRVpFIEFOQUxZWkUgcHJlZmV0Y2hfcHJvZHVjdHM7CgotLSBB
ZHZlcnNhcmlhbCB0YWJsZTogc2VxdWVudGlhbCBoZWFwIGFjY2VzcwpDUkVBVEUgVU5MT0dHRUQg
VEFCTEUgcHJlZmV0Y2hfc2VxdWVudGlhbCAoCiAgaWQgYmlnaW50LAogIHZhbDEgaW50LAogIHZh
bDIgdGV4dAopOwpJTlNFUlQgSU5UTyBwcmVmZXRjaF9zZXF1ZW50aWFsClNFTEVDVCBpLCBpICUg
MTAwMCwgJ3ZhbHVlXycgfHwgaQpGUk9NIGdlbmVyYXRlX3NlcmllcygxLCA1MDAwMDApIGk7CkNS
RUFURSBJTkRFWCBwcmVmZXRjaF9zZXF1ZW50aWFsX2lkeCBPTiBwcmVmZXRjaF9zZXF1ZW50aWFs
KGlkKTsKVkFDVVVNIEFOQUxZWkUgcHJlZmV0Y2hfc2VxdWVudGlhbDsKCi0tIEFkdmVyc2FyaWFs
IHRhYmxlOiBzcGFyc2UgKDEgVElEIHBlciBibG9jaykKQ1JFQVRFIFVOTE9HR0VEIFRBQkxFIHBy
ZWZldGNoX3NwYXJzZSAoCiAgaWQgYmlnaW50LAogIGNhdGVnb3J5IGludCwKICBwYWRkaW5nIHRl
eHQKKTsKQUxURVIgVEFCTEUgcHJlZmV0Y2hfc3BhcnNlIEFMVEVSIENPTFVNTiBwYWRkaW5nIFNF
VCBTVE9SQUdFIHBsYWluOwpJTlNFUlQgSU5UTyBwcmVmZXRjaF9zcGFyc2UKU0VMRUNUIGksIChp
ICUgNTApICsgMSwgcmVwZWF0KCd4JywgNDAwMCkKRlJPTSBnZW5lcmF0ZV9zZXJpZXMoMSwgNTAw
MDApIGkKT1JERVIgQlkgcmFuZG9tKCk7CkNSRUFURSBJTkRFWCBwcmVmZXRjaF9zcGFyc2VfY2F0
X2lkeCBPTiBwcmVmZXRjaF9zcGFyc2UoY2F0ZWdvcnkpOwpWQUNVVU0gQU5BTFlaRSBwcmVmZXRj
aF9zcGFyc2U7CgpDSEVDS1BPSU5UOwo=
--00000000000074b58d064b0abf03--