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

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(at)gmail(dot)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(at)anarazel(dot)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