Here is a patch to provide default gucs for EXPLAIN options.
I have two goals with this patch. The first is that I personally
*always* want BUFFERS turned on, so this would allow me to do it without
typing it every time.
The second is it would make it easier to change the actual default for
settings if we choose to do so because users would be able to switch it
back if they prefer.
The patch is based off of a995b371ae.
--
Vik Fearing
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index a2694e548a..ca2769b02a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -5308,6 +5308,141 @@ ANY <replaceable class="parameter">num_sync</replaceable> ( <replaceable class="
</variablelist>
</sect2>
+
+ <sect2 id="runtime-config-query-explain">
+ <title>Default EXPLAIN options</title>
+
+ <variablelist>
+
+ <varlistentry id="guc-default-explain-analyze" xreflabel="default_explain_analyze">
+ <term><varname>default_explain_analyze</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_analyze</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>ANALYZE</command> option for <command>EXPLAIN</command>.
+ The default is off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-buffers" xreflabel="default_explain_buffers">
+ <term><varname>default_explain_buffers</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_buffers</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>BUFFERS</command> option for <command>EXPLAIN</command>.
+ The default is off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-cost" xreflabel="default_explain_cost">
+ <term><varname>default_explain_cost</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_cost</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>COST</command> option for <command>EXPLAIN</command>.
+ The default is on.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-format" xreflabel="default_explain_format">
+ <term><varname>default_explain_format</varname> (<type>text</type>)
+ <indexterm>
+ <primary><varname>default_explain_format</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>FORMAT</command> option for <command>EXPLAIN</command>.
+ The default is text.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-settings" xreflabel="default_explain_settings">
+ <term><varname>default_explain_settings</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_settings</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>SETTINGS</command> option for <command>EXPLAIN</command>.
+ The default is off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-summary" xreflabel="default_explain_summary">
+ <term><varname>default_explain_summary</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_summary</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>SUMMARY</command> option for <command>EXPLAIN</command>.
+ The default is off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-timing" xreflabel="default_explain_timing">
+ <term><varname>default_explain_timing</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_timing</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>TIMING</command> option for <command>EXPLAIN</command>.
+ The default is on.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-verbose" xreflabel="default_explain_verbose">
+ <term><varname>default_explain_verbose</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_verbose</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>VERBOSE</command> option for <command>EXPLAIN</command>.
+ The default is off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="guc-default-explain-wal" xreflabel="default_explain_wal">
+ <term><varname>default_explain_wal</varname> (<type>boolean</type>)
+ <indexterm>
+ <primary><varname>default_explain_wal</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Sets the default <command>WAL</command> option for <command>EXPLAIN</command>.
+ The default is off.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </sect2>
+
<sect2 id="runtime-config-query-other">
<title>Other Planner Options</title>
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index efd7201d61..974de580bb 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -17,6 +17,7 @@
#include "catalog/pg_type.h"
#include "commands/createas.h"
#include "commands/defrem.h"
+#include "commands/explain.h"
#include "commands/prepare.h"
#include "executor/nodeHash.h"
#include "foreign/fdwapi.h"
@@ -39,6 +40,26 @@
#include "utils/typcache.h"
#include "utils/xml.h"
+/*
+ * User-tweakable parameters
+ */
+bool default_explain_analyze = false;
+bool default_explain_buffers = false;
+bool default_explain_costs = true;
+int default_explain_format = EXPLAIN_FORMAT_TEXT;
+bool default_explain_settings = false;
+bool default_explain_summary = false;
+bool default_explain_timing = true;
+bool default_explain_verbose = false;
+bool default_explain_wal = false;
+
+const struct config_enum_entry explain_format_options[] = {
+ {"text", EXPLAIN_FORMAT_TEXT, false},
+ {"xml", EXPLAIN_FORMAT_XML, false},
+ {"json", EXPLAIN_FORMAT_JSON, false},
+ {"yaml", EXPLAIN_FORMAT_YAML, false},
+ {NULL, 0, false}
+};
/* Hook for plugins to get control in ExplainOneQuery() */
ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL;
@@ -164,8 +185,26 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
TupOutputState *tstate;
List *rewritten;
ListCell *lc;
- bool timing_set = false;
- bool summary_set = false;
+ DefElem *danalyze = NULL;
+ DefElem *dbuffers = NULL;
+ DefElem *dcosts = NULL;
+ DefElem *dformat = NULL;
+ DefElem *dsettings = NULL;
+ DefElem *dsummary = NULL;
+ DefElem *dtiming = NULL;
+ DefElem *dverbose = NULL;
+ DefElem *dwal = NULL;
+
+ /* Set defaults. */
+ es->analyze = default_explain_analyze;
+ es->buffers = default_explain_buffers;
+ es->costs = default_explain_costs;
+ es->format = default_explain_format;
+ es->settings = default_explain_settings;
+ es->summary = default_explain_summary;
+ es->timing = default_explain_timing;
+ es->verbose = default_explain_verbose;
+ es->wal = default_explain_wal;
/* Parse options list. */
foreach(lc, stmt->options)
@@ -173,30 +212,46 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
DefElem *opt = (DefElem *) lfirst(lc);
if (strcmp(opt->defname, "analyze") == 0)
+ {
+ if (danalyze)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ danalyze = opt;
es->analyze = defGetBoolean(opt);
- else if (strcmp(opt->defname, "verbose") == 0)
- es->verbose = defGetBoolean(opt);
- else if (strcmp(opt->defname, "costs") == 0)
- es->costs = defGetBoolean(opt);
+ }
else if (strcmp(opt->defname, "buffers") == 0)
+ {
+ if (dbuffers)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dbuffers = opt;
es->buffers = defGetBoolean(opt);
- else if (strcmp(opt->defname, "wal") == 0)
- es->wal = defGetBoolean(opt);
- else if (strcmp(opt->defname, "settings") == 0)
- es->settings = defGetBoolean(opt);
- else if (strcmp(opt->defname, "timing") == 0)
- {
- timing_set = true;
- es->timing = defGetBoolean(opt);
}
- else if (strcmp(opt->defname, "summary") == 0)
+ else if (strcmp(opt->defname, "costs") == 0)
{
- summary_set = true;
- es->summary = defGetBoolean(opt);
+ if (dcosts)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dcosts = opt;
+ es->costs = defGetBoolean(opt);
}
else if (strcmp(opt->defname, "format") == 0)
{
- char *p = defGetString(opt);
+ char *p;
+
+ if (dformat)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dformat = opt;
+ p = defGetString(opt);
if (strcmp(p, "text") == 0)
es->format = EXPLAIN_FORMAT_TEXT;
@@ -213,6 +268,56 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
opt->defname, p),
parser_errposition(pstate, opt->location)));
}
+ else if (strcmp(opt->defname, "settings") == 0)
+ {
+ if (dsettings)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dsettings = opt;
+ es->settings = defGetBoolean(opt);
+ }
+ else if (strcmp(opt->defname, "summary") == 0)
+ {
+ if (dsummary)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dsummary = opt;
+ es->summary = defGetBoolean(opt);
+ }
+ else if (strcmp(opt->defname, "timing") == 0)
+ {
+ if (dtiming)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dtiming = opt;
+ es->timing = defGetBoolean(opt);
+ }
+ else if (strcmp(opt->defname, "verbose") == 0)
+ {
+ if (dverbose)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dverbose = opt;
+ es->verbose = defGetBoolean(opt);
+ }
+ else if (strcmp(opt->defname, "wal") == 0)
+ {
+ if (dwal)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options"),
+ parser_errposition(pstate, opt->location)));
+ dwal = opt;
+ es->wal = defGetBoolean(opt);
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
@@ -221,27 +326,41 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
parser_errposition(pstate, opt->location)));
}
+ /*
+ * If ANALYZE wasn't explicitly set and isn't on by default, turn off some
+ * settings that aren't also explicitly set. This is so we can raise errors
+ * on incompatible options.
+ */
+ if (!danalyze && !es->analyze)
+ {
+ if (!dbuffers)
+ es->buffers = false;
+ if (!dtiming)
+ es->timing = false;
+ if (!dwal)
+ es->wal = false;
+ }
+
+ /* check that BUFFERS is used with EXPLAIN ANALYZE */
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),
- errmsg("EXPLAIN option WAL requires ANALYZE")));
-
- /* if the timing was not set explicitly, set default value */
- es->timing = (timing_set) ? es->timing : es->analyze;
-
- /* check that timing is used with EXPLAIN ANALYZE */
+ /* check that TIMING is used with EXPLAIN ANALYZE */
if (es->timing && !es->analyze)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN option TIMING requires ANALYZE")));
- /* if the summary was not set explicitly, set default value */
- es->summary = (summary_set) ? es->summary : es->analyze;
+ /* check that WAL is used with EXPLAIN ANALYZE */
+ if (es->wal && !es->analyze)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("EXPLAIN option WAL requires ANALYZE")));
+
+ /* if SUMMARY was not set explicitly, set default value */
+ es->summary = (dsummary) ? es->summary : es->analyze;
/*
* Parse analysis was done already, but we still have to run the rule
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 2f3e0a70e0..349946eb89 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -38,6 +38,7 @@
#include "catalog/pg_authid.h"
#include "catalog/storage.h"
#include "commands/async.h"
+#include "commands/explain.h"
#include "commands/prepare.h"
#include "commands/trigger.h"
#include "commands/user.h"
@@ -511,6 +512,7 @@ extern const struct config_enum_entry archive_mode_options[];
extern const struct config_enum_entry recovery_target_action_options[];
extern const struct config_enum_entry sync_method_options[];
extern const struct config_enum_entry dynamic_shared_memory_options[];
+extern const struct config_enum_entry explain_format_options[];
/*
* GUC option variables that are exported from this module
@@ -725,6 +727,8 @@ const char *const config_group_names[] =
gettext_noop("Replication / Subscribers"),
/* QUERY_TUNING */
gettext_noop("Query Tuning"),
+ /* QUERY_TUNING_EXPLAIN */
+ gettext_noop("Query Tuning / EXPLAIN Options"),
/* QUERY_TUNING_METHOD */
gettext_noop("Query Tuning / Planner Method Configuration"),
/* QUERY_TUNING_COST */
@@ -931,6 +935,78 @@ static const unit_conversion time_unit_conversion_table[] =
static struct config_bool ConfigureNamesBool[] =
{
+ {
+ {"default_explain_analyze", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default ANALYZE option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_analyze,
+ false,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_buffers", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default BUFFERS option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_buffers,
+ false,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_costs", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default COSTS option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_costs,
+ true,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_settings", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default SETTINGS option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_settings,
+ false,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_summary", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default SUMMARY option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_summary,
+ false,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_timing", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default TIMING option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_timing,
+ true,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_verbose", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default VERBOSE option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_verbose,
+ false,
+ NULL, NULL, NULL
+ },
+ {
+ {"default_explain_wal", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default WAL option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_wal,
+ false,
+ NULL, NULL, NULL
+ },
{
{"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD,
gettext_noop("Enables the planner's use of sequential-scan plans."),
@@ -4490,6 +4566,16 @@ static struct config_enum ConfigureNamesEnum[] =
NULL, NULL, NULL
},
+ {
+ {"default_explain_format", PGC_USERSET, QUERY_TUNING_EXPLAIN,
+ gettext_noop("Sets the default FORMAT option for EXPLAIN."),
+ NULL
+ },
+ &default_explain_format,
+ EXPLAIN_FORMAT_TEXT, explain_format_options,
+ NULL, NULL, NULL
+ },
+
{
{"default_transaction_isolation", PGC_USERSET, CLIENT_CONN_STATEMENT,
gettext_noop("Sets the transaction isolation level of each new transaction."),
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 995b6ca155..3bcf399ba3 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -414,6 +414,18 @@
#plan_cache_mode = auto # auto, force_generic_plan or
# force_custom_plan
+# - EXPLAIN Options -
+
+#default_explain_analyze = off
+#default_explain_buffers = off
+#default_explain_costs = on
+#default_explain_format = text
+#default_explain_settings = off
+#default_explain_summary = off
+#default_explain_timing = on
+#default_explain_verbose = off
+#default_explain_wal = off
+
#------------------------------------------------------------------------------
# REPORTING AND LOGGING
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index ba661d32a6..9156f55920 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -124,4 +124,17 @@ extern void ExplainOpenGroup(const char *objtype, const char *labelname,
extern void ExplainCloseGroup(const char *objtype, const char *labelname,
bool labeled, ExplainState *es);
+/*
+ * User-tweakable parameters
+ */
+extern PGDLLEXPORT bool default_explain_analyze;
+extern PGDLLEXPORT bool default_explain_buffers;
+extern PGDLLEXPORT bool default_explain_costs;
+extern PGDLLEXPORT int default_explain_format;
+extern PGDLLEXPORT bool default_explain_settings;
+extern PGDLLEXPORT bool default_explain_summary;
+extern PGDLLEXPORT bool default_explain_timing;
+extern PGDLLEXPORT bool default_explain_verbose;
+extern PGDLLEXPORT bool default_explain_wal;
+
#endif /* EXPLAIN_H */
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index 454c2df487..f31cfa7ddf 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -77,6 +77,7 @@ enum config_group
REPLICATION_STANDBY,
REPLICATION_SUBSCRIBERS,
QUERY_TUNING,
+ QUERY_TUNING_EXPLAIN,
QUERY_TUNING_METHOD,
QUERY_TUNING_COST,
QUERY_TUNING_GEQO,
