MIME-Version: 1.0
References: 
 <CAH2-Wzko86NwiENCJGtakJ=fOhWpr-Yz-F+1oxgv2Ku1mvXwvA@mail.gmail.com>
 <6cb6109d-71d6-490c-8056-d8885081b008@vondra.me>
 <c7a77pcyc5eynme376wvyojryijtlieyxsu3bvxp4eiy6au6uf@caniulyi4jr5>
 <CAH2-Wz=L7h-koDKa3_NEg39Faw7MrOkSVOsodvQ4toSQahvWjQ@mail.gmail.com>
 <a9c0a5f9-cf5e-4ac9-abf6-683483161169@vondra.me>
 <DC1PBQW5HBEJ.3W4SMGI4LW3VI@bowt.ie>
 <7c308720-5cd4-4508-8cc0-6e55a6352731@vondra.me>
 <DC2B4TY023QP.2ZL331BPZCBSX@bowt.ie>
 <CAH2-WzmuGzTH-62EWTgQ4F66XEBJtJk25psF4GDuAGqeC4a34g@mail.gmail.com>
 <DC2DFSYXBDSJ.2N7WXLD4JT05H@bowt.ie>
 <6wyxbnry2unm3kbcu2sabhzhs7baoedlg77xqm42chpofjq45g@igst42zpl7ok>
 <CAH2-WzntgDeopLJpyEbUh23Qr1vgoYv5jbFkYsymTScEKxBj7A@mail.gmail.com>
In-Reply-To: 
 <CAH2-WzntgDeopLJpyEbUh23Qr1vgoYv5jbFkYsymTScEKxBj7A@mail.gmail.com>
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 14 Aug 2025 15:45:26 -0400
Message-ID: 
 <CAH2-WzkaTHg2X9R-gLRNBEoL82t2mkrQq-3f=y3GAzrj40fFZw@mail.gmail.com>
Subject: Re: index prefetching
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Thomas Munro <thomas.munro@gmail.com>,
	Nazir Bilal Yavuz <byavuz81@gmail.com>, Robert Haas <robertmhaas@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Georgios <gkokolatos@protonmail.com>,
	Konstantin Knizhnik <knizhnik@garret.ru>, Dilip Kumar <dilipbalaut@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAH2-WzkaTHg2X9R-gLRNBEoL82t2mkrQq-3f%3Dy3GAzrj40fFZw%40mail.gmail.com>
Precedence: bulk

On Thu, Aug 14, 2025 at 3:15=E2=80=AFPM Peter Geoghegan <pg@bowt.ie> wrote:
> Then why does the exact same pair of runs show "I/O Timings: shared
> read=3D194.629" for the sequential table backwards scan (with total
> execution time 1132.360 ms), versus "I/O Timings: shared read=3D352.88"
> (with total execution time 697.681 ms) for the random table backwards
> scan?

If you're interested in trying this out for yourself, I've pushed my
working branch here:

https://github.com/petergeoghegan/postgres/tree/index-prefetch-batch-v1.2

Note that the test case you'll run is added by the most recent commit:

https://github.com/petergeoghegan/postgres/commit/c9ceb765f3b138f53b7f1fdf4=
94ba7c816082aa1

Run microbenchmarks/random_backwards_weird.sql to do an initial load
of both of the tables. Then run
microbenchmarks/queries_random_backwards_weird.sql to actually run the
relevant queries. There are 4 such queries, but only the 2 backwards
scan queries really seem relevant.

--=20
Peter Geoghegan