From: | Ashutosh Bapat <ashutosh(dot)bapat(dot)oss(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org>, ams(at)toroid(dot)org |
Cc: | jian he <jian(dot)universality(at)gmail(dot)com>, Andrei Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>, Andy Fan <zhihui(dot)fan1213(at)gmail(dot)com>, David Rowley <dgrowleyml(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Report planning memory in EXPLAIN ANALYZE |
Date: | 2024-01-18 09:55:17 |
Message-ID: | CAExHW5s0cgV1FT+nzSD_BJGjYK0g9FkRQ0vn45Fs=iLOGRgRSQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
0001-EXPLAIN-reports-memory-consumed-for-plannin-20240118.patch | text/x-patch | 16.5 KB |
0002-Use-Planning-section-to-report-memory-per-A-20240118.patch | text/x-patch | 8.8 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2024-01-18 10:15:31 | Re: [17] CREATE COLLATION default provider |
Previous Message | Amit Kapila | 2024-01-18 09:54:10 | Re: subscription disable_on_error not working after ALTER SUBSCRIPTION set bad conninfo |