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
