MIME-Version: 1.0
References: 
 <CAP53Pkx0=ph0vG_M20yVAoK11yGSTZP=53-rZt36OCP4hBPaDQ@mail.gmail.com>
 <CAD21AoCKz0t06-qOOm_7kgMn=Niu0yYB3R5jhOY0oVb=5AWytA@mail.gmail.com>
 <CAExHW5uqQuPNEjj2ondemCYdtFHExm=jvw-4naPn7gdMmkhrgw@mail.gmail.com>
 <CAP53PkyK65PM-Ws7ctj5-eKqVxHo5FzoWKW7=XPq5Jc1qZCJNg@mail.gmail.com>
 <CAD21AoCzv5q89VDZ=Ot1wWguBO0MGdCc0-NXN83-Jg57uiJN1Q@mail.gmail.com>
 <CAExHW5tynt3Vmmhvvk929Ljfz6jTaJWWoRDthhV=e13GvYRSqQ@mail.gmail.com>
 <670b867e-6518-48b0-b7c2-bd5f10ab58c6@vondra.me>
 <CAExHW5sMsaz1j+hrdhyo-DJp7JCgJx87=q2iJfOc_9mwYWyvmw@mail.gmail.com>
 <7ab3914f-da59-4c1f-b809-225637b586e8@iki.fi>
 <bdljrq3k5wm2jzxntpbenzbyozjx4syf43taodgxasc6qbqooo@nvnstsdlhboe>
 <f64f741f-36db-4b5b-b7ff-ee86af65e3e6@iki.fi>
In-Reply-To: <f64f741f-36db-4b5b-b7ff-ee86af65e3e6@iki.fi>
From: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>
Date: Wed, 8 Apr 2026 12:06:32 +0530
Message-ID: 
 <CAExHW5sXoaMTVYDmfVT2AgUWj7vu9sS-M2-b3Pa8K5dQr=zNdg@mail.gmail.com>
Subject: Re: pg_buffercache: Add per-relation summary stats
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: Andres Freund <andres@anarazel.de>, Tomas Vondra <tomas@vondra.me>,
 chaturvedipalak1911@gmail.com,
	Masahiko Sawada <sawada.mshk@gmail.com>, Lukas Fittl <lukas@fittl.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
	Paul A Jungwirth <pj@illuminatedcomputing.com>,
 Khoa Nguyen <khoaduynguyen@gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: 
 <https://www.postgresql.org/message-id/CAExHW5sXoaMTVYDmfVT2AgUWj7vu9sS-M2-b3Pa8K5dQr%3DzNdg%40mail.gmail.com>
Precedence: bulk

On Tue, Apr 7, 2026 at 9:25=E2=80=AFPM Heikki Linnakangas <hlinnaka@iki.fi>=
 wrote:
>
> On 07/04/2026 16:47, Andres Freund wrote:
> > On 2026-04-07 16:07:45 +0300, Heikki Linnakangas wrote:
> >> On 28/03/2026 06:18, Ashutosh Bapat wrote:
> >>> Parallely myself and Palak Chaturvedi developed a quick patch to
> >>> modernise pg_buffercache_pages() and use tuplestore so that it doesn'=
t
> >>> have to rely on NBuffers being the same between start of the scan,
> >>> when memory allocated, when the scan ends - a condition possible with
> >>> resizing buffer cache. It seems to improve the timings by about 10-30=
%
> >>> on my laptop for 128MB buffercache size. Without this patch the time
> >>> taken to execute Lukas's query varies between 10-15ms on my laptop.
> >>> With this patch it varies between 8-9ms. So the timing is more stable
> >>> as a side effect. It's not a 10x improvement that we are looking for
> >>> but it looks like a step in the right direction. That improvement
> >>> seems to come purely because we avoid creating a heap tuple. I wonder
> >>> if there are some places up in the execution tree where full
> >>> heaptuples get formed again instead of continuing to use minimal
> >>> tuples or places where we perform some extra actions that are not
> >>> required.
> >
> > I don't think that's the reason for the improvement - tuplestore_putval=
ues()
> > forms a minimal tuple, and the cost to form a minimal tuple and a heap =
tuple
> > aren't meaningfully different.
>
> Yeah, I wasn't fully convinced of that part either, which is why I left
> it out of the commit message. I mostly wanted to get rid of the
> double-buffering where we first accumulated all the data in an array.
>

Just because of the name of the function, I thought
tuplestore_puttuple stores virtual tuple. But looking at the function
it's clearly using minimal tuples. Sorry for my misunderstanding.

> > I think the problem is that we materialize rowmode SRFs as a tuplestore=
 if
> > they are in the from list.  You can easily see this even with just
> > generate_series():
> >
> > postgres[1520825][1]=3D# SELECT count(*) FROM generate_series(1, 100000=
0);
> > =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=90
> > =E2=94=82  count  =E2=94=82
> > =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=A4
> > =E2=94=82 1000000 =E2=94=82
> > =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=98
> > (1 row)
> >
> > Time: 117.939 ms
> > postgres[1520825][1]=3D# SELECT count(*) FROM (SELECT generate_series(1=
, 1000000));
> > =E2=94=8C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=90
> > =E2=94=82  count  =E2=94=82
> > =E2=94=9C=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=A4
> > =E2=94=82 1000000 =E2=94=82
> > =E2=94=94=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=80=E2=94=
=80=E2=94=80=E2=94=80=E2=94=98
> > (1 row)
> >
> > Time: 58.914 ms
>
> Oh, to be honest I didn't remember that we *don't* materialize when it's
> in the target list.
>

IIUC, query using SRF in targetlist runs faster because it does "not"
use tuplestore. I consistently saw 10%-30% performance improvement
after using tuplestore in pg_buffercache_pages(). Is that purely
because of avoiding an in-memory array?

--
Best Wishes,
Ashutosh Bapat