public inbox for [email protected]
help / color / mirror / Atom feedFrom: Peter Geoghegan <[email protected]>
To: Tomas Vondra <[email protected]>
To: Andres Freund <[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: Thu, 14 Aug 2025 13:57:42 -0400
Message-ID: <CAH2-WzmuGzTH-62EWTgQ4F66XEBJtJk25psF4GDuAGqeC4a34g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<CAH2-WzmdiO4fHA1O06SYUjgMQZG7haysY7Tu5DS5z-CHsv5MLQ@mail.gmail.com>
<[email protected]>
<CAH2-Wz=Y-PsC6_tZOPhHWvPx0geGnrh9VKjUZ-168ezUM_XM2Q@mail.gmail.com>
<CA+hUKGKMaZLmNQHaa_DZMw9MJJKGegjrqnTY3KOZB-_nvFa3wQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<CAH2-Wzko86NwiENCJGtakJ=fOhWpr-Yz-F+1oxgv2Ku1mvXwvA@mail.gmail.com>
<[email protected]>
<c7a77pcyc5eynme376wvyojryijtlieyxsu3bvxp4eiy6au6uf@caniulyi4jr5>
<CAH2-Wz=L7h-koDKa3_NEg39Faw7MrOkSVOsodvQ4toSQahvWjQ@mail.gmail.com>
<[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
On Thu, Aug 14, 2025 at 12:56 PM Peter Geoghegan <[email protected]> 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
Attachments:
[image/svg+xml] sequential_vs_random.svg (107.4K, 2-sequential_vs_random.svg)
download | view image
view thread (348+ 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]
Subject: Re: index prefetching
In-Reply-To: <CAH2-WzmuGzTH-62EWTgQ4F66XEBJtJk25psF4GDuAGqeC4a34g@mail.gmail.com>
* 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