Re: 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

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