Re: Parent/child context relation in pg_get_backend_memory_contexts()

Hi hackers,


Melih Mutlu <m.melihmutlu@gmail.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