Message-ID: <9471e8396d5e0de1b0b087678f52e9dc478837a8.camel@cybertec.at>
Subject: Re: Does PostgreSQL cache all columns of a table after SELECT?
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Tim Schwenke <tim@trallnag.com>, pgsql-novice
	 <pgsql-novice@lists.postgresql.org>
Date: Mon, 05 Jun 2023 15:58:38 +0200
In-Reply-To: <1888b7bb7fa.112313046679561.2788161417267896332@trallnag.com>
References: <1888b7bb7fa.112313046679561.2788161417267896332@trallnag.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.46.4 (3.46.4-1.fc37) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/9471e8396d5e0de1b0b087678f52e9dc478837a8.camel%40cybertec.at>
Precedence: bulk

On Mon, 2023-06-05 at 14:15 +0200, Tim Schwenke wrote:
> Hello,
>=20
> I have the following table with the following columns:
>=20
> large_a: text (few dozen characters up to a few hundred)
> large_b: text (few dozen characters up to a few hundred)
>=20
> The table has several million rows. The DB is running on a large machine.
>=20
> I perform the following query:
>=20
> select large_a from table;
>=20
> The first query takes a few minutes. Afterwards I see that the cache in m=
emory has grown. Next query only takes a few seconds.
>=20
> What I want to know:
>=20
> Does the cache also contain large_b? Or is only large_a cached? Assumptio=
n is that memory is large enough to fit everything.

It depends.

If "large_a" and "large_b" are TOASTed, then they will only be cached if th=
e column
is explicitly selected.

For shorter attributes, the whole row is in one piece, and all columns will=
 get cached.

However, for sequential scans that are larger than a quarter of shared buff=
ers,
PostgreSQL uses a ring buffer to avoid blowing out the cache.  In that case=
, most
data won't be cached for the next query.

Yours,
Laurenz Albe