From 5de890e3610d5a12cdaea36413d967cf5c544e20 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 29 Jan 2024 17:53:03 +0100 Subject: 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 --- src/test/regress/expected/explain.out | 76 +++++++++++++++++++++++++++++++++++ src/test/regress/sql/explain.sql | 8 ++++ 2 files changed, 84 insertions(+) (limited to 'src/test') diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index 809655e16ea..1299ee79ad5 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -326,6 +326,82 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); ERROR: EXPLAIN options ANALYZE and GENERIC_PLAN cannot be used together CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement +-- 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) + -- Test EXPLAIN (GENERIC_PLAN) with partition pruning -- partitions should be pruned at plan time, based on constants, -- but there should be no pruning based on parameter placeholders diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index b6b7beab27a..2274dc1b5a6 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -94,6 +94,14 @@ select explain_filter('explain (generic_plan) select unique1 from tenk1 where th -- should fail select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +-- MEMORY option +select explain_filter('explain (memory) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze) select * from int8_tbl i8'); +select explain_filter('explain (memory, summary, format yaml) select * from int8_tbl i8'); +select explain_filter('explain (memory, analyze, format json) select * from int8_tbl i8'); +prepare int8_query as select * from int8_tbl i8; +select explain_filter('explain (memory) execute int8_query'); + -- Test EXPLAIN (GENERIC_PLAN) with partition pruning -- partitions should be pruned at plan time, based on constants, -- but there should be no pruning based on parameter placeholders -- cgit v1.2.3