public inbox for [email protected]  

help / color / mirror / Atom feed

From: Andres Freund <[email protected]>
To: Peter Geoghegan <[email protected]>
Cc: Tomas Vondra <[email protected]>
Cc: Alexandre Felipe <[email protected]>
Cc: Thomas Munro <[email protected]>
Cc: Nazir Bilal Yavuz <[email protected]>
Cc: Robert Haas <[email protected]>
Cc: Melanie Plageman <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Georgios <[email protected]>
Cc: Konstantin Knizhnik <[email protected]>
Cc: Dilip Kumar <[email protected]>
Subject: Re: index prefetching
Date: Tue, 17 Feb 2026 14:27:56 -0500
Message-ID: <64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep> (raw)
In-Reply-To: <CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com>
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>
	<[email protected]>
	<il7jtfowpatrlg33qb5plj7v7pferes4ogerq5fdczszi4kokh@sbwvb2ukfgos>
	<[email protected]>
	<ws47e3wly6skt36b23zy5qfvcxzueo6od3uicunuodsqnxl7os@7v2qi7qkxzbz>
	<CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com>

Hi,

On 2026-02-17 12:16:23 -0500, Peter Geoghegan wrote:
> On Mon, Feb 16, 2026 at 11:48 AM Andres Freund <[email protected]> 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 quite
valuable to use dm_delay to have a disk with reproducible (i.e. not cloud)
higher latency (i.e. not just a local SSD).  Low latency NVMe can reduce the
penalty of not enough readahead so much that it's hard to spot problems...


> > Note that there is pretty much *no* readhead, because the yields happen more
> > 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.

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.

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 number
of already "consumed" batches into account).


To evaluate the amount of wasted work, it could be useful to make the read
stream stats page spit out the amount of "unconsumed" IOs at the end of the
scan.



> > 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.

Would be good to document why the yielding exists more extensively in the
comment above it...


> 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.


I assume that there are no mark & restores in the query, given that presumably
the inner side is unique?


Greetings,

Andres Freund

view thread (87+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: index prefetching
  In-Reply-To: <64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox