Re: index prefetching

On Thu, Aug 14, 2025 at 12:56 PM Peter Geoghegan <pg@bowt.ie> wrote:
> I performed the usual procedure of prewarming the index and evicting the heap
> relation, and then actually running the relevant query through EXPLAIN
> ANALYZE. Direct I/O was used throughout.

> io_method=io_uring
> ------------------
>
> Original backwards scan: 1052.807 ms (shared read=187.876)
> "No heap correlation" backwards scan: 649.473 ms (shared read=365.802)

Attached is a differential flame graph that compares the execution of
these 2 queries in terms of the default perf event (which is "cycles",
per the generic recipe for making one of these put out by Brendan
Gregg). The actual query runtime for each query was very similar to
what I report here -- the backwards scan is a little under twice as
fast.

The only interesting thing about the flame graph is just how little
difference there seems to be (at least for this particular perf event
type). The only thing that stands out even a little bit is the 8.33%
extra time spent in pg_checksum_page for the "No heap
correlation"/random query. But that's entirely to be expected: we're
reading 49933 pages with the sequential backwards scan query, whereas
the random one must read 77813 pages.

--
Peter Geoghegan