Re: Report planning memory in EXPLAIN ANALYZE - Mailing list pgsql-hackers

Hi David,
Replying to your comments on this thread.

> On Tue, Aug 8, 2023 at 11:40 AM Ashutosh Bapat <ashutosh.bapat.oss@gmail.com> wrote:
>>
>> Hi All,
>> I have been looking at memory consumed when planning a partitionwise join [1], [2] and [3]. I am using the attached
patchto measure the memory consumption. The patch has been useful to detect an increased memory consumption in other
patchese.g. [4] and [5]. The patch looks useful by itself. So I propose this enhancement in EXPLAIN ANALYZE. 
>>

On Wed, Aug 9, 2023 at 10:12 AM David Rowley <dgrowleyml@gmail.com> wrote:
>
> I see you're recording the difference in the CurrentMemoryContext of
> palloc'd memory before and after planning.  That won't really alert us
> to problems if the planner briefly allocates, say 12GBs of memory
> during, say the join search then quickly pfree's it again.  unless
> it's an oversized chunk, aset.c won't free() any memory until
> MemoryContextReset(). Chunks just go onto a freelist for reuse later.
> So at the end of planning, the context may still have that 12GBs
> malloc'd, yet your new EXPLAIN ANALYZE property might end up just
> reporting a tiny fraction of that.
>
> I wonder if it would be more useful to just have ExplainOneQuery()
> create a new memory context and switch to that and just report the
> context's mem_allocated at the end.

The memory allocated but not used is still available for use in rest
of the query processing stages. The memory context which is
CurrentMemoryContext at the time of planning is also
CurrentMemoryContext at the time of its execution if it's not
PREPAREd. So it's not exactly "consumed" by memory. But your point is
valid, that it indicates how much was allocated. Just reporting
allocated memory wont' be enough since it might have been allocated
before planning began. How about reporting both used and net allocated
memory? If we use a separate memory context as you suggest, context's
mem_allocated would be net allocated.

>
> It's also slightly annoying that these planner-related summary outputs
> are linked to EXPLAIN ANALYZE. We could be showing them in EXPLAIN
> without ANALYZE.  If we were to change that now, it might be a bit
> annoying for the regression tests as we'd need to go and add SUMMARY
> OFF in a load of places...

We do report planning time when SUMMARY ON without ANALYZE. Am I
missing something?

#select version();
                                                 version
----------------------------------------------------------------------------------------------------------
 PostgreSQL 16devel on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit
(1 row)
#explain (summary on) select * from pg_class;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..18.13 rows=413 width=273)
 Planning Time: 0.282 ms
(2 rows)
#explain (summary off) select * from pg_class;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..18.13 rows=413 width=273)
(1 row)

I need to just make sure that the Planning Memory is reported with SUMMARY ON.


--
Best Wishes,
Ashutosh Bapat