Subject: Loading the latest N rows into the cache seems way too fast.

From: Ron Johnson <[email-masked]>
Date: Mon, 17 Feb 2025 15:32:47 -0500

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!

---

From: Tom Lane <[email-masked]>
Date: Mon, 17 Feb 2025 16:36:37 -0500

> computer speed, or is this statement not doing what I hope it's doing?

It's not pulling in the TOAST storage where the bytea column lives.
(pg_prewarm wouldn't have either, without special pushups.)

			regards, tom lane

---

From: Ron Johnson <[email-masked]>
Date: Mon, 17 Feb 2025 16:40:56 -0500

> (pg_prewarm wouldn't have either, without special pushups.)

Puzzling, since I ran "PERFORM *".  What if I explicitly mentioned the
bytea column's name?

---

From: Tom Lane <[email-masked]>
Date: Mon, 17 Feb 2025 16:51:10 -0500

> Puzzling, since I ran "PERFORM *".  What if I explicitly mentioned the

You'd have to do something that actually used the column's value,
perhaps "md5(byteacol)" or such.  (The obvious candidate would be
length(), but I think that is optimized to not fetch or decompress
the whole value.)

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Mon, 17 Feb 2025 14:58:53 -0700

> Puzzling, since I ran "PERFORM *".  What if I explicitly mentioned the
It's more about the system optimizing away data retrieval because you've
indicated you don't care about the contents due to using PERFORM.  All it
needs is a pointer to represent the future data, not the data itself.  And
PERFORM will never resolve that pointer by itself - so as Tom said your
query would need to force pointer resolution by computing on the data.

David J.

---

From: Ron Johnson <[email-masked]>
Date: Mon, 17 Feb 2025 18:03:18 -0500

> length(), but I think that is optimized to not fetch or decompress

That's definitely taking a LOT longer...