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>
In-Reply-To: <6wyxbnry2unm3kbcu2sabhzhs7baoedlg77xqm42chpofjq45g@igst42zpl7ok>
From: Peter Geoghegan <pg@bowt.ie>
Date: Thu, 14 Aug 2025 15:15:02 -0400
Message-ID: 
 <CAH2-WzntgDeopLJpyEbUh23Qr1vgoYv5jbFkYsymTScEKxBj7A@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-WzntgDeopLJpyEbUh23Qr1vgoYv5jbFkYsymTScEKxBj7A%40mail.gmail.com>
Precedence: bulk

On Thu, Aug 14, 2025 at 2:53=E2=80=AFPM Andres Freund <andres@anarazel.de> =
wrote:
> I think this is just an indicator of being IO bound.

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?

Obviously it is hard to believe that the query with shared
read=3D194.629 is one that is naturally much more I/O bound than another
similar query that shows shared read=3D352.88. What "I/O Timings" shows
more or less makes sense to me already -- it just doesn't begin to
explain why *overall query execution* is much slower when scanning
backwards sequentially.

>   I'd see what changes if you temporarily reduce
>   /sys/block/nvme6n1/queue/max_sectors_kb to a smaller size.

I reduced max_sectors_kb from 128 to 8. That had no significant effect.

> Could you show iostat for both cases?

iostat has lots of options. Can you be more specific?

--=20
Peter Geoghegan