Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit9d701e6

Browse files
committed
Rework EXPLAIN for planner's buffer usage.
Commitce77abe allowed EXPLAIN (BUFFERS) to report the informationon buffer usage during planning phase. However three issues werereported regarding this feature.(1) Previously, EXPLAIN option BUFFERS required ANALYZE. So the query had to be actually executed by specifying ANALYZE even when we want to see only the planner's buffer usage. This was inconvenient especially when the query was write one like DELETE.(2) EXPLAIN included the planner's buffer usage in summary information. So SUMMARY option had to be enabled to report that. Also this format was confusing.(3) The output structure for planning information was not consistent between TEXT format and the others. For example, "Planning" tag was output in JSON format, but not in TEXT format.For (1), this commit allows us to perform EXPLAIN (BUFFERS) withoutANALYZE to report the planner's buffer usage.For (2), this commit changed EXPLAIN output so that the planner'sbuffer usage is reported before summary information.For (3), this commit made the output structure for planninginformation more consistent between the formats.Back-patch to v13 where the planner's buffer usage was allowed tobe reported in EXPLAIN.Reported-by: Pierre Giraud, David RowleyAuthor: Fujii MasaoReviewed-by: David Rowley, Julien Rouhaud, Pierre GiraudDiscussion:https://postgr.es/m/07b226e6-fa49-687f-b110-b7c37572f69e@dalibo.com
1 parentd259afa commit9d701e6

File tree

4 files changed

+77
-29
lines changed

4 files changed

+77
-29
lines changed

‎doc/src/sgml/ref/explain.sgml

Lines changed: 1 addition & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -187,8 +187,7 @@ ROLLBACK;
187187
query processing.
188188
The number of blocks shown for an
189189
upper-level node includes those used by all its child nodes. In text
190-
format, only non-zero values are printed. This parameter may only be
191-
used when <literal>ANALYZE</literal> is also enabled. It defaults to
190+
format, only non-zero values are printed. It defaults to
192191
<literal>FALSE</literal>.
193192
</para>
194193
</listitem>

‎src/backend/commands/explain.c

Lines changed: 23 additions & 23 deletions
Original file line numberDiff line numberDiff line change
@@ -116,7 +116,8 @@ static void show_instrumentation_count(const char *qlabel, int which,
116116
staticvoidshow_foreignscan_info(ForeignScanState*fsstate,ExplainState*es);
117117
staticvoidshow_eval_params(Bitmapset*bms_params,ExplainState*es);
118118
staticconstchar*explain_get_index_name(OidindexId);
119-
staticvoidshow_buffer_usage(ExplainState*es,constBufferUsage*usage);
119+
staticvoidshow_buffer_usage(ExplainState*es,constBufferUsage*usage,
120+
boolplanning);
120121
staticvoidshow_wal_usage(ExplainState*es,constWalUsage*usage);
121122
staticvoidExplainIndexScanDetails(Oidindexid,ScanDirectionindexorderdir,
122123
ExplainState*es);
@@ -221,11 +222,6 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
221222
parser_errposition(pstate,opt->location)));
222223
}
223224

224-
if (es->buffers&& !es->analyze)
225-
ereport(ERROR,
226-
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
227-
errmsg("EXPLAIN option BUFFERS requires ANALYZE")));
228-
229225
if (es->wal&& !es->analyze)
230226
ereport(ERROR,
231227
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
@@ -586,8 +582,13 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
586582
/* Create textual dump of plan tree */
587583
ExplainPrintPlan(es,queryDesc);
588584

589-
if (es->summary&& (planduration||bufusage))
585+
/* Show buffer usage in planning */
586+
if (bufusage)
587+
{
590588
ExplainOpenGroup("Planning","Planning", true,es);
589+
show_buffer_usage(es,bufusage, true);
590+
ExplainCloseGroup("Planning","Planning", true,es);
591+
}
591592

592593
if (es->summary&&planduration)
593594
{
@@ -596,19 +597,6 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
596597
ExplainPropertyFloat("Planning Time","ms",1000.0*plantime,3,es);
597598
}
598599

599-
/* Show buffer usage */
600-
if (es->summary&&bufusage)
601-
{
602-
if (es->format==EXPLAIN_FORMAT_TEXT)
603-
es->indent++;
604-
show_buffer_usage(es,bufusage);
605-
if (es->format==EXPLAIN_FORMAT_TEXT)
606-
es->indent--;
607-
}
608-
609-
if (es->summary&& (planduration||bufusage))
610-
ExplainCloseGroup("Planning","Planning", true,es);
611-
612600
/* Print info about runtime of triggers */
613601
if (es->analyze)
614602
ExplainPrintTriggers(es,queryDesc);
@@ -1996,7 +1984,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
19961984

19971985
/* Show buffer/WAL usage */
19981986
if (es->buffers&&planstate->instrument)
1999-
show_buffer_usage(es,&planstate->instrument->bufusage);
1987+
show_buffer_usage(es,&planstate->instrument->bufusage, false);
20001988
if (es->wal&&planstate->instrument)
20011989
show_wal_usage(es,&planstate->instrument->walusage);
20021990

@@ -2015,7 +2003,7 @@ ExplainNode(PlanState *planstate, List *ancestors,
20152003

20162004
ExplainOpenWorker(n,es);
20172005
if (es->buffers)
2018-
show_buffer_usage(es,&instrument->bufusage);
2006+
show_buffer_usage(es,&instrument->bufusage, false);
20192007
if (es->wal)
20202008
show_wal_usage(es,&instrument->walusage);
20212009
ExplainCloseWorker(n,es);
@@ -3301,7 +3289,7 @@ explain_get_index_name(Oid indexId)
33013289
* Show buffer usage details.
33023290
*/
33033291
staticvoid
3304-
show_buffer_usage(ExplainState*es,constBufferUsage*usage)
3292+
show_buffer_usage(ExplainState*es,constBufferUsage*usage,boolplanning)
33053293
{
33063294
if (es->format==EXPLAIN_FORMAT_TEXT)
33073295
{
@@ -3317,6 +3305,15 @@ show_buffer_usage(ExplainState *es, const BufferUsage *usage)
33173305
usage->temp_blks_written>0);
33183306
boolhas_timing= (!INSTR_TIME_IS_ZERO(usage->blk_read_time)||
33193307
!INSTR_TIME_IS_ZERO(usage->blk_write_time));
3308+
boolshow_planning= (planning&& (has_shared||
3309+
has_local||has_temp||has_timing));
3310+
3311+
if (show_planning)
3312+
{
3313+
ExplainIndentText(es);
3314+
appendStringInfoString(es->str,"Planning:\n");
3315+
es->indent++;
3316+
}
33203317

33213318
/* Show only positive counter values. */
33223319
if (has_shared||has_local||has_temp)
@@ -3386,6 +3383,9 @@ show_buffer_usage(ExplainState *es, const BufferUsage *usage)
33863383
INSTR_TIME_GET_MILLISEC(usage->blk_write_time));
33873384
appendStringInfoChar(es->str,'\n');
33883385
}
3386+
3387+
if (show_planning)
3388+
es->indent--;
33893389
}
33903390
else
33913391
{

‎src/test/regress/expected/explain.out

Lines changed: 51 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -106,7 +106,6 @@ select explain_filter('explain (analyze, buffers, format json) select * from int
106106
"Temp Written Blocks": N +
107107
}, +
108108
"Planning": { +
109-
"Planning Time": N.N, +
110109
"Shared Hit Blocks": N, +
111110
"Shared Read Blocks": N, +
112111
"Shared Dirtied Blocks": N, +
@@ -118,6 +117,7 @@ select explain_filter('explain (analyze, buffers, format json) select * from int
118117
"Temp Read Blocks": N, +
119118
"Temp Written Blocks": N +
120119
}, +
120+
"Planning Time": N.N, +
121121
"Triggers": [ +
122122
], +
123123
"Execution Time": N.N +
@@ -155,7 +155,6 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8
155155
<Temp-Written-Blocks>N</Temp-Written-Blocks> +
156156
</Plan> +
157157
<Planning> +
158-
<Planning-Time>N.N</Planning-Time> +
159158
<Shared-Hit-Blocks>N</Shared-Hit-Blocks> +
160159
<Shared-Read-Blocks>N</Shared-Read-Blocks> +
161160
<Shared-Dirtied-Blocks>N</Shared-Dirtied-Blocks>+
@@ -167,6 +166,7 @@ select explain_filter('explain (analyze, buffers, format xml) select * from int8
167166
<Temp-Read-Blocks>N</Temp-Read-Blocks> +
168167
<Temp-Written-Blocks>N</Temp-Written-Blocks> +
169168
</Planning> +
169+
<Planning-Time>N.N</Planning-Time> +
170170
<Triggers> +
171171
</Triggers> +
172172
<Execution-Time>N.N</Execution-Time> +
@@ -201,7 +201,6 @@ select explain_filter('explain (analyze, buffers, format yaml) select * from int
201201
Temp Read Blocks: N +
202202
Temp Written Blocks: N +
203203
Planning: +
204-
Planning Time: N.N +
205204
Shared Hit Blocks: N +
206205
Shared Read Blocks: N +
207206
Shared Dirtied Blocks: N +
@@ -212,10 +211,58 @@ select explain_filter('explain (analyze, buffers, format yaml) select * from int
212211
Local Written Blocks: N +
213212
Temp Read Blocks: N +
214213
Temp Written Blocks: N +
214+
Planning Time: N.N +
215215
Triggers: +
216216
Execution Time: N.N
217217
(1 row)
218218

219+
select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
220+
explain_filter
221+
---------------------------------------------------------
222+
Seq Scan on int8_tbl i8 (cost=N.N..N.N rows=N width=N)
223+
(1 row)
224+
225+
select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
226+
explain_filter
227+
------------------------------------
228+
[ +
229+
{ +
230+
"Plan": { +
231+
"Node Type": "Seq Scan", +
232+
"Parallel Aware": false, +
233+
"Relation Name": "int8_tbl",+
234+
"Alias": "i8", +
235+
"Startup Cost": N.N, +
236+
"Total Cost": N.N, +
237+
"Plan Rows": N, +
238+
"Plan Width": N, +
239+
"Shared Hit Blocks": N, +
240+
"Shared Read Blocks": N, +
241+
"Shared Dirtied Blocks": N, +
242+
"Shared Written Blocks": N, +
243+
"Local Hit Blocks": N, +
244+
"Local Read Blocks": N, +
245+
"Local Dirtied Blocks": N, +
246+
"Local Written Blocks": N, +
247+
"Temp Read Blocks": N, +
248+
"Temp Written Blocks": N +
249+
}, +
250+
"Planning": { +
251+
"Shared Hit Blocks": N, +
252+
"Shared Read Blocks": N, +
253+
"Shared Dirtied Blocks": N, +
254+
"Shared Written Blocks": N, +
255+
"Local Hit Blocks": N, +
256+
"Local Read Blocks": N, +
257+
"Local Dirtied Blocks": N, +
258+
"Local Written Blocks": N, +
259+
"Temp Read Blocks": N, +
260+
"Temp Written Blocks": N +
261+
} +
262+
} +
263+
]
264+
(1 row)
265+
219266
-- SETTINGS option
220267
-- We have to ignore other settings that might be imposed by the environment,
221268
-- so printing the whole Settings field unfortunately won't do.
@@ -402,7 +449,6 @@ select jsonb_pretty(
402449
"Shared Written Blocks": 0 +
403450
}, +
404451
"Planning": { +
405-
"Planning Time": 0.0, +
406452
"Local Hit Blocks": 0, +
407453
"Temp Read Blocks": 0, +
408454
"Local Read Blocks": 0, +
@@ -416,6 +462,7 @@ select jsonb_pretty(
416462
}, +
417463
"Triggers": [ +
418464
], +
465+
"Planning Time": 0.0, +
419466
"Execution Time": 0.0 +
420467
} +
421468
]

‎src/test/regress/sql/explain.sql

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -57,6 +57,8 @@ select explain_filter('explain (analyze, buffers, format text) select * from int
5757
select explain_filter('explain (analyze, buffers, format json) select * from int8_tbl i8');
5858
select explain_filter('explain (analyze, buffers, format xml) select * from int8_tbl i8');
5959
select explain_filter('explain (analyze, buffers, format yaml) select * from int8_tbl i8');
60+
select explain_filter('explain (buffers, format text) select * from int8_tbl i8');
61+
select explain_filter('explain (buffers, format json) select * from int8_tbl i8');
6062

6163
-- SETTINGS option
6264
-- We have to ignore other settings that might be imposed by the environment,

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp