Re: Parent/child context relation in pg_get_backend_memory_contexts()

Hi hackers,

Melih Mutlu <m(dot)melihmutlu(at)gmail(dot)com>, 16 Haz 2023 Cum, 17:03 tarihinde şunu
yazdı:

> 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,
--
Melih Mutlu
Microsoft