Date: Mon, 05 Jun 2023 15:47:34 +0200
From: Tim Schwenke <tim@trallnag.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: "Pgsql Novice" <pgsql-novice@lists.postgresql.org>
Message-Id: <1888bd0285f.c47bd298692167.399116048841887836@trallnag.com>
In-Reply-To: <1888bb46d75.c27dbb7f688001.5044215935079224344@trallnag.com>
References: <1888b7bb7fa.112313046679561.2788161417267896332@trallnag.com>
 <CAKFQuwamUDxtmA3G4mUP1y4DvntoJ4EA4o02465dfiRO3O9rBQ@mail.gmail.com>
 <1888bb46d75.c27dbb7f688001.5044215935079224344@trallnag.com>
Subject: Re: Does PostgreSQL cache all columns of a table after SELECT?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_1996878_1350139321.1685972854879"
Importance: Medium
User-Agent: Zoho Mail
Archived-At: 
 <https://www.postgresql.org/message-id/1888bd0285f.c47bd298692167.399116048841887836%40trallnag.com>
Precedence: bulk

------=_Part_1996878_1350139321.1685972854879
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello,


I just tested it and it indeed works this way.


1. Created new table with only relevant columns.

2. Stopped PostgreSQL DB.

3. Dropped page cache.

4. Started again.

5. Run query against new table

6. Way less cache used, query is faster, less data is being read into cache=
.


---- On Mon, 05 Jun 2023 15:17:17 +0200 Tim Schwenke <tim@trallnag.com> wro=
te ---


Hello David,=20
=20
from what I understand, in PostgreSQL, tables are stored in one or more fil=
es called segments. There is no separation by columns.=20
=20
https://www.postgresql.org/docs/8.1/storage.html=20
=20
This means if I select a single column from a table the first time, the ful=
l file / segment is read and put into page cache if there is enough space. =
This means a table with only one large column large_a takes up less page ca=
che compared to a table with many large columns large_a and large_b, even t=
hough in both cases only large_a is selected.=20
=20
Is that more or less correct? Ignoring toast?=20
=20
Tim S.=20
=20
---- On Mon, 05 Jun 2023 14:58:21 +0200 David G. Johnston  wrote ---=20
=20
 >=20
 >=20
 > On Monday, June 5, 2023, Tim Schwenke mailto:tim@trallnag.com> wrote:=20
 >=20
 >=20
 > Does the cache also contain large_b? Or is only large_a cached? Assumpti=
on is that memory is large enough to fit everything.=20
 >=20
 >=20
 > Shared buffers is a page cache.=20
 >=20
 > David J.=C2=A0=20
 >
------=_Part_1996878_1350139321.1685972854879
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head>=
<meta content=3D"text/html;charset=3DUTF-8" http-equiv=3D"Content-Type"></h=
ead><body ><div style=3D"font-family: Verdana, Arial, Helvetica, sans-serif=
; font-size: 10pt;"><div>Hello,<br></div><div><br></div><div>I just tested =
it and it indeed works this way.<br></div><div><br></div><div>1. Created ne=
w table with only relevant columns.<br></div><div>2. Stopped PostgreSQL DB.=
<br></div><div>3. Dropped page cache.<br></div><div>4. Started again.<br></=
div><div>5. Run query against new table<br></div><div>6. Way less cache use=
d, query is faster, less data is being read into cache.<br></div><div><br><=
/div><div class=3D"zmail_extra_hr" style=3D"border-top: 1px solid rgb(204, =
204, 204); height: 0px; margin-top: 10px; margin-bottom: 10px; line-height:=
 0px;"><br></div><div class=3D"zmail_extra" data-zbluepencil-ignore=3D"true=
"><div><br></div><div id=3D"Zm-_Id_-Sgn1">---- On Mon, 05 Jun 2023 15:17:17=
 +0200 <b>Tim Schwenke &lt;tim@trallnag.com&gt;</b> wrote ---<br></div><div=
><br></div><blockquote id=3D"blockquote_zmail" style=3D"margin: 0px;"><div>=
Hello David, <br> <br>from what I understand, in PostgreSQL, tables are sto=
red in one or more files called segments. There is no separation by columns=
. <br> <br><a href=3D"https://www.postgresql.org/docs/8.1/storage.html" tar=
get=3D"_blank">https://www.postgresql.org/docs/8.1/storage.html</a> <br> <b=
r>This means if I select a single column from a table the first time, the f=
ull file / segment is read and put into page cache if there is enough space=
. This means a table with only one large column large_a takes up less page =
cache compared to a table with many large columns large_a and large_b, even=
 though in both cases only large_a is selected. <br> <br>Is that more or le=
ss correct? Ignoring toast? <br> <br>Tim S. <br> <br>---- On Mon, 05 Jun 20=
23 14:58:21 +0200 David G. Johnston  wrote --- <br> <br> &gt; <br> &gt; <br=
> &gt; On Monday, June 5, 2023, Tim Schwenke <a href=3D"mailto:tim@trallnag=
.com" target=3D"_blank">tim@trallnag.com</a>&gt; wrote: <br> &gt; <br> &gt;=
 <br> &gt; Does the cache also contain large_b? Or is only large_a cached? =
Assumption is that memory is large enough to fit everything. <br> &gt; <br>=
 &gt; <br> &gt; Shared buffers is a page cache. <br> &gt; <br> &gt; David J=
.&nbsp; <br> &gt; <br> <br></div></blockquote></div></div><br></body></html=
>
------=_Part_1996878_1350139321.1685972854879--