Re: index prefetching - Mailing list pgsql-hackers

From Thomas Munro
Subject Re: index prefetching
Date
Msg-id CA+hUKGL2PhFyDoqrHefqasOnaXhSg48t1phs3VM8BAdrZqKZkw@mail.gmail.com
Whole thread Raw
In response to Re: index prefetching  (Tomas Vondra <tomas@vondra.me>)
List pgsql-hackers
On Wed, Jul 23, 2025 at 1:55 AM Tomas Vondra <tomas@vondra.me> wrote:
> On 7/21/25 14:39, Thomas Munro wrote:
> > Here also are some alternative experimental patches for preserving
> > accumulated look-ahead distance better in cases like that.  Needs more
> > exploration... thoughts/ideas welcome...
>
> Thanks! I'll rerun the tests with these patches once the current round
> of tests (with the simple distance restore after a reset) completes.

Here's C, a tider expression of the policy from the B patch.

Also, I realised that the quickly-drafted A patch didn't actually
implement what Andres suggested in the other thread as I had intended,
what he actually speculated about is distance * 2 + nblocks.

But it doesn't seem to matter much: anything you come up with along
those lines seems to suffer from the problem that you can easily
produce a test that defeats it by inserting just one more hit in
between the misses, where the numbers involved can be quite small.
The only policy I've come up with so far that doesn't give up until we
definitely can't do better is the one that tracks a hypothetical
window of the largest distance we possibly could have, and refuses to
shrink the actual window until even the maximum wouldn't be enough, as
expressed in the B and C patches.

On the flip side, that degree of pessimism has a cost: of course it
takes much longer to come back to distance = 1 and perhaps the fast
path.  Does it matter?  I don't know.

(It's only a hunch at this point but I think I can see a potentially
better way to derive that sustain value from information available
with another in-development patch that adds a new io_currency_target
value, using IO subsystem feedback to compute the IO concurrency level
that avoids I/O stalls but not more instead of going all the way to
the GUC limits and making it the user's problem to set them sensibly.
I'll have to look into that properly, but I think it might be able to
produce an ideal sustain value...)

Attachment

pgsql-hackers by date:

Previous
From: Tomas Vondra
Date:
Subject: Re: index prefetching
Next
From: Peter Geoghegan
Date:
Subject: Re: index prefetching