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

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