MIME-Version: 1.0
References: <CAE8JnxN_EwnTLLMWGhvgwaomYZ0ysm7NeogA-BqBd=Rs3S7Oqw@mail.gmail.com>
 <64a2re223ajj4popowsyu4xekbuvvyfwkrihn5yzyrkwsmsuvp@2lls3tpww5dl>
 <a67mvhyi2q45eg4eimhpwdg6l3s3dmpahti2svffvmvzwmss27@r4nohusvndbq>
 <c19a18fc-ef2e-4d91-b7ba-576d1891315d@vondra.me> <il7jtfowpatrlg33qb5plj7v7pferes4ogerq5fdczszi4kokh@sbwvb2ukfgos>
 <52512325-b1f2-4fff-819e-f68122b2e427@vondra.me> <ws47e3wly6skt36b23zy5qfvcxzueo6od3uicunuodsqnxl7os@7v2qi7qkxzbz>
 <CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=D_voWxZDg@mail.gmail.com>
 <64mfcfv7iihc4pmqlxarii4esnmqry52ckz5m7lmwylnfnuxuz@oxh4ioxkjtep>
 <CAH2-Wzmy7NMba9k8m_VZ-XNDZJEUQBU8TeLEeL960-rAKb-+tQ@mail.gmail.com> <issqornf6vdn3vb64fjuoathypmu3e5pgputd3lpfuvoeqyvzr@qfordnhplp2v>
In-Reply-To: <issqornf6vdn3vb64fjuoathypmu3e5pgputd3lpfuvoeqyvzr@qfordnhplp2v>
From: Peter Geoghegan <pg@bowt.ie>
Date: Fri, 27 Feb 2026 15:11:24 -0500
Message-ID: <CAH2-WzkEZiRL5wedTTnB0uKZFkm52BPJO6wZugoik95amtz4DQ@mail.gmail.com>
Subject: Re: index prefetching
To: Andres Freund <andres@anarazel.de>
Cc: Tomas Vondra <tomas@vondra.me>, Alexandre Felipe <o.alexandre.felipe@gmail.com>, 
	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-WzkEZiRL5wedTTnB0uKZFkm52BPJO6wZugoik95amtz4DQ%40mail.gmail.com>
Precedence: bulk

On Thu, Feb 26, 2026 at 11:18=E2=80=AFPM Andres Freund <andres@anarazel.de>=
 wrote:
> Note how the increase in scanned heap pages actually *decreases* the over=
all
> time rather substantially.
>
> It's quite visible, both in iostat, and a query like
>   SELECT pid, target_desc, off, length FROM pg_aios \watch 0.5
>
> that for the first query has basically no IO concurrency, the second has =
very
> intermittent IO concurrency and the third one has nice IO concurrency.
>
>
> If I disable the yield logic, the fillfactor=3D90 case is good:

I can recreate your results. Including the part where you found that
the problem would go away once yields were completely disabled.

I can certainly understand why you're suspicious of the yielding
mechanism. I wonder if I gave undue weight to the merge join query I
showed you [1] (and one or two others like it). Declaring that the
underlying merge join/yielding issue is not worth the complexity
required to yield would certainly be convenient. Yielding *isn't*
helpful for the vast majority of individual queries, so I'm certainly
tempted. But I can't help but feel nervous about the large disparity
in the number of *index* pages read by that particular query, once the
yielding mechanism is disabled.

Just in case there's any doubt: I'm flexible about whether a yielding
mechanism is needed and how it should work. Ideally we can come up
with a design that gives us the best of all possible worlds -- but
everything is on the table. It's not that I'm attached to the idea of
yielding; I'm just nervous about one or two funny looking cases [1].

With that being said, it seems as if yielding isn't the only factor in
play here. I also notice that even master exhibits roughly the same
performance disparity (also while using direct I/O, though with
shared_buffers set to 16GB rather than your 2GB):

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D
EXPLAIN OUTPUT (best run, master)
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D

--- Fillfactor 90 ---
  Index Scan using pgbench_accounts_ff90_pkey on pgbench_accounts_ff90
    Index Searches: 1
    Buffers: shared hit=3D27325 read=3D181819
    I/O Timings: shared read=3D16822.256
  Planning Time: 0.035 ms
  Execution Time: 18048.198 ms

--- Fillfactor 50 ---
  Index Scan using pgbench_accounts_ff50_pkey on pgbench_accounts_ff50
    Index Searches: 1
    Buffers: shared hit=3D27325 read=3D333334
    I/O Timings: shared read=3D30685.965
  Planning Time: 0.028 ms
  Execution Time: 32005.962 ms

--- Fillfactor 25 ---
  Index Scan using pgbench_accounts_ff25_pkey on pgbench_accounts_ff25
    Index Searches: 1
    Buffers: shared hit=3D27325 read=3D666667
    I/O Timings: shared read=3D10278.124
  Planning Time: 0.034 ms
  Execution Time: 11796.573 ms

While fillfactor 90 is fastest, fillfactor 25 is almost 3x faster than
fillfactor 50, despite performing about twice as many reads. I have to
imagine this relates to my Samsung 980 Pro SSD performing its own
read-ahead, in a way that works inconsistently across workloads.

Note again that this effect with master only appears when
shared_buffers is set to 16GB. With your 2GB shared_buffers setting,
master takes 17930.381 ms for FF 90, 31822.473 ms for FF 50, and
61094.676 ms for FF 25 (which is at least consistent-ish in the way
that one would expect).

For context, here is how the patch compares to master with
shared_buffers=3D16GB (here master uses the same query execution/query
plans as those shown above) once the patch/Pfetch's yielding is
disabled:

  FF  Heap Pages      Master   Pfetch ON   ON/Master
----------------------------------------------------
  90      181819     18048.2      1465.0       0.081x
  50      333334     32006.0      1682.2       0.053x
  25      666667     11796.6      1928.4       0.163x

I also noticed that the patch isn't at all sensitive to whether
shared_buffers is set to 2GB or 16GB -- not once yielding is disabled
like this. Obviously that insensitivity is desirable, which argues for
removing yielding.

For context, with standard v11/with yielding enabled, the "2GB vs
16GB" matters to quite a surprising degree:

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Patch + yielding, 2GB shared_buffers
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

  FF  Heap Pages   Pfetch ON
------------------------------
  90      181819      4276.5
  50      333334      1523.3
  25      666667      6805.7

=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D
Patch + yielding, 16GB shared_buffers
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

  FF  Heap Pages   Pfetch ON
------------------------------
  90      181819      4384.6
  50      333334      1683.2
  25      666667      2002.0

Notice that with 16GB shared_buffers, the perverse effect from
yielding is even more pronounced!

I'm not sure how relevant this later point about "shared_buffers
sensitivity with yielding" really is. Nor am I sure if the effect with
master (and the possible role of device-level readahead) is all that
significant. I'm pointing all of this out in the hope that you can
offer an explanation that'll help me to improve my own intutions about
this stuff.

[1] https://postgr.es/m/CAH2-Wzk-89uCvdJ1Q6NsM6LvDvUEt6Qy66T6A60J=3DD_voWxZ=
Dg@mail.gmail.com
--
Peter Geoghegan