Re: Report planning memory in EXPLAIN ANALYZE

On Tue, Jan 16, 2024 at 8:19 PM Ashutosh Bapat
<ashutosh(dot)bapat(dot)oss(at)gmail(dot)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