Message-ID: <8b2525bcc0c714603cc4f44a686b8b0e4659ac92.camel@cybertec.at>
Subject: Re: shared buffers
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Marc Millas <marc.millas@mokadb.com>, 
 "pgsql-general@lists.postgresql.org"
	 <pgsql-general@lists.postgresql.org>
Date: Sat, 26 Apr 2025 00:46:15 +0200
In-Reply-To: <CADX_1abjKJhk10vWz6S=cwgXsppGXw2VjNnLVByE3EMJzLzD4w@mail.gmail.com>
References: 
	<CADX_1abjKJhk10vWz6S=cwgXsppGXw2VjNnLVByE3EMJzLzD4w@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.54.3 (3.54.3-1.fc41) 
MIME-Version: 1.0
Archived-At: <https://www.postgresql.org/message-id/8b2525bcc0c714603cc4f44a686b8b0e4659ac92.camel%40cybertec.at>
Precedence: bulk

On Fri, 2025-04-25 at 15:42 +0200, Marc Millas wrote:
> got something strange to me:
> Same db ie. same data, around 1.2TB,one on pg13, one on pg16
> same 16 GB of shared_buffers,
> I am the single user.
> both have track_io_timing on
>=20
> on pg13, if I run a big request with explain (analyze,buffers),=C2=A0
> I see around 6 GB read
> if I do rerun the very same request, no more read(s), all data in the sha=
red buffers cache. fine
> If I check with pg_buffercache what's in it, I see the biggest tables of =
my request within
> the biggest users (in number of blocks used). All this is fine.
>=20
> next, if I do the very same on the pg16 machine, whatever the number of t=
imes I rerun the
> explain=C2=A0(analyze, buffers) of the same request, each time, the expla=
in shows the same volume
> of reads. again and again.
> If I check with pg_buffercache, the set of objects stay the same, WITHOUT=
 the objects of my
> request, just like if those objects where sticky.

I can't see the plans, so I can only guess.

Perhaps the v16 plan uses a sequential scan on a table that is more than a =
quarter of
shared_buffers in size, so that PostgreSQL uses a ring buffer to read it in=
stead of
blowing out more than a quarter of its buffer cache.

Yours,
Laurenz Albe