MIME-Version: 1.0
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Mon, 17 Feb 2025 15:32:47 -0500
Message-ID: <CANzqJaBTPgTJ_M3dGiOa5H-FvAo71oCCa1QHejbzK+joKdrSyw@mail.gmail.com>
Subject: Loading the latest N rows into the cache seems way too fast.
To: pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000114180062e5c6e15"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaBTPgTJ_M3dGiOa5H-FvAo71oCCa1QHejbzK%2BjoKdrSyw%40mail.gmail.com>
Precedence: bulk

--000000000000114180062e5c6e15
Content-Type: text/plain; charset="UTF-8"

PG 9.6.24 and PG 14.15, if it matters.
(Yes, 9.6 is really EOL.  I don't control that.)

(I could use pg_prewarm, but the table is much bigger than RAM, and
last_block value only has the newest record if data has never been
deleted.  The oldest records regularly get deleted, and then the table is
vacuumed; thus, new records can be anywhere in the table.)

Thus, roll my own cache-loading statement.

The bigint "id" column in "mytbl" is populated from a sequence, and so is
monotonically increasing: the newest records will have the biggest id
values.
The table also has a bytea column that averages about 100KB.

Loading 200K rows is more than 200MB.  I expected this "prewarm" statement
to take much longer than 1/2 second.  Am I still in the dark ages of
computer speed, or is this statement not doing what I hope it's doing?

$ time psql -h foo bar -Xc "DO \$\$ BEGIN PERFORM * FROM mytbl ORDER BY id
DESC LIMIT 200000 ; END \$\$;"
DO

real    0m0.457s
user    0m0.005s
sys     0m0.004s

-- 
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

--000000000000114180062e5c6e15
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>PG 9.6.24 and PG 14.15, if it matters.</div><div>(Yes=
, 9.6 is really EOL.=C2=A0 I don&#39;t control that.)</div><div><br></div><=
div>(I could use pg_prewarm, but the table is much=C2=A0bigger than RAM, an=
d last_block value only has the newest record if data has never been delete=
d.=C2=A0 The oldest records regularly get deleted, and then the table is va=
cuumed; thus, new records can be anywhere in the table.)</div><div><br></di=
v><div>Thus, roll my own cache-loading statement.</div><div><br></div><div>=
The bigint &quot;id&quot; column in &quot;mytbl&quot; is populated from a s=
equence, and so is monotonically increasing: the newest records will have t=
he biggest id values.</div><div>The table also has a bytea column that aver=
ages about 100KB.</div><div><br></div><div>Loading 200K rows is more than 2=
00MB.=C2=A0 I expected this &quot;prewarm&quot; statement to take much long=
er than 1/2 second.=C2=A0 Am I still in the dark ages of computer speed, or=
 is this statement not doing what I hope it&#39;s doing?</div><div><br></di=
v><div>$ time psql -h foo bar -Xc &quot;DO \$\$ BEGIN PERFORM * FROM mytbl =
ORDER BY id DESC LIMIT 200000 ; END \$\$;&quot;<br>DO<br><br>real =C2=A0 =
=C2=A00m0.457s<br>user =C2=A0 =C2=A00m0.005s<br>sys =C2=A0 =C2=A0 0m0.004s<=
/div><div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><d=
iv dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signature"=
><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div>Don&#39;=
t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobster!</div=
></div></div></div></div></div>

--000000000000114180062e5c6e15--