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

Hi Andres,

Thanks for the report. Attached is a patch that changes describeFunctions()
so that, when system functions are not requested and no function pattern is
supplied, functions in pg_catalog and information_schema are filtered by
p.pronamespace before pg_function_is_visible() is evaluated.

I used an uncorrelated ARRAY subquery over pg_namespace rather than
regnamespace casts. In the tested plan it becomes an InitPlan, and it also
avoids an error if information_schema has been dropped.

Using fresh backends built from the same master revision, I measured:

before after
CacheMemoryContext increase 7,454,912 13,312 bytes
shared buffer hits 19,105 108
execution time 10.189 0.304 ms

The default \df result was unchanged (0 rows). I also tested \dfS, verbose,
schema-qualified and argument-type patterns, search_path changes, same-name
functions in multiple schemas, and a database without information_schema.
The patch adds a TAP test for the generated query, and make check-world
passes.

Review and comments would be appreciated.

Regards,
Xiaoyu

Hi,

I plan to register this patch in the next open CommitFest so that it can
enter the regular review queue and be picked up by CFBot.

If anyone has already started reviewing it, or sees an issue with the
current approach, please let me know.

Regards,
Xiaoyu

On Wed, 22 Jul 2026 19:55:50 +0800, xiaoyu liu xliu19163@gmail.com wrote:

Hi Andres,

Thanks for the report. Attached is a patch that changes describeFunctions()
so that, when system functions are not requested and no function pattern is
supplied, functions in pg_catalog and information_schema are filtered by
p.pronamespace before pg_function_is_visible() is evaluated.

I used an uncorrelated ARRAY subquery over pg_namespace rather than
regnamespace casts. In the tested plan it becomes an InitPlan, and it also
avoids an error if information_schema has been dropped.

Using fresh backends built from the same master revision, I measured:

before after
CacheMemoryContext increase 7,454,912 13,312 bytes
shared buffer hits 19,105 108
execution time 10.189 0.304 ms

The default \df result was unchanged (0 rows). I also tested \dfS, verbose,
schema-qualified and argument-type patterns, search_path changes, same-name
functions in multiple schemas, and a database without information_schema.
The patch adds a TAP test for the generated query, and make check-world
passes.

Review and comments would be appreciated.

Regards,
Xiaoyu

On Mon, 20 Jul 2026 17:25:00 -0400, Andres Freund andres@anarazel.de wrote:

Hi,

I was just looking at an memory usage issue and noticed that a single ‘\df’
in
a database without any user-defined functions, increases backend memory
usage
by ~7MB.

Turns out the fault of that is psql’s query, which populates the catcaches
for
every function in the system:

empty[817089][1]=# explain ANALYZE /**** INTERNAL QUERY ***
*/ /* Get matching functions */
SELECT n.nspname as “Schema”,
p.proname as “Name”,
pg_catalog.pg_get_function_result(p.oid) as “Result data type”,
pg_catalog.pg_get_function_arguments(p.oid) as “Argument data types”,
CASE p.prokind
WHEN ‘a’ THEN ‘agg’
WHEN ‘w’ THEN ‘window’
WHEN ‘p’ THEN ‘proc’
ELSE ‘func’
END as “Type”
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname <> ‘pg_catalog’
AND n.nspname <> ‘information_schema’
ORDER BY 1, 2, 4;
┌──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ QUERY PLAN │
├──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Sort (cost=269.17…270.89 rows=688 width=224) (actual time=37.135…37.137
rows=0.00 loops=1) │
│ Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid)) │
│ Sort Method: quicksort Memory: 25kB │
│ Buffers: shared hit=19100 │
│ -> Hash Join (cost=1.11…236.74 rows=688 width=224) (actual
time=37.089…37.090 rows=0.00 loops=1) │
│ Hash Cond: (p.pronamespace = n.oid) │
│ Buffers: shared hit=19094 │
│ -> Seq Scan on pg_proc p (cost=0.00…221.47 rows=1147 width=73) (actual
time=0.040…36.613 rows=3431.00 loops=1) │
│ Filter: pg_function_is_visible(oid) │
│ Rows Removed by Filter: 11 │
│ Buffers: shared hit=19093 │
│ -> Hash (cost=1.07…1.07 rows=3 width=68) (actual time=0.009…0.010
rows=3.00 loops=1) │
│ Buckets: 1024 Batches: 1 Memory Usage: 9kB │
│ Buffers: shared hit=1 │
│ -> Seq Scan on pg_namespace n (cost=0.00…1.07 rows=3 width=68) (actual
time=0.003…0.005 rows=3.00 loops=1) │
│ Filter: ((nspname <> ‘pg_catalog’::name) AND (nspname <>
‘information_schema’::name)) │
│ Rows Removed by Filter: 2 │
│ Buffers: shared hit=1 │
│ Planning: │
│ Buffers: shared hit=270 │
│ Planning Time: 1.019 ms │
│ Execution Time: 37.277 ms │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(22 rows)

Because the nspname <> ‘pg_catalog’ condition is not something that can be
evaluated during the sequential scan on pg_proc, pg_function_is_visible() is
executed for every proc. Which in turn ends up trigger the population of the
entire PROCOID *and* PROCNAMEARGSNSP catcaches (because catalog functions
are
visible, we end up doing FuncnameGetCandidates for all functions, which in
turn does a list search in PROCNAMEARGSNSP).

(Also, pretty odd this is a left join, given that the filter condition turns
it back into an inner join)

To show the effect:

empty[817815][1]=# SELECT count(*), sum(total_bytes) total_bytes,
sum(total_nblocks) total_nblocks FROM pg_backend_memory_contexts WHERE path
@> (SELECT path FROM pg_backend_memory_contexts WHERE name =
‘CacheMemoryContext’);
┌───────┬─────────────┬───────────────┐
│ count │ total_bytes │ total_nblocks │
├───────┼─────────────┼───────────────┤
│ 97 │ 1251072 │ 163 │
└───────┴─────────────┴───────────────┘
(1 row)

empty[817815][1]=# \df
List of functions
┌────────┬──────┬──────────────────┬─────────────────────┬──────┐
│ Schema │ Name │ Result data type │ Argument data types │ Type │
├────────┼──────┼──────────────────┼─────────────────────┼──────┤
└────────┴──────┴──────────────────┴─────────────────────┴──────┘
(0 rows)

empty[817815][1]=# SELECT count(*), sum(total_bytes) total_bytes,
sum(total_nblocks) total_nblocks FROM pg_backend_memory_contexts WHERE path
@> (SELECT path FROM pg_backend_memory_contexts WHERE name =
‘CacheMemoryContext’);
┌───────┬─────────────┬───────────────┐
│ count │ total_bytes │ total_nblocks │
├───────┼─────────────┼───────────────┤
│ 102 │ 8705984 │ 182 │
└───────┴─────────────┴───────────────┘
(1 row)

I think a lot of psql’s queries have this issue, although most of them won’t
be as problematic, because pg_proc has a fair number of rows in pg_catalog
(compared to e.g. pg_class, where’s an order of magnitude fewer).

If the query instead is rewritten to filter with:

WHERE pg_catalog.pg_function_is_visible(p.oid)
AND p.pronamespace <> ‘pg_catalog’::regnamespace
AND p.pronamespace <> ‘information_schema’::regnamespace

the query is considerably faster (both first and subsequent executions) and
more importantly the memory usage afterwards is:

┌───────┬─────────────┬───────────────┐
│ count │ total_bytes │ total_nblocks │
├───────┼─────────────┼───────────────┤
│ 101 │ 1264384 │ 174 │
└───────┴─────────────┴───────────────┘
(1 row)

I used a regnamespace query here, but because we use patterns etc in some
places, it’s probably better done as a subquery.

I don’t plan to work on fixing this in the near term, but it seemed like a
significant enough effect to be worth mentioning on the list.

Greetings,

Andres Freund

Hi Hackers,

On 24.07.26 05:17, xiaoyu liu wrote:
[...]

If anyone has already started reviewing it, or sees an issue with the
current approach, please let me know.

Thanks for the patch. I tested it and it applied to the current master
branch (a1bb92fb), and check-world passes. I checked the size of the
CacheMemoryContext before and after running \df:

master@a1bb92fb: 8,705,984 B
patch applied: 1,264,384 B

So the patch clearly reduces the size of the CacheMemoryContext
populated by this query. I also tested the query plan shape. As
mentioned in the comment of the patch, the OID lookup subquery becomes
an InitPlan:

QUERY PLAN

---------------------------------------------------------------------------------------------------------
Sort (cost=268.31..268.32 rows=1 width=224)
Sort Key: n.nspname, p.proname, (pg_get_function_arguments(p.oid))
InitPlan array_1
-> Seq Scan on pg_namespace (cost=0.00..1.06 rows=2 width=4)
Filter: (nspname = ANY
('{pg_catalog,information_schema}'::name[]))
-> Nested Loop Left Join (cost=0.00..267.24 rows=1 width=224)
Join Filter: (n.oid = p.pronamespace)
-> Seq Scan on pg_proc p (cost=0.00..266.11 rows=1 width=73)
Filter: ((pronamespace <> ALL ((InitPlan array_1).col1))
AND pg_function_is_visible(oid))
-> Seq Scan on pg_namespace n (cost=0.00..1.05 rows=5 width=68)
(10 rows)

Now a 'Nested Loop Left Join' is used. As Andres pointed out, this LEFT
JOIN could be changed to a JOIN.

Tests
=====

I think I found an issue in the test. According to the test description,
it ensures "filters system functions before testing visibility". To do
so, the filter must run first, then pg_function_is_visible(). This
happens currently as desired in the query plan.

Filter: ((pronamespace <> ALL ((InitPlan array_1).col1)) AND
pg_function_is_visible(oid))

However, PostgreSQL could reorder both conditions (they are ordered that
way because of their costs). But the costs could change for whatever
reason. For example, if you run "ALTER FUNCTION
pg_catalog.pg_function_is_visible(oid) COST 1;", the conditions in the
query filter flip to:

Filter: (pg_function_is_visible(oid) AND (pronamespace <> ALL ((InitPlan
array_1).col1)))

In this case, pg_function_is_visible() runs for every row found in the
catalog. I verified this using 'funccount' and running \df in parallel
(in my database I have 6 user functions and 3455 catalog functions defined):

# Filter first and then pg_function_is_visible(oid)

$ sudo funccount-bpfcc "$(pg_config
--bindir)/postgres:pg_function_is_visible"
[...]
FUNC COUNT
pg_function_is_visible 6
Detaching...

# pg_function_is_visible(oid) first and then the filter

$ sudo funccount-bpfcc "$(pg_config
--bindir)/postgres:pg_function_is_visible"
[...]
FUNC COUNT
pg_function_is_visible 3461
Detaching...

The regex only checks the SQL text that psql generates, not the
resulting query plan. That text is identical in both cases, so the test
cannot detect this regression at all: with the procost changed as above,
pg_function_is_visible() is called for every row in pg_proc and
001_basic.pl still passes.

That said, I am not sure if we need such kind of test in the end. For
example, 112e40b867b reordered clauses for plan reasons in describe.c
and went in without tests.

Other \d commands
=================

As Andres outlined, there are more \d commands affected by this problem
and only \df is changed by the current version of this patch. For
example, \do uses the old shape of the query plan where
'pg_operator_is_visible' is called for every operator.

The query plan for \do:

QUERY PLAN

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=133.01..133.41 rows=161 width=256)
Sort Key: n.nspname, o.oprname, (CASE WHEN (o.oprkind = 'l'::"char")
THEN NULL::text ELSE format_type(o.oprleft, NULL::integer) END), (CASE
WHEN (o.oprkind = 'r'::"char") THEN NULL::text ELSE
format_type(o.oprright, NULL::integer) END)
-> Hash Join (cost=1.11..127.10 rows=161 width=256)
Hash Cond: (o.oprnamespace = n.oid)
-> Seq Scan on pg_operator o (cost=0.00..42.18 rows=268 width=89)
Filter: pg_operator_is_visible(oid)
-> Hash (cost=1.07..1.07 rows=3 width=68)
-> Seq Scan on pg_namespace n (cost=0.00..1.07 rows=3
width=68)
Filter: ((nspname <> 'pg_catalog'::name) AND
(nspname <> 'information_schema'::name))
(9 rows)

What do you think about introducing a helper function like
'appendSystemSchemaFilter()' that adds the filter condition as proposed
in the patch, and calling it from the other places in describe.c that
exclude the system schemas?

Best regards
Jan

--
Jan Nidzwetzki
PlanetScale Postgres Core Team

Hi Jan,

Thanks for the detailed review and for confirming the memory reduction.

Attached is v2. I followed your suggestions:

- introduced appendSystemSchemaFilter() and reused it in the
describe.c queries that exclude pg_catalog and information_schema,
including \df and \do;
- changed the corresponding object-to-pg_namespace LEFT JOINs to JOIN
where the namespace is mandatory;
- removed the TAP test from v1, since checking the generated SQL text
cannot guarantee planner clause evaluation order.

The filter still uses an uncorrelated pg_namespace subquery, so it
becomes an InitPlan. The ARRAY(...) / <> ALL form also continues to
work when information_schema is absent.

I rebased v2 onto current master (3b120b1e94d). make check-world
passes for all suites enabled in my local build. TAP tests were not
enabled because IPC::Run is not available locally. I also manually
checked the affected psql commands, including \df and \do, and
confirmed the expected plan shape and CacheMemoryContext behavior.

Please let me know what you think.

Best regards,
Xiaoyu

Hi Xiaoyu,

On 01.09.26 05:00, xliu19163@gmail.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

Hi Jan,

Thanks for testing v2 and for the additional review.

Attached is v3. I split the changes into two patches:

1. Change the applicable object-to-pg_namespace joins from LEFT JOIN to JOIN.
2. Add appendSystemSchemaFilter() and use it in the affected describe queries.

I also wrapped both commit messages at around 75 characters.

I left the pg_toast optimization out of this series, as suggested, so it can
be handled separately without expanding the scope of this patch.

The series is rebased onto current master (792094a5ce8). make check-world
passes for all suites enabled in my local build. TAP tests were not enabled
because IPC::Run is unavailable locally.

Best regards,
Xiaoyu

Hi Xiaoyu,

On 03.09.26 04:30, xliu19163@gmail.com wrote:

Hi Jan,

Thanks for testing v2 and for the additional review.

Attached is v3. I split the changes into two patches:

Thank you for the v3 patch series. I have applied the patches in order
and performed a "make check-world" after each patch. They applied
cleanly to the master branch, and all tests passed successfully.

I ran a couple of tests and compared the command output to master. The
output looks good and unchanged. I can also confirm that the memory
reduction I measured with v2 is still there.

So, the series looks good to me.

Best regards
Jan

--
Jan Nidzwetzki
PlanetScale Postgres Core Team