digoal
2024-01-30
PostgreSQL , PolarDB , DuckDB , explain , planner pharse , memory consumption
Add EXPLAIN (MEMORY) to report planner memory consumption
author Alvaro Herrera <alvherre@alvh.no-ip.org>
Mon, 29 Jan 2024 16:53:03 +0000 (17:53 +0100)
committer Alvaro Herrera <alvherre@alvh.no-ip.org>
Mon, 29 Jan 2024 16:53:03 +0000 (17:53 +0100)
commit 5de890e3610d5a12cdaea36413d967cf5c544e20
tree fa419c222f2f3c59c2e478ea305e21e6f1d05c66 tree
parent 6a1ea02c491d16474a6214603dce40b5b122d4d1 commit | diff
Add EXPLAIN (MEMORY) to report planner memory consumption
This adds a new "Memory:" line under the "Planning:" group (which
currently only has "Buffers:") when the MEMORY option is specified.
In order to make the reporting reasonably accurate, we create a separate
memory context for planner activities, to be used only when this option
is given. The total amount of memory allocated by that context is
reported as "allocated"; we subtract memory in the context's freelists
from that and report that result as "used". We use
MemoryContextStatsInternal() to obtain the quantities.
The code structure to show buffer usage during planning was not in
amazing shape, so I (Álvaro) modified the patch a bit to clean that up
in passing.
Author: Ashutosh Bapat
Reviewed-by: David Rowley, Andrey Lepikhov, Jian He, Andy Fan
Discussion: https://www.postgresql.org/message-id/CAExHW5sZA=5LJ_ZPpRO-w09ck8z9p7eaYAqq3Ks9GDfhrxeWBw@mail.gmail.com
说明
+ <varlistentry>
+ <term><literal>MEMORY</literal></term>
+ <listitem>
+ <para>
+ Include information on memory consumption by the query planning phase.
+ Specifically, include the precise amount of storage used by planner
+ in-memory structures, as well as total memory considering allocation
+ overhead.
+ This parameter defaults to <literal>FALSE</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
例子
+-- MEMORY option
+select explain_filter('explain (memory) select * from int8_tbl i8');
+ explain_filter
+---------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ Memory: used=N bytes allocated=N bytes
+(2 rows)
+
+select explain_filter('explain (memory, analyze) select * from int8_tbl i8');
+ explain_filter
+-----------------------------------------------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N) (actual time=N.N..N.N rows=N loops=N)
+ Memory: used=N bytes allocated=N bytes
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(4 rows)
+
+select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8');
+ explain_filter
+-------------------------------
+ - Plan: +
+ Node Type: "Seq Scan" +
+ Parallel Aware: false +
+ Async Capable: false +
+ Relation Name: "int8_tbl"+
+ Alias: "i8" +
+ Startup Cost: N.N +
+ Total Cost: N.N +
+ Plan Rows: N +
+ Plan Width: N +
+ Planning: +
+ Memory Used: N +
+ Memory Allocated: N +
+ Planning Time: N.N
+(1 row)
+
+select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8');
+ explain_filter
+------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Seq Scan", +
+ "Parallel Aware": false, +
+ "Async Capable": false, +
+ "Relation Name": "int8_tbl",+
+ "Alias": "i8", +
+ "Startup Cost": N.N, +
+ "Total Cost": N.N, +
+ "Plan Rows": N, +
+ "Plan Width": N, +
+ "Actual Startup Time": N.N, +
+ "Actual Total Time": N.N, +
+ "Actual Rows": N, +
+ "Actual Loops": N +
+ }, +
+ "Planning": { +
+ "Memory Used": N, +
+ "Memory Allocated": N +
+ }, +
+ "Planning Time": N.N, +
+ "Triggers": [ +
+ ], +
+ "Execution Time": N.N +
+ } +
+ ]
+(1 row)
+
+prepare int8_query as select * from int8_tbl i8;
+select explain_filter('explain (memory) execute int8_query');
+ explain_filter
+---------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+ Memory: used=N bytes allocated=N bytes
+(2 rows)
