Re: index prefetching

On Fri, Feb 27, 2026 at 7:37 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> I do continue to wonder if we ought to pass down some hints from the planner
> about how much data an indexscan is likely to read to influence readahead
> aggressiveness.

As you know, I already have some of that, but it isn't particularly
well developed. I agree that pushing the hinting mechanism further and
using something like your proposed READ_STREAM_SLOW_START mechanism
(which you mention in your later email from today) seems most
promising.

> I do agree it's right beign concerned about the increase in index fetches with
> such mark/restore cases.

> Is this, by any chance, with starting the server and running these queries in
> that order? Are you repeating these runs within one server start, evicting
> the buffers inbetween?

Yes, I repeat the runs 3 times, evicting/prewarming as needed each
time. I go with the fastest run.

> Are you using huge pages? I see rather differing performance results
> with/without when not prefetching.

Yes, I always use huge_pages (and never use transparent huge pages).

> It really shouldn't be sensitive - that query will never be able to reuse
> heapam pages within a query, and evicting a clean buffer isn't that expensive.

Could have been my previous failure to use "cpupower idle-set -D 0" to
get maximally stable performance for my microbenchmarks. As discussed
privately, I've added this as yet another step in the script that
automates all this for me/that prepares my system to run a
microbenchmark.

--
Peter Geoghegan