Re: Parent/child context relation in pg_get_backend_memory_contexts()

Greetings,

* Andres Freund (andres@anarazel.de) wrote:
> On 2023-10-18 15:53:30 -0400, Stephen Frost wrote:
> > > 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;
> >
> > I wonder if we should perhaps just include
> > "total_bytes_including_children" as another column?  Certainly seems
> > like a very useful thing that folks would like to see.
>
> The "issue" is where to stop - should we also add that for some of the other
> columns? They are a bit less important, but not that much.

I'm not sure the others really make sense to aggregate in this way as
free space isn't able to be moved between contexts.  That said, if
someone wants it then I'm not against that.  I'm actively in support of
adding an aggregated total though as that, at least to me, seems to be
very useful to have.

Thanks,

Stephen