With this change, here's a query to find how much space used by each
context including its children:
> WITH RECURSIVE cte AS (
> SELECT id, total_bytes, id as root, name as root_name
> FROM memory_contexts
> UNION ALL
> SELECT r.id, r.total_bytes, cte.root, cte.root_name
> FROM memory_contexts r
> INNER JOIN cte ON r.parent_id = cte.id
> ),
> memory_contexts AS (
> SELECT * FROM pg_backend_memory_contexts
> )
> SELECT root as id, root_name as name, sum(total_bytes)
> FROM cte
> GROUP BY root, root_name
> ORDER BY sum DESC;
Given that the above query to get total bytes including all children is still a complex one, I decided to add an additional info in pg_backend_memory_contexts.
The new "path" field displays an integer array that consists of ids of all parents for the current context. This way it's easier to tell whether a context is a child of another context, and we don't need to use recursive queries to get this info.
Here how pg_backend_memory_contexts would look like with this patch:
postgres=# SELECT name, id, parent, parent_id, path
FROM pg_backend_memory_contexts
ORDER BY total_bytes DESC LIMIT 10;
name | id | parent | parent_id | path
-------------------------+-----+------------------+-----------+--------------
CacheMemoryContext | 27 | TopMemoryContext | 0 | {0}
Timezones | 124 | TopMemoryContext | 0 | {0}
TopMemoryContext | 0 | | |
MessageContext | 8 | TopMemoryContext | 0 | {0}
WAL record construction | 118 | TopMemoryContext | 0 | {0}
ExecutorState | 18 | PortalContext | 17 | {0,16,17}
TupleSort main | 19 | ExecutorState | 18 | {0,16,17,18}
TransactionAbortContext | 14 | TopMemoryContext | 0 | {0}
smgr relation table | 10 | TopMemoryContext | 0 | {0}
GUC hash table | 123 | GUCMemoryContext | 122 | {0,122}
(10 rows)
An example query to calculate the total_bytes including its children for a context (say CacheMemoryContext) would look like this:
WITH contexts AS (
SELECT * FROM pg_backend_memory_contexts
)
SELECT sum(total_bytes)
FROM contexts
WHERE ARRAY[(SELECT id FROM contexts WHERE name = 'CacheMemoryContext')] <@ path;
We still need to use cte since ids are not persisted and might change in each run of pg_backend_memory_contexts. Materializing the result can prevent any inconsistencies due to id change. Also it can be even good for performance reasons as well.
Any thoughts?
Thanks,