On 2020/08/20 13:00, David Rowley wrote:
On Thu, 20 Aug 2020 at 03:31, Fujii Masao <masao.fu...@oss.nttdata.com> wrote:
With the patch, for example, whatever "summary" settng is, "buffers on"
displays the planner's buffer usage if it happens.
I forgot to mention earlier, you'll also need to remove the part in
the docs that mentions BUFFERS requires ANALYZE.
Thanks for the review! I removed that.
Attached is the updated version of the patch.
I also added the regression test performing "explain (buffers on)"
without "analyze" option.
Regards,
--
Fujii Masao
Advanced Computing Technology Center
Research and Development Headquarters
NTT DATA CORPORATION
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index 1c19e254dc..906b2ccd50 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -187,8 +187,7 @@ ROLLBACK;
query processing.
The number of blocks shown for an
upper-level node includes those used by all its child nodes. In text
- format, only non-zero values are printed. This parameter may only be
- used when <literal>ANALYZE</literal> is also enabled. It defaults to
+ format, only non-zero values are printed. It defaults to
<literal>FALSE</literal>.
</para>
</listitem>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 30e0a7ee7f..c98c9b5547 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -116,7 +116,8 @@ static void show_instrumentation_count(const char *qlabel,
int which,
static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es);
static void show_eval_params(Bitmapset *bms_params, ExplainState *es);
static const char *explain_get_index_name(Oid indexId);
-static void show_buffer_usage(ExplainState *es, const BufferUsage *usage);
+static void show_buffer_usage(ExplainState *es, const BufferUsage *usage,
+ bool planning);
static void show_wal_usage(ExplainState *es, const WalUsage *usage);
static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir,
ExplainState *es);
@@ -221,11 +222,6 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
parser_errposition(pstate,
opt->location)));
}
- if (es->buffers && !es->analyze)
- ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("EXPLAIN option BUFFERS requires
ANALYZE")));
-
if (es->wal && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -586,8 +582,13 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into,
ExplainState *es,
/* Create textual dump of plan tree */
ExplainPrintPlan(es, queryDesc);
- if (es->summary && (planduration || bufusage))
+ /* Show buffer usage in planning */
+ if (bufusage)
+ {
ExplainOpenGroup("Planning", "Planning", true, es);
+ show_buffer_usage(es, bufusage, true);
+ ExplainCloseGroup("Planning", "Planning", true, es);
+ }
if (es->summary && planduration)
{
@@ -596,19 +597,6 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into,
ExplainState *es,
ExplainPropertyFloat("Planning Time", "ms", 1000.0 * plantime,
3, es);
}
- /* Show buffer usage */
- if (es->summary && bufusage)
- {
- if (es->format == EXPLAIN_FORMAT_TEXT)
- es->indent++;
- show_buffer_usage(es, bufusage);
- if (es->format == EXPLAIN_FORMAT_TEXT)
- es->indent--;
- }
-
- if (es->summary && (planduration || bufusage))
- ExplainCloseGroup("Planning", "Planning", true, es);
-
/* Print info about runtime of triggers */
if (es->analyze)
ExplainPrintTriggers(es, queryDesc);
@@ -1996,7 +1984,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
/* Show buffer/WAL usage */
if (es->buffers && planstate->instrument)
- show_buffer_usage(es, &planstate->instrument->bufusage);
+ show_buffer_usage(es, &planstate->instrument->bufusage, false);
if (es->wal && planstate->instrument)
show_wal_usage(es, &planstate->instrument->walusage);
@@ -2015,7 +2003,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
ExplainOpenWorker(n, es);
if (es->buffers)
- show_buffer_usage(es, &instrument->bufusage);
+ show_buffer_usage(es, &instrument->bufusage,
false);
if (es->wal)
show_wal_usage(es, &instrument->walusage);
ExplainCloseWorker(n, es);
@@ -3301,7 +3289,7 @@ explain_get_index_name(Oid indexId)
* Show buffer usage details.
*/
static void
-show_buffer_usage(ExplainState *es, const BufferUsage *usage)
+show_buffer_usage(ExplainState *es, const BufferUsage *usage, bool planning)
{
if (es->format == EXPLAIN_FORMAT_TEXT)
{
@@ -3317,6 +3305,15 @@ show_buffer_usage(ExplainState *es, const BufferUsage
*usage)
usage->temp_blks_written > 0);
bool has_timing =
(!INSTR_TIME_IS_ZERO(usage->blk_read_time) ||
!INSTR_TIME_IS_ZERO(usage->blk_write_time));
+ bool show_planning = (planning && (has_shared ||
+
has_local || has_temp || has_timing));
+
+ if (show_planning)
+ {
+ ExplainIndentText(es);
+ appendStringInfoString(es->str, "Planning:\n");
+ es->indent++;
+ }
/* Show only positive counter values. */
if (has_shared || has_local || has_temp)
@@ -3386,6 +3383,9 @@ show_buffer_usage(ExplainState *es, const BufferUsage
*usage)
INSTR_TIME_GET_MILLISEC(usage->blk_write_time));
appendStringInfoChar(es->str, '\n');
}
+
+ if (show_planning)
+ es->indent--;
}
else
{
diff --git a/src/test/regress/expected/explain.out
b/src/test/regress/expected/explain.out
index 96baba038c..a1ee6c6792 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -106,7 +106,6 @@ select explain_filter('explain (analyze, buffers, format
json) select * from int
"Temp Written Blocks": N +
}, +
"Planning": { +
- "Planning Time": N.N, +
"Shared Hit Blocks": N, +
"Shared Read Blocks": N, +
"Shared Dirtied Blocks": N, +
@@ -118,6 +117,7 @@ select explain_filter('explain (analyze, buffers, format
json) select * from int
"Temp Read Blocks": N, +
"Temp Written Blocks": N +
}, +
+ "Planning Time": N.N, +
"Triggers": [ +
], +
"Execution Time": N.N +
@@ -155,7 +155,6 @@ select explain_filter('explain (analyze, buffers, format
xml) select * from int8
<Temp-Written-Blocks>N</Temp-Written-Blocks> +
</Plan> +
<Planning> +
- <Planning-Time>N.N</Planning-Time> +
<Shared-Hit-Blocks>N</Shared-Hit-Blocks> +
<Shared-Read-Blocks>N</Shared-Read-Blocks> +
<Shared-Dirtied-Blocks>N</Shared-Dirtied-Blocks>+
@@ -167,6 +166,7 @@ select explain_filter('explain (analyze, buffers, format
xml) select * from int8
<Temp-Read-Blocks>N</Temp-Read-Blocks> +
<Temp-Written-Blocks>N</Temp-Written-Blocks> +
</Planning> +
+ <Planning-Time>N.N</Planning-Time> +
<Triggers> +
</Triggers> +
<Execution-Time>N.N</Execution-Time> +
@@ -201,7 +201,6 @@ select explain_filter('explain (analyze, buffers, format
yaml) select * from int
Temp Read Blocks: N +
Temp Written Blocks: N +
Planning: +
- Planning Time: N.N +
Shared Hit Blocks: N +
Shared Read Blocks: N +
Shared Dirtied Blocks: N +
@@ -212,10 +211,58 @@ select explain_filter('explain (analyze, buffers, format
yaml) select * from int
Local Written Blocks: N +
Temp Read Blocks: N +
Temp Written Blocks: N +
+ Planning Time: N.N +
Triggers: +
Execution Time: N.N
(1 row)
+select explain_filter('explain (buffers, format text) select * from int8_tbl
i8');
+ explain_filter
+---------------------------------------------------------
+ Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
+(1 row)
+
+select explain_filter('explain (buffers, format json) select * from int8_tbl
i8');
+ explain_filter
+------------------------------------
+ [ +
+ { +
+ "Plan": { +
+ "Node Type": "Seq Scan", +
+ "Parallel Aware": false, +
+ "Relation Name": "int8_tbl",+
+ "Alias": "i8", +
+ "Startup Cost": N.N, +
+ "Total Cost": N.N, +
+ "Plan Rows": N, +
+ "Plan Width": N, +
+ "Shared Hit Blocks": N, +
+ "Shared Read Blocks": N, +
+ "Shared Dirtied Blocks": N, +
+ "Shared Written Blocks": N, +
+ "Local Hit Blocks": N, +
+ "Local Read Blocks": N, +
+ "Local Dirtied Blocks": N, +
+ "Local Written Blocks": N, +
+ "Temp Read Blocks": N, +
+ "Temp Written Blocks": N +
+ }, +
+ "Planning": { +
+ "Shared Hit Blocks": N, +
+ "Shared Read Blocks": N, +
+ "Shared Dirtied Blocks": N, +
+ "Shared Written Blocks": N, +
+ "Local Hit Blocks": N, +
+ "Local Read Blocks": N, +
+ "Local Dirtied Blocks": N, +
+ "Local Written Blocks": N, +
+ "Temp Read Blocks": N, +
+ "Temp Written Blocks": N +
+ } +
+ } +
+ ]
+(1 row)
+
-- SETTINGS option
-- We have to ignore other settings that might be imposed by the environment,
-- so printing the whole Settings field unfortunately won't do.
@@ -402,7 +449,6 @@ select jsonb_pretty(
"Shared Written Blocks": 0 +
}, +
"Planning": { +
- "Planning Time": 0.0, +
"Local Hit Blocks": 0, +
"Temp Read Blocks": 0, +
"Local Read Blocks": 0, +
@@ -416,6 +462,7 @@ select jsonb_pretty(
}, +
"Triggers": [ +
], +
+ "Planning Time": 0.0, +
"Execution Time": 0.0 +
} +
]
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index dce2a34207..01783c607a 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -57,6 +57,8 @@ select explain_filter('explain (analyze, buffers, format
text) select * from int
select explain_filter('explain (analyze, buffers, format json) select * from
int8_tbl i8');
select explain_filter('explain (analyze, buffers, format xml) select * from
int8_tbl i8');
select explain_filter('explain (analyze, buffers, format yaml) select * from
int8_tbl i8');
+select explain_filter('explain (buffers, format text) select * from int8_tbl
i8');
+select explain_filter('explain (buffers, format json) select * from int8_tbl
i8');
-- SETTINGS option
-- We have to ignore other settings that might be imposed by the environment,
