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
Appearance settings

Commit 33e05f8

Browse filesBrowse files
author
Amit Kapila
committed
Add the option to report WAL usage in EXPLAIN and auto_explain.
This commit adds a new option WAL similar to existing option BUFFERS in the EXPLAIN command. This option allows to include information on WAL record generation added by commit df3b181 in EXPLAIN output. This also allows the WAL usage information to be displayed via the auto_explain module. A new parameter auto_explain.log_wal controls whether WAL usage statistics are printed when an execution plan is logged. This parameter has no effect unless auto_explain.log_analyze is enabled. Author: Julien Rouhaud Reviewed-by: Dilip Kumar and Amit Kapila Discussion: https://postgr.es/m/CAB-hujrP8ZfUkvL5OYETipQwA=e3n7oqHFU=4ZLxWS_Cza3kQQ@mail.gmail.com
1 parent a40caf5 commit 33e05f8
Copy full SHA for 33e05f8

File tree

Expand file treeCollapse file tree

6 files changed

+124
-6
lines changed
Filter options
Expand file treeCollapse file tree

6 files changed

+124
-6
lines changed

‎contrib/auto_explain/auto_explain.c

Copy file name to clipboardExpand all lines: contrib/auto_explain/auto_explain.c
+15Lines changed: 15 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ static int auto_explain_log_min_duration = -1; /* msec or -1 */
2727
static bool auto_explain_log_analyze = false;
2828
static bool auto_explain_log_verbose = false;
2929
static bool auto_explain_log_buffers = false;
30+
static bool auto_explain_log_wal = false;
3031
static bool auto_explain_log_triggers = false;
3132
static bool auto_explain_log_timing = true;
3233
static bool auto_explain_log_settings = false;
@@ -148,6 +149,17 @@ _PG_init(void)
148149
NULL,
149150
NULL);
150151

152+
DefineCustomBoolVariable("auto_explain.log_wal",
153+
"Log WAL usage.",
154+
NULL,
155+
&auto_explain_log_wal,
156+
false,
157+
PGC_SUSET,
158+
0,
159+
NULL,
160+
NULL,
161+
NULL);
162+
151163
DefineCustomBoolVariable("auto_explain.log_triggers",
152164
"Include trigger statistics in plans.",
153165
"This has no effect unless log_analyze is also set.",
@@ -280,6 +292,8 @@ explain_ExecutorStart(QueryDesc *queryDesc, int eflags)
280292
queryDesc->instrument_options |= INSTRUMENT_ROWS;
281293
if (auto_explain_log_buffers)
282294
queryDesc->instrument_options |= INSTRUMENT_BUFFERS;
295+
if (auto_explain_log_wal)
296+
queryDesc->instrument_options |= INSTRUMENT_WAL;
283297
}
284298
}
285299

@@ -374,6 +388,7 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
374388
es->analyze = (queryDesc->instrument_options && auto_explain_log_analyze);
375389
es->verbose = auto_explain_log_verbose;
376390
es->buffers = (es->analyze && auto_explain_log_buffers);
391+
es->wal = (es->analyze && auto_explain_log_wal);
377392
es->timing = (es->analyze && auto_explain_log_timing);
378393
es->summary = es->analyze;
379394
es->format = auto_explain_log_format;

‎doc/src/sgml/auto-explain.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/auto-explain.sgml
+20Lines changed: 20 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -109,6 +109,26 @@ LOAD 'auto_explain';
109109
</listitem>
110110
</varlistentry>
111111

112+
<varlistentry>
113+
<term>
114+
<varname>auto_explain.log_wal</varname> (<type>boolean</type>)
115+
<indexterm>
116+
<primary><varname>auto_explain.log_wal</varname> configuration parameter</primary>
117+
</indexterm>
118+
</term>
119+
<listitem>
120+
<para>
121+
<varname>auto_explain.log_wal</varname> controls whether WAL
122+
usage statistics are printed when an execution plan is logged; it's
123+
equivalent to the <literal>WAL</literal> option of <command>EXPLAIN</command>.
124+
This parameter has no effect
125+
unless <varname>auto_explain.log_analyze</varname> is enabled.
126+
This parameter is off by default.
127+
Only superusers can change this setting.
128+
</para>
129+
</listitem>
130+
</varlistentry>
131+
112132
<varlistentry>
113133
<term>
114134
<varname>auto_explain.log_timing</varname> (<type>boolean</type>)

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

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/explain.sgml
+14Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -41,6 +41,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replac
4141
COSTS [ <replaceable class="parameter">boolean</replaceable> ]
4242
SETTINGS [ <replaceable class="parameter">boolean</replaceable> ]
4343
BUFFERS [ <replaceable class="parameter">boolean</replaceable> ]
44+
WAL [ <replaceable class="parameter">boolean</replaceable> ]
4445
TIMING [ <replaceable class="parameter">boolean</replaceable> ]
4546
SUMMARY [ <replaceable class="parameter">boolean</replaceable> ]
4647
FORMAT { TEXT | XML | JSON | YAML }
@@ -192,6 +193,19 @@ ROLLBACK;
192193
</listitem>
193194
</varlistentry>
194195

196+
<varlistentry>
197+
<term><literal>WAL</literal></term>
198+
<listitem>
199+
<para>
200+
Include information on WAL record generation. Specifically, include the
201+
number of records, number of full page image records and amount of WAL
202+
bytes generated. In text format, only non-zero values are printed. This
203+
parameter may only be used when <literal>ANALYZE</literal> is also
204+
enabled. It defaults to <literal>FALSE</literal>.
205+
</para>
206+
</listitem>
207+
</varlistentry>
208+
195209
<varlistentry>
196210
<term><literal>TIMING</literal></term>
197211
<listitem>

‎src/backend/commands/explain.c

Copy file name to clipboardExpand all lines: src/backend/commands/explain.c
+70-4Lines changed: 70 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -113,6 +113,7 @@ static void show_foreignscan_info(ForeignScanState *fsstate, ExplainState *es);
113113
static void show_eval_params(Bitmapset *bms_params, ExplainState *es);
114114
static const char *explain_get_index_name(Oid indexId);
115115
static void show_buffer_usage(ExplainState *es, const BufferUsage *usage);
116+
static void show_wal_usage(ExplainState *es, const WalUsage *usage);
116117
static void ExplainIndexScanDetails(Oid indexid, ScanDirection indexorderdir,
117118
ExplainState *es);
118119
static void ExplainScanTarget(Scan *plan, ExplainState *es);
@@ -175,6 +176,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
175176
es->costs = defGetBoolean(opt);
176177
else if (strcmp(opt->defname, "buffers") == 0)
177178
es->buffers = defGetBoolean(opt);
179+
else if (strcmp(opt->defname, "wal") == 0)
180+
es->wal = defGetBoolean(opt);
178181
else if (strcmp(opt->defname, "settings") == 0)
179182
es->settings = defGetBoolean(opt);
180183
else if (strcmp(opt->defname, "timing") == 0)
@@ -219,6 +222,11 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
219222
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
220223
errmsg("EXPLAIN option BUFFERS requires ANALYZE")));
221224

225+
if (es->wal && !es->analyze)
226+
ereport(ERROR,
227+
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
228+
errmsg("EXPLAIN option WAL requires ANALYZE")));
229+
222230
/* if the timing was not set explicitly, set default value */
223231
es->timing = (timing_set) ? es->timing : es->analyze;
224232

@@ -506,6 +514,8 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
506514

507515
if (es->buffers)
508516
instrument_option |= INSTRUMENT_BUFFERS;
517+
if (es->wal)
518+
instrument_option |= INSTRUMENT_WAL;
509519

510520
/*
511521
* We always collect timing for the entire statement, even when node-level
@@ -1970,12 +1980,14 @@ ExplainNode(PlanState *planstate, List *ancestors,
19701980
}
19711981
}
19721982

1973-
/* Show buffer usage */
1983+
/* Show buffer/WAL usage */
19741984
if (es->buffers && planstate->instrument)
19751985
show_buffer_usage(es, &planstate->instrument->bufusage);
1986+
if (es->wal && planstate->instrument)
1987+
show_wal_usage(es, &planstate->instrument->walusage);
19761988

1977-
/* Prepare per-worker buffer usage */
1978-
if (es->workers_state && es->buffers && es->verbose)
1989+
/* Prepare per-worker buffer/WAL usage */
1990+
if (es->workers_state && (es->buffers || es->wal) && es->verbose)
19791991
{
19801992
WorkerInstrumentation *w = planstate->worker_instrument;
19811993

@@ -1988,7 +2000,10 @@ ExplainNode(PlanState *planstate, List *ancestors,
19882000
continue;
19892001

19902002
ExplainOpenWorker(n, es);
1991-
show_buffer_usage(es, &instrument->bufusage);
2003+
if (es->buffers)
2004+
show_buffer_usage(es, &instrument->bufusage);
2005+
if (es->wal)
2006+
show_wal_usage(es, &instrument->walusage);
19922007
ExplainCloseWorker(n, es);
19932008
}
19942009
}
@@ -3087,6 +3102,44 @@ show_buffer_usage(ExplainState *es, const BufferUsage *usage)
30873102
}
30883103
}
30893104

3105+
/*
3106+
* Show WAL usage details.
3107+
*/
3108+
static void
3109+
show_wal_usage(ExplainState *es, const WalUsage *usage)
3110+
{
3111+
if (es->format == EXPLAIN_FORMAT_TEXT)
3112+
{
3113+
/* Show only positive counter values. */
3114+
if ((usage->wal_records > 0) || (usage->wal_num_fpw > 0) ||
3115+
(usage->wal_bytes > 0))
3116+
{
3117+
ExplainIndentText(es);
3118+
appendStringInfoString(es->str, "WAL:");
3119+
3120+
if (usage->wal_records > 0)
3121+
appendStringInfo(es->str, " records=%ld",
3122+
usage->wal_records);
3123+
if (usage->wal_num_fpw > 0)
3124+
appendStringInfo(es->str, " full page writes=%ld",
3125+
usage->wal_num_fpw);
3126+
if (usage->wal_bytes > 0)
3127+
appendStringInfo(es->str, " bytes=" UINT64_FORMAT,
3128+
usage->wal_bytes);
3129+
appendStringInfoChar(es->str, '\n');
3130+
}
3131+
}
3132+
else
3133+
{
3134+
ExplainPropertyInteger("WAL records", NULL,
3135+
usage->wal_records, es);
3136+
ExplainPropertyInteger("WAL full page writes", NULL,
3137+
usage->wal_num_fpw, es);
3138+
ExplainPropertyUInteger("WAL bytes", NULL,
3139+
usage->wal_bytes, es);
3140+
}
3141+
}
3142+
30903143
/*
30913144
* Add some additional details about an IndexScan or IndexOnlyScan
30923145
*/
@@ -3871,6 +3924,19 @@ ExplainPropertyInteger(const char *qlabel, const char *unit, int64 value,
38713924
ExplainProperty(qlabel, unit, buf, true, es);
38723925
}
38733926

3927+
/*
3928+
* Explain an unsigned integer-valued property.
3929+
*/
3930+
void
3931+
ExplainPropertyUInteger(const char *qlabel, const char *unit, uint64 value,
3932+
ExplainState *es)
3933+
{
3934+
char buf[32];
3935+
3936+
snprintf(buf, sizeof(buf), UINT64_FORMAT, value);
3937+
ExplainProperty(qlabel, unit, buf, true, es);
3938+
}
3939+
38743940
/*
38753941
* Explain a float-valued property, using the specified number of
38763942
* fractional digits.

‎src/bin/psql/tab-complete.c

Copy file name to clipboardExpand all lines: src/bin/psql/tab-complete.c
+2-2Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -3045,8 +3045,8 @@ psql_completion(const char *text, int start, int end)
30453045
*/
30463046
if (ends_with(prev_wd, '(') || ends_with(prev_wd, ','))
30473047
COMPLETE_WITH("ANALYZE", "VERBOSE", "COSTS", "SETTINGS",
3048-
"BUFFERS", "TIMING", "SUMMARY", "FORMAT");
3049-
else if (TailMatches("ANALYZE|VERBOSE|COSTS|SETTINGS|BUFFERS|TIMING|SUMMARY"))
3048+
"BUFFERS", "WAL", "TIMING", "SUMMARY", "FORMAT");
3049+
else if (TailMatches("ANALYZE|VERBOSE|COSTS|SETTINGS|BUFFERS|WAL|TIMING|SUMMARY"))
30503050
COMPLETE_WITH("ON", "OFF");
30513051
else if (TailMatches("FORMAT"))
30523052
COMPLETE_WITH("TEXT", "XML", "JSON", "YAML");

‎src/include/commands/explain.h

Copy file name to clipboardExpand all lines: src/include/commands/explain.h
+3Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,6 +42,7 @@ typedef struct ExplainState
4242
bool analyze; /* print actual times */
4343
bool costs; /* print estimated costs */
4444
bool buffers; /* print buffer usage */
45+
bool wal; /* print WAL usage */
4546
bool timing; /* print detailed node timing */
4647
bool summary; /* print total planning and execution timing */
4748
bool settings; /* print modified settings */
@@ -111,6 +112,8 @@ extern void ExplainPropertyText(const char *qlabel, const char *value,
111112
ExplainState *es);
112113
extern void ExplainPropertyInteger(const char *qlabel, const char *unit,
113114
int64 value, ExplainState *es);
115+
extern void ExplainPropertyUInteger(const char *qlabel, const char *unit,
116+
uint64 value, ExplainState *es);
114117
extern void ExplainPropertyFloat(const char *qlabel, const char *unit,
115118
double value, int ndigits, ExplainState *es);
116119
extern void ExplainPropertyBool(const char *qlabel, bool value,

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.