MIME-Version: 1.0
References: 
 <CAH2-Wzn7vqmt=qE_hDrOx4NETkUoCbdn74G1gswMXi1APUuYrA@mail.gmail.com>
 <b8ab7410-0b96-495f-88e6-6b41db39d290@vondra.me>
 <CAH2-Wz==zkbOumyX-M4quqbX9GCLcjW_zXmdsaK37q-55rj_fQ@mail.gmail.com>
 <dc4c7ace-f3c4-4325-a63c-b1f9aa5541ba@vondra.me>
 <CAH2-Wz=G_HKYZ34-UkBMQjk7SCNtu9O5V4t=A0u82=WT-rtBuw@mail.gmail.com>
 <CA+TgmoaEvBnx2npWycGt1ChTe8m800wMiioUCspaSb0qzc3=Kg@mail.gmail.com>
 <CAH2-WznFwgU3AddTqnvJABX5xo-9upG6NiX+2s0eaFhFj6tRAg@mail.gmail.com>
 <CA+Tgmobav+-oR9-jJUGbHj3j7bhwPpz7qVkfr_9zUSF-kens9A@mail.gmail.com>
 <CAH2-WzkqnVGLEQ31W1vm8T_uzy-ma-6A8QL-C56=0QUqs12b=Q@mail.gmail.com>
 <CAH2-WznmrgwFShyKuKjF2v7M_Eid6VzKd+SRPjh4-y68T6uCDw@mail.gmail.com>
 <esftck6ayqkkdtzijd736oazhve577sp7hthnwouyg2stlwlqj@rmhohbhl7tuz>
 <57d0e292-73d5-4ab9-9855-110ee9cbd90a@vondra.me>
 <efac3238-6f34-41ea-a393-26cc0441b506@vondra.me>
 <32c15a30-6e25-4f6d-9191-76a19482c556@vondra.me>
 <CAH2-WzmVvU2KmKyq8sUeYXrc_roA5LfOgDE1-vdtorpk_M3DfA@mail.gmail.com>
 <ec4d8c7b-bbc7-4805-919f-8fdeb11b3d34@vondra.me>
 <64c8b824-6203-46a3-b045-5e95b796feee@vondra.me>
In-Reply-To: <64c8b824-6203-46a3-b045-5e95b796feee@vondra.me>
From: Peter Geoghegan <pg@bowt.ie>
Date: Wed, 16 Jul 2025 09:36:48 -0400
Message-ID: 
 <CAH2-Wzm-u6b4gDbLNP=1pkfqJbEyPyey9M-8wG0C+QOTit963Q@mail.gmail.com>
Subject: Re: index prefetching
To: Tomas Vondra <tomas@vondra.me>
Cc: Andres Freund <andres@anarazel.de>, Robert Haas <robertmhaas@gmail.com>,
	Melanie Plageman <melanieplageman@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Georgios <gkokolatos@protonmail.com>,
	Thomas Munro <thomas.munro@gmail.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-Wzm-u6b4gDbLNP%3D1pkfqJbEyPyey9M-8wG0C%2BQOTit963Q%40mail.gmail.com>
Precedence: bulk

On Wed, Jul 16, 2025 at 4:40=E2=80=AFAM Tomas Vondra <tomas@vondra.me> wrot=
e:
> But the thing I don't really understand it the "cyclic" dataset (for
> example). And the "simple" patch performs really badly here. This data
> set is designed to not work for prefetching, it's pretty much an
> adversary case. There's ~100 TIDs from 100 pages for each key value, and
> once you read the 100 pages you'll hit them many times for following
> values. Prefetching is pointless, and skipping duplicate blocks can't
> help, because the blocks are not effective.
>
> But how come the "complex" patch does so much better? It can't really
> benefit from prefetching TID from the next leaf - not this much. Yet it
> does a bit better than master. I'm looking at this since yesterday, and
> it makes no sense to me. Per "perf trace" it actually does 2x many
> fadvise calls compared to the "simple" patch (which is strange on it's
> own, I think), yet it's apparently so much faster?

The "simple" patch has _bt_readpage reset the read stream. That
doesn't make any sense to me. Though it does explain why the "complex"
patch does so many more fadvise calls.

Another issue with the "simple" patch: it adds 2 bool fields to
"BTScanPosItem". That increases its size considerably. We're very
sensitive to the size of this struct (I think that you know about this
already). Bloating it like this will blow up our memory usage, since
right now we allocate MaxTIDsPerBTreePage/1358 such structs for
so->currPos (and so->markPos). Wasting all that memory on alignment
padding is probably going to have consequences beyond memory bloat.

--=20
Peter Geoghegan