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 <[email protected]> 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
From aa70a92408ba967e4e97c53f92e5430e7f8524ee Mon Sep 17 00:00:00 2001
From: MisterRaindrop <[email protected]>
Date: Wed, 22 Jul 2026 18:49:53 +0800
Subject: [PATCH v1] psql: filter system functions before visibility checks in
\df
---
src/bin/psql/describe.c | 26 ++++++++++++++++++++++----
src/bin/psql/t/001_basic.pl | 12 ++++++++++++
2 files changed, 34 insertions(+), 4 deletions(-)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index a2f09c26369..9233a9ff1e0 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -546,6 +546,28 @@ describeFunctions(const char *functypes, const char *func_pattern,
appendPQExpBufferStr(&buf, " )\n");
}
+ /*
+ * Use p.pronamespace here, rather than n.nspname, so that the
+ * system schemas can be filtered during the pg_proc scan, before calling
+ * pg_function_is_visible(). The latter can populate catalog caches for
+ * every function it examines. The ARRAY subquery becomes an InitPlan and
+ * quietly ignores either schema if it does not exist.
+ */
+ if (!showSystem && !func_pattern)
+ {
+ if (have_where)
+ appendPQExpBufferStr(&buf, " AND ");
+ else
+ {
+ appendPQExpBufferStr(&buf, "WHERE ");
+ have_where = true;
+ }
+ appendPQExpBufferStr(&buf,
+ "p.pronamespace <> ALL (ARRAY(\n"
+ " SELECT oid FROM pg_catalog.pg_namespace\n"
+ " WHERE nspname IN ('pg_catalog', 'information_schema')))\n");
+ }
+
if (!validateSQLNamePattern(&buf, func_pattern, have_where, false,
"n.nspname", "p.proname", NULL,
"pg_catalog.pg_function_is_visible(p.oid)",
@@ -586,10 +608,6 @@ describeFunctions(const char *functypes, const char *func_pattern,
}
}
- if (!showSystem && !func_pattern)
- appendPQExpBufferStr(&buf, " AND n.nspname <> 'pg_catalog'\n"
- " AND n.nspname <> 'information_schema'\n");
-
appendPQExpBufferStr(&buf, "ORDER BY 1, 2, 4;");
res = PSQLexec(buf.data);
diff --git a/src/bin/psql/t/001_basic.pl b/src/bin/psql/t/001_basic.pl
index bbd330216ae..d972e82a9dd 100644
--- a/src/bin/psql/t/001_basic.pl
+++ b/src/bin/psql/t/001_basic.pl
@@ -75,6 +75,18 @@ $node->start;
psql_like($node, '\copyright', qr/Copyright/, '\copyright');
psql_like($node, '\help', qr/ALTER/, '\help without arguments');
psql_like($node, '\help SELECT', qr/SELECT/, '\help with argument');
+psql_like(
+ $node,
+ '\set ECHO_HIDDEN on
+\df',
+ qr{
+ WHERE \s+ p\.pronamespace \s+ <> \s+ ALL \s+ \(ARRAY\(
+ .*? SELECT \s+ oid \s+ FROM \s+ pg_catalog\.pg_namespace
+ .*? WHERE \s+ nspname \s+ IN \s+
+ \('pg_catalog', \s+ 'information_schema'\)\)\)
+ .*? AND \s+ pg_catalog\.pg_function_is_visible\(p\.oid\)
+ }xs,
+ '\df filters system functions before testing visibility');
# Test clean handling of unsupported replication command responses
psql_fails_like(
--
2.44.0
