Subject: Re: explain plans with information about (modified) gucs
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Jim Finnerty <jfinnert@amazon.com>, pgsql-hackers@postgresql.org
References: <e1791b4c-df9c-be02-edc5-7c8874944be0@2ndquadrant.com>
 <1544792718021-0.post@n3.nabble.com>
 <847526e0-2e15-29da-9488-ab387b9762c8@2ndquadrant.com>
Message-ID: <9fd6dc95-738b-4b06-9616-844a4fc141b8@2ndquadrant.com>
Date: Fri, 14 Dec 2018 16:18:37 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.1
MIME-Version: 1.0
In-Reply-To: <847526e0-2e15-29da-9488-ab387b9762c8@2ndquadrant.com>
Content-Type: multipart/mixed;
 boundary="------------D57F36AD70C9A1B06AF8D66B"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------D57F36AD70C9A1B06AF8D66B
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit


On 12/14/18 3:01 PM, Tomas Vondra wrote:
> On 12/14/18 2:05 PM, Jim Finnerty wrote:
>> You might want to only include the GUCs that are query tuning parameters,
>> i.e., those returned by:
>>
>> SELECT name, setting, category
>> FROM pg_settings
>> WHERE category LIKE 'Query Tuning%'
>> ORDER BY category, name;
>>
> 
> Good idea! Thanks.

V2 filtering the options to QUERY_TUNING group (and subgroups).

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--------------D57F36AD70C9A1B06AF8D66B
Content-Type: text/x-patch;
 name="explain-with-gucs-v2.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="explain-with-gucs-v2.diff"

diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c
index 646cd0d42c..ec44c59b7f 100644
--- a/contrib/auto_explain/auto_explain.c
+++ b/contrib/auto_explain/auto_explain.c
@@ -28,6 +28,7 @@ static bool auto_explain_log_verbose = false;
 static bool auto_explain_log_buffers = false;
 static bool auto_explain_log_triggers = false;
 static bool auto_explain_log_timing = true;
+static bool auto_explain_log_gucs = false;
 static int	auto_explain_log_format = EXPLAIN_FORMAT_TEXT;
 static int	auto_explain_log_level = LOG;
 static bool auto_explain_log_nested_statements = false;
@@ -112,6 +113,17 @@ _PG_init(void)
 							 NULL,
 							 NULL);
 
+	DefineCustomBoolVariable("auto_explain.log_gucs",
+							 "Print modified GUC values.",
+							 NULL,
+							 &auto_explain_log_gucs,
+							 false,
+							 PGC_SUSET,
+							 0,
+							 NULL,
+							 NULL,
+							 NULL);
+
 	DefineCustomBoolVariable("auto_explain.log_verbose",
 							 "Use EXPLAIN VERBOSE for plan logging.",
 							 NULL,
@@ -356,6 +368,7 @@ explain_ExecutorEnd(QueryDesc *queryDesc)
 			es->timing = (es->analyze && auto_explain_log_timing);
 			es->summary = es->analyze;
 			es->format = auto_explain_log_format;
+			es->gucs = auto_explain_log_gucs;
 
 			ExplainBeginOutput(es);
 			ExplainQueryText(es, queryDesc);
diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml
index 120b168d45..852c69b7bb 100644
--- a/doc/src/sgml/auto-explain.sgml
+++ b/doc/src/sgml/auto-explain.sgml
@@ -169,6 +169,23 @@ LOAD 'auto_explain';
     </listitem>
    </varlistentry>
 
+   <varlistentry>
+    <term>
+     <varname>auto_explain.log_gucs</varname> (<type>boolean</type>)
+     <indexterm>
+      <primary><varname>auto_explain.log_gucs</varname> configuration parameter</primary>
+     </indexterm>
+    </term>
+    <listitem>
+     <para>
+      <varname>auto_explain.log_gucs</varname> controls whether information
+      about modified configuration options are logged with the execution
+      plan.  Only options modified at the database, user, client or session
+      level are considered modified.  This parameter is off by default.
+     </para>
+    </listitem>
+   </varlistentry>
+
    <varlistentry>
     <term>
      <varname>auto_explain.log_format</varname> (<type>enum</type>)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index de09ded65b..b8cab69f71 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -31,6 +31,7 @@
 #include "storage/bufmgr.h"
 #include "tcop/tcopprot.h"
 #include "utils/builtins.h"
+#include "utils/guc_tables.h"
 #include "utils/json.h"
 #include "utils/lsyscache.h"
 #include "utils/rel.h"
@@ -164,6 +165,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, const char *queryString,
 			es->costs = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "buffers") == 0)
 			es->buffers = defGetBoolean(opt);
+		else if (strcmp(opt->defname, "gucs") == 0)
+			es->gucs = defGetBoolean(opt);
 		else if (strcmp(opt->defname, "timing") == 0)
 		{
 			timing_set = true;
@@ -547,6 +550,37 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es,
 	/* Create textual dump of plan tree */
 	ExplainPrintPlan(es, queryDesc);
 
+	if (es->gucs)
+	{
+		int		i;
+		int		num;
+		StringInfoData	str;
+		struct config_generic **gucs;
+
+		gucs = get_modified_guc_options(&num);
+
+		for (i = 0; i < num; i++)
+		{
+			char *setting;
+			struct config_generic *conf = gucs[i];
+
+			if (i == 0)
+				initStringInfo(&str);
+			else
+				appendStringInfoString(&str, ", ");
+
+			setting = GetConfigOptionByName(conf->name, NULL, true);
+
+			if (setting)
+				appendStringInfo(&str, "%s = '%s'", conf->name, setting);
+			else
+				appendStringInfo(&str, "%s = NULL", conf->name);
+		}
+
+		if (num > 0)
+			ExplainPropertyText("GUCs", str.data, es);
+	}
+
 	if (es->summary && planduration)
 	{
 		double		plantime = INSTR_TIME_GET_DOUBLE(*planduration);
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 6fe1939881..2d37760c7a 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -8556,6 +8556,45 @@ ShowAllGUCConfig(DestReceiver *dest)
 	end_tup_output(tstate);
 }
 
+struct config_generic **
+get_modified_guc_options(int *num)
+{
+	int		i;
+	struct config_generic **result;
+
+	*num = 0;
+	result = palloc(sizeof(struct config_generic *) * num_guc_variables);
+
+	for (i = 0; i < num_guc_variables; i++)
+	{
+		struct config_generic *conf = guc_variables[i];
+
+		/* return only options visible to the user */
+		if ((conf->flags & GUC_NO_SHOW_ALL) ||
+			((conf->flags & GUC_SUPERUSER_ONLY) &&
+			 !is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_SETTINGS)))
+			continue;
+
+		/* only parameters related to query tuning */
+		if ((conf->group != QUERY_TUNING) &&
+			(conf->group != QUERY_TUNING_METHOD) &&
+			(conf->group != QUERY_TUNING_COST) &&
+			(conf->group != QUERY_TUNING_GEQO) &&
+			(conf->group != QUERY_TUNING_OTHER))
+			continue;
+
+		/* return only options that were modified (w.r.t. config file) */
+		if (conf->source <= PGC_S_ARGV)
+			continue;
+
+		/* assign to the values array */
+		result[*num] = conf;
+		*num = *num + 1;
+	}
+
+	return result;
+}
+
 /*
  * Return GUC variable value by name; optionally return canonical form of
  * name.  If the GUC is unset, then throw an error unless missing_ok is true,
diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h
index d3f70fda08..05afca22aa 100644
--- a/src/include/commands/explain.h
+++ b/src/include/commands/explain.h
@@ -35,6 +35,7 @@ typedef struct ExplainState
 	bool		buffers;		/* print buffer usage */
 	bool		timing;			/* print detailed node timing */
 	bool		summary;		/* print total planning and execution timing */
+	bool		gucs;			/* print modified GUCs */
 	ExplainFormat format;		/* output format */
 	/* state for output formatting --- not reset for each new plan tree */
 	int			indent;			/* current indentation level */
diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h
index 6f9fdb6a5f..4942e192d6 100644
--- a/src/include/utils/guc_tables.h
+++ b/src/include/utils/guc_tables.h
@@ -267,5 +267,6 @@ extern void build_guc_variables(void);
 extern const char *config_enum_lookup_by_value(struct config_enum *record, int val);
 extern bool config_enum_lookup_by_name(struct config_enum *record,
 						   const char *value, int *retval);
+extern struct config_generic **get_modified_guc_options(int *num);
 
 #endif							/* GUC_TABLES_H */

--------------D57F36AD70C9A1B06AF8D66B--