Re: Many of psql's describe functions bloat cache / waste mem

Hi Xiaoyu,

On 01.09.26 05:00, xliu19163(at)gmail(dot)com wrote:
> Hi Jan,
>
> Thanks for the detailed review and for confirming the memory reduction.
>
> Attached is v2. I followed your suggestions:

Thanks for v2 of the patch. It applied cleanly to the current master
branch and check-world passes.

Testing
=======

I ran a few tests locally, and it decreases memory usage for several
commands. I used the following script for verification:

sql="FROM pg_backend_memory_contexts
WHERE path @> (SELECT path FROM pg_backend_memory_contexts
WHERE name = 'CacheMemoryContext')"

for c in '\d' '\da' '\dc' '\dd' '\dD' '\df' '\di' '\dm' '\do' \
'\dO' '\dp' '\dP' '\ds' '\dt' '\dT' '\dv' '\z'; do
printf '%-5s ' "$c"
psql -X -qtA <<EOF
SELECT sum(total_bytes) AS before $sql \gset
\o /dev/null
$c
\o
SELECT sum(total_bytes) - :before $sql;
EOF
done

Compared to the master branch, I see the following changes:

cmd master v2 saved
\d 541696 541696 0
\da 537600 537600 0
\dc 535552 535552 0
\dd 1608704 560128 1048576
\dD 543744 543744 0
\df 7979200 537600 7441600
\di 541696 541696 0
\dm 541696 541696 0
\do 1583104 534528 1048576
\dO 1600576 535552 1065024
\dp 564224 564224 0
\dP 537600 537600 0
\ds 537600 537600 0
\dt 541696 541696 0
\dT 537600 537600 0
\dv 537600 537600 0
\z 564224 564224 0
total 10603776

The commands that show no change either have a cheap qual that
short-circuits before the visibility check (\da filters on prokind
first) or save less than one allocation block, which total_bytes cannot
show. We could also compare used_bytes, but since this thread started
with total_bytes, I don't want to change the memory-usage measurement
method to keep the results comparable (but I'm happy to do these
measurements if required).

Review
======

- One thing I noticed: in listTables() and listPartitionedTables(), the
TOAST schemas are still excluded via n.nspname. That part of the filter
cannot be applied during the pg_class scan, so \di still calls
pg_table_is_visible() for every TOAST index. Folding "n.nspname !~
'^pg_toast'" into the subquery in appendSystemSchemaFilter() would avoid
that. However, it has to stay optional for the other callers, since
objects can be created in pg_toast. That could be a follow-up patch to
keep this one focused on memory usage.

- The commit message is missing line breaks. I suggest wrapping it at
around 75 characters.

- The patch addresses two things: (1) it changes LEFT JOIN to JOIN and
(2) adds the filter. It could be beneficial to split this into two
different commits.

Apart from this, the patch looks good to me.

Best regards
Jan

--
Jan Nidzwetzki
PlanetScale Postgres Core Team