Message-ID: <7ab3914f-da59-4c1f-b809-225637b586e8@iki.fi>
Date: Tue, 7 Apr 2026 16:07:45 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pg_buffercache: Add per-relation summary stats
To: Ashutosh Bapat <ashutosh.bapat.oss@gmail.com>,
 Tomas Vondra <tomas@vondra.me>, chaturvedipalak1911@gmail.com
Cc: 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>
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>
Content-Language: en-US
From: Heikki Linnakangas <hlinnaka@iki.fi>
In-Reply-To: 
 <CAExHW5sMsaz1j+hrdhyo-DJp7JCgJx87=q2iJfOc_9mwYWyvmw@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/7ab3914f-da59-4c1f-b809-225637b586e8%40iki.fi>
Precedence: bulk

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 didn't dig into the history to find out why we didn't modernize
> pg_buffercache_pages(). I don't see any hazard though.

Committed this modernization patch, thanks!

It would be nice to have a proper row-at-a-time mode that would avoid 
materializing the result, but collecting all the data in a temporary 
array is clearly worse than just putting them to the tuplestore 
directly. The only reason I can think of why we'd prefer to use a 
temporary array like that is to get a more consistent snapshot of all 
the buffers, by keeping the time spent scanning the buffers as short as 
possible. But we're not getting a consistent view anyway, it's just a 
matter of degree.

I wondered about this in pg_buffercache_pages.c:

> 	/*
> 	 * To smoothly support upgrades from version 1.0 of this extension
> 	 * transparently handle the (non-)existence of the pinning_backends
> 	 * column. We unfortunately have to get the result type for that... - we
> 	 * can't use the result type determined by the function definition without
> 	 * potentially crashing when somebody uses the old (or even wrong)
> 	 * function definition though.
> 	 */
> 	if (get_call_result_type(fcinfo, NULL, &expected_tupledesc) != TYPEFUNC_COMPOSITE)
> 		elog(ERROR, "return type must be a row type");
> 
> 	if (expected_tupledesc->natts < NUM_BUFFERCACHE_PAGES_MIN_ELEM ||
> 		expected_tupledesc->natts > NUM_BUFFERCACHE_PAGES_ELEM)
> 		elog(ERROR, "incorrect number of output arguments");

I guess it's still needed, if you have pg_upgraded all the way from 1.0. 
To test that, I created this view to match the old 1.0 definition:

CREATE VIEW public.legacy_pg_buffercache AS
  SELECT bufferid,
     relfilenode,
     reltablespace,
     reldatabase,
     relforknumber,
     relblocknumber,
     isdirty,
     usagecount
    FROM public.pg_buffercache_pages() p(bufferid integer, relfilenode 
oid, reltablespace oid, reldatabase oid, relforknumber smallint, 
relblocknumber bigint, isdirty boolean, usagecount smallint);

"select * from public.legacy_pg_buffercache" still works, so all good.

- Heikki