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

On Tue, Jan 16, 2024 at 8:19 PM Ashutosh Bapat
<ashutosh.bapat.oss@gmail.com> wrote:
>
> Using a boolean return and moving es->indent-- outside of
> show_buffer_usage would be less elegant.
>

I went ahead with this option so as not to break backward
compatibility in any manner. Attached patch 0002 has the code and
expected output changes.

I have continued to use the variable "show_planning" and the new
variable is named accordingly. Other options I could think of, like
open_planning_section ended up being all long and rejected.

I considered adding a test for EXPLAIN(memory, buffers) but
explain.sql filters out Buffers: and Planning: lines, so it's not of
much use.

Returning from show_planning_buffers() is not necessary, but it looks
consistent with show_buffer_usage() in the code block using them
together.

Here's how the output looks now
In Text format
#explain (memory, buffers) select * from pg_class;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..18.15 rows=415 width=273)
 Planning:
   Buffers: shared hit=134 read=15
   I/O Timings: shared read=0.213
   Memory: used=22928 bytes, allocated=32768 bytes
(5 rows)

with planning time
#explain (analyze, memory, buffers) select * from pg_class;
                                               QUERY PLAN
---------------------------------------------------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..18.15 rows=415 width=273) (actual
time=0.020..0.378 rows=415 loops=1)
   Buffers: shared hit=14
 Planning:
   Buffers: shared hit=134 read=15
   I/O Timings: shared read=0.635
   Memory: used=22928 bytes, allocated=32768 bytes
 Planning Time: 4.328 ms
 Execution Time: 1.262 ms
(8 rows)

Just memory option
#explain (memory) select * from pg_class;
                         QUERY PLAN
-------------------------------------------------------------
 Seq Scan on pg_class  (cost=0.00..18.15 rows=415 width=273)
 Planning:
   Memory: used=22928 bytes, allocated=32768 bytes
(3 rows)

In JSON format
#explain (memory, buffers, format json) select * from pg_class;
(Notice Memory Used and Memory Allocated properties.
              QUERY PLAN
---------------------------------------
 [                                    +
   {                                  +
     "Plan": {                        +
... snip ...
     },                               +
     "Planning": {                    +
       "Shared Hit Blocks": 152,      +
       "Shared Read Blocks": 0,       +
       "Shared Dirtied Blocks": 0,    +
       "Shared Written Blocks": 0,    +
       "Local Hit Blocks": 0,         +
       "Local Read Blocks": 0,        +
       "Local Dirtied Blocks": 0,     +
       "Local Written Blocks": 0,     +
       "Temp Read Blocks": 0,         +
       "Temp Written Blocks": 0,      +
       "Shared I/O Read Time": 0.000, +
       "Shared I/O Write Time": 0.000,+
       "Local I/O Read Time": 0.000,  +
       "Local I/O Write Time": 0.000, +
       "Temp I/O Read Time": 0.000,   +
       "Temp I/O Write Time": 0.000,  +
       "Memory Used": 22928,          +
       "Memory Allocated": 32768      +
     }                                +
   }                                  +
 ]
(1 row)

JSON format with planning time
#explain (analyze, memory, buffers, format json) select * from pg_class;
              QUERY PLAN
---------------------------------------
 [                                    +
   {                                  +
     "Plan": {                        +
 ... snip ...
     },                               +
     "Planning": {                    +
       "Shared Hit Blocks": 152,      +
       "Shared Read Blocks": 0,       +
       "Shared Dirtied Blocks": 0,    +
       "Shared Written Blocks": 0,    +
       "Local Hit Blocks": 0,         +
       "Local Read Blocks": 0,        +
       "Local Dirtied Blocks": 0,     +
       "Local Written Blocks": 0,     +
       "Temp Read Blocks": 0,         +
       "Temp Written Blocks": 0,      +
       "Shared I/O Read Time": 0.000, +
       "Shared I/O Write Time": 0.000,+
       "Local I/O Read Time": 0.000,  +
       "Local I/O Write Time": 0.000, +
       "Temp I/O Read Time": 0.000,   +
       "Temp I/O Write Time": 0.000,  +
       "Memory Used": 22928,          +
       "Memory Allocated": 32768      +
     },                               +
     "Planning Time": 3.840,          +
     "Triggers": [                    +
     ],                               +
     "Execution Time": 1.266          +
   }                                  +
 ]
(1 row)

>
> How about separating those with ",". That would add a minor
> inconsistency with how values in Buffers and I/O Timings sections are
> reported.
>

did this way.

--
Best Wishes,
Ashutosh Bapat