Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9yim-0001SP-3g for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Mar 2019 21:07:28 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1h9yik-0005JI-SR for pgsql-hackers@arkaria.postgresql.org; Fri, 29 Mar 2019 21:07:26 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9yik-0005JB-Bx for pgsql-hackers@lists.postgresql.org; Fri, 29 Mar 2019 21:07:26 +0000 Received: from mail-wm1-x343.google.com ([2a00:1450:4864:20::343]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1h9yig-00038j-Bz for pgsql-hackers@postgresql.org; Fri, 29 Mar 2019 21:07:25 +0000 Received: by mail-wm1-x343.google.com with SMTP id o10so10531997wmc.1 for ; Fri, 29 Mar 2019 14:07:22 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=2ndquadrant-com.20150623.gappssmtp.com; s=20150623; h=date:from:to:cc:subject:message-id:references:mime-version :content-disposition:in-reply-to:user-agent; bh=IPxPpl3A4pNPBrP+mmY9r7sT0PtFUmdYTXhjSBovnyQ=; b=IW5Wk2FofOfl/fcWONY6JeZDJTTAKYByLAKG9Lab9e+3RJaw/1qdmhVVoMorey2Wmf TRF14YDY/U6FAv9ZPmvqbV3wqYmbcUzRRThM+MtSSIzuj8W0dqp/RS0Fi39yo2z9Ncrg klzqPUYoyfHDrxGCRqNfyQogiI5oP7h/q3m1VlVfGRApQK3PtSyxW7i+1ETK/TA+2su4 559CDKGN0579sIYuGLJJIXC9NKWUhqTxP9wO/nn66QOfVvNBeH0T3ZHcigKh6Jzf1SN0 FR0zqJnE/8jUXHfMpygW0Ctia2uNrZs+vUQuNcxJcC3BN7DqHQZpp/ciWrEcm8FaJcc8 Opww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:date:from:to:cc:subject:message-id:references :mime-version:content-disposition:in-reply-to:user-agent; bh=IPxPpl3A4pNPBrP+mmY9r7sT0PtFUmdYTXhjSBovnyQ=; b=FmitwH3cC/iDRRnwAZLLy8Id2TQ/wSJbqimQuz9xTjYX/v2WDZ/oWZz8x5dj1q3G8g jqtLKSFUOerEVnSmV5aXpm8imoS71QIh0so24T7W4wZJKV+sVNLKAkVBQKa/QYLKRTJj WnK9MxQQpe8yx8bemlJwQgZwiaUIOyh/VsxurjSdlm51SjxyNeSfGCEc/laVQ2kd4iui LiXHOw+JjUi7EaZJftbw2STCglJHh3afQyMLcoXGdIp/j5MHOR7eFBTbcVZwF54asReF DCz1Yl36M7sWXDcdA7GsuQ3ue+2Yj48SqwjSy7GewWoMQObsU0jVZ3COVlvauJZ9MR+5 3s8g== X-Gm-Message-State: APjAAAVK/ubRoI0UK1tOnN1mhpLu2UqO89nCW7oGbEv1OGvNEHwVVVup BG4pGqjM8C9xioYFpG+40y9oJA== X-Google-Smtp-Source: APXvYqxZw0j5eGuVbTQtjK8csWnFs+ro2eV+9nRHQnClPQ61bDry2vJ1+5klhuqMZG7iAFdn+PtLdg== X-Received: by 2002:a1c:304:: with SMTP id 4mr4576261wmd.97.1553893640824; Fri, 29 Mar 2019 14:07:20 -0700 (PDT) Received: from localhost (ip-86-49-243-43.net.upcbroadband.cz. [86.49.243.43]) by smtp.gmail.com with ESMTPSA id s203sm4539548wmf.14.2019.03.29.14.07.19 (version=TLS1_3 cipher=AEAD-AES256-GCM-SHA384 bits=256/256); Fri, 29 Mar 2019 14:07:19 -0700 (PDT) Date: Fri, 29 Mar 2019 22:07:17 +0100 From: Tomas Vondra To: Rafia Sabih Cc: Michael Paquier , John Naylor , Sergei Agalakov , PostgreSQL-development Subject: Re: explain plans with information about (modified) gucs Message-ID: <20190329210717.GD4719@development> References: <5396e093-ed6b-5962-2ed5-337a0425160b@2ndquadrant.com> <603089e0-3387-30f1-3cbc-25dd7797e17a@2ndquadrant.com> <20190204022209.GY1881@paquier.xyz> <905460bc-db16-112f-1e2c-8dd48667f000@2ndquadrant.com> <20190326200453.GA16551@development> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="jho1yZJdad60DJr+" Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.11.4+135 (aae3e555) (2019-03-21) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --jho1yZJdad60DJr+ Content-Type: text/plain; charset=us-ascii; format=flowed Content-Disposition: inline On Wed, Mar 27, 2019 at 09:06:04AM +0100, Rafia Sabih wrote: >On Tue, 26 Mar 2019 at 21:04, Tomas Vondra >wrote: > >> On Mon, Mar 18, 2019 at 11:31:48AM +0100, Rafia Sabih wrote: >> >On Sun, 24 Feb 2019 at 00:06, Tomas Vondra >> wrote: >> >> >> >> Hi, >> >> >> >> attached is an updated patch, fixing and slightly tweaking the docs. >> >> >> >> >> >> Barring objections, I'll get this committed later next week. >> >> >> >I was having a look at this patch, and this kept me wondering, >> > >> >+static void >> >+ExplainShowSettings(ExplainState *es) >> >+{ >> >Is there some reason for not providing any explanation above this >> >function just like the rest of the functions in this file? >> > >> >Similarly, for >> > >> >struct config_generic ** >> >get_explain_guc_options(int *num) >> >{ >> > >> >/* also bail out of there are no options */ >> >+ if (!num) >> >+ return; >> >I think you meant 'if' instead if 'of' here. >> >> Thanks for the review - attached is a patch adding the missing comments, >> and doing two additional minor improvements: >> >> 1) Renaming ExplainShowSettings to ExplainPrintSettings, to make it more >> consistent with naming of the other functions in explain.c. >> >> 2) Actually counting GUC_EXPLAIN options, and only allocating space for >> those in get_explain_guc_options, instead of using num_guc_variables. The >> diffrence is quite significant (~50 vs. ~300), and considering each entry >> is 8B it makes a difference because such large chunks tend to have higher >> palloc overhed (due to ALLOCSET_SEPARATE_THRESHOLD). >> >> > Looks like the patch is in need of a rebase. >At commit: 1983af8e899389187026cb34c1ca9d89ea986120 > >P.S. reject files attached. > D'oh! That was a stupid mistake - I apparently attched just the delta against the previous patch version, i.e. the improvements I described. Attaches is a correct (and complete) patch. I planned to get this committed today, but considering this I'll wait until early next week to allow for feedback. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --jho1yZJdad60DJr+ Content-Type: text/plain; charset=us-ascii Content-Disposition: attachment; filename="explain-with-settings-20190329.patch" diff --git a/contrib/auto_explain/auto_explain.c b/contrib/auto_explain/auto_explain.c index 7b22927674..edc50f9368 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_settings = 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_settings", + "Log modified configuration parameters affecting query planning.", + NULL, + &auto_explain_log_settings, + 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->settings = auto_explain_log_settings; ExplainBeginOutput(es); ExplainQueryText(es, queryDesc); diff --git a/doc/src/sgml/auto-explain.sgml b/doc/src/sgml/auto-explain.sgml index 120b168d45..296ae2de80 100644 --- a/doc/src/sgml/auto-explain.sgml +++ b/doc/src/sgml/auto-explain.sgml @@ -169,6 +169,24 @@ LOAD 'auto_explain'; + + + auto_explain.log_settings (boolean) + + auto_explain.log_settings configuration parameter + + + + + auto_explain.log_settings controls whether information + about modified configuration options affecting query planning are logged + with the execution plan. Only options affecting query planning with value + different from the built-in default value are considered. This parameter is + off by default. Only superusers can change this setting. + + + + auto_explain.log_format (enum) diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index 8dc0d7038a..385d10411f 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -39,6 +39,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] statementboolean ] VERBOSE [ boolean ] COSTS [ boolean ] + SETTINGS [ boolean ] BUFFERS [ boolean ] TIMING [ boolean ] SUMMARY [ boolean ] @@ -152,6 +153,17 @@ ROLLBACK; + + SETTINGS + + + Include information on configuration parameters. Specifically, include + options affecting query planning with value different from the built-in + default value. This parameter defaults to FALSE. + + + + BUFFERS diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index 1831ea81cf..2a289b8b94 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -29,6 +29,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" @@ -162,6 +163,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, "settings") == 0) + es->settings = defGetBoolean(opt); else if (strcmp(opt->defname, "timing") == 0) { timing_set = true; @@ -596,6 +599,72 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, ExplainCloseGroup("Query", NULL, true, es); } +/* + * ExplainPrintSettings - + * Print summary of modified settings affecting query planning. + */ +static void +ExplainPrintSettings(ExplainState *es) +{ + int num; + struct config_generic **gucs; + + /* bail out if GUC information not requested */ + if (!es->settings) + return; + + gucs = get_explain_guc_options(&num); + + /* also bail out of there are no options */ + if (!num) + return; + + if (es->format != EXPLAIN_FORMAT_TEXT) + { + int i; + + ExplainOpenGroup("Settings", "Settings", false, es); + + for (i = 0; i < num; i++) + { + char *setting; + struct config_generic *conf = gucs[i]; + + setting = GetConfigOptionByName(conf->name, NULL, true); + + ExplainPropertyText(conf->name, setting, es); + } + + ExplainCloseGroup("Settings", "Settings", false, es); + } + else + { + int i; + StringInfoData str; + + initStringInfo(&str); + + for (i = 0; i < num; i++) + { + char *setting; + struct config_generic *conf = gucs[i]; + + if (i > 0) + 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("Settings", str.data, es); + } +} + /* * ExplainPrintPlan - * convert a QueryDesc's plan tree to text and append it to es->str @@ -633,6 +702,12 @@ ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc) if (IsA(ps, GatherState) &&((Gather *) ps->plan)->invisible) ps = outerPlanState(ps); ExplainNode(ps, NIL, NULL, NULL, es); + + /* + * If requested, include information about GUC parameters with values + * that don't match the built-in defaults. + */ + ExplainPrintSettings(es); } /* diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index aa564d153a..39f844ebc5 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -881,7 +881,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_seqscan", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of sequential-scan plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_seqscan, true, @@ -890,7 +891,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_indexscan", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of index-scan plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_indexscan, true, @@ -899,7 +901,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_indexonlyscan", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of index-only-scan plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_indexonlyscan, true, @@ -908,7 +911,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_bitmapscan", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of bitmap-scan plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_bitmapscan, true, @@ -917,7 +921,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_tidscan", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of TID scan plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_tidscan, true, @@ -926,7 +931,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_sort", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of explicit sort steps."), - NULL + NULL, + GUC_EXPLAIN }, &enable_sort, true, @@ -935,7 +941,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_hashagg", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of hashed aggregation plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_hashagg, true, @@ -944,7 +951,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_material", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of materialization."), - NULL + NULL, + GUC_EXPLAIN }, &enable_material, true, @@ -953,7 +961,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_nestloop", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of nested-loop join plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_nestloop, true, @@ -962,7 +971,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_mergejoin", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of merge join plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_mergejoin, true, @@ -971,7 +981,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_hashjoin", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of hash join plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_hashjoin, true, @@ -980,7 +991,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_gathermerge", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of gather merge plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_gathermerge, true, @@ -989,7 +1001,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_partitionwise_join", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables partitionwise join."), - NULL + NULL, + GUC_EXPLAIN }, &enable_partitionwise_join, false, @@ -998,7 +1011,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_partitionwise_aggregate", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables partitionwise aggregation and grouping."), - NULL + NULL, + GUC_EXPLAIN }, &enable_partitionwise_aggregate, false, @@ -1007,7 +1021,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_parallel_append", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of parallel append plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_parallel_append, true, @@ -1016,7 +1031,8 @@ static struct config_bool ConfigureNamesBool[] = { {"enable_parallel_hash", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enables the planner's use of parallel hash plans."), - NULL + NULL, + GUC_EXPLAIN }, &enable_parallel_hash, true, @@ -1027,7 +1043,8 @@ static struct config_bool ConfigureNamesBool[] = gettext_noop("Enable plan-time and run-time partition pruning."), gettext_noop("Allows the query planner and executor to compare partition " "bounds to conditions in the query to determine which " - "partitions must be scanned.") + "partitions must be scanned."), + GUC_EXPLAIN }, &enable_partition_pruning, true, @@ -1037,7 +1054,8 @@ static struct config_bool ConfigureNamesBool[] = {"geqo", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("Enables genetic query optimization."), gettext_noop("This algorithm attempts to do planning without " - "exhaustive searching.") + "exhaustive searching."), + GUC_EXPLAIN }, &enable_geqo, true, @@ -1625,7 +1643,7 @@ static struct config_bool ConfigureNamesBool[] = "optimize_bounded_sort", PGC_USERSET, QUERY_TUNING_METHOD, gettext_noop("Enable bounded sorting using heap sort."), NULL, - GUC_NOT_IN_SAMPLE + GUC_NOT_IN_SAMPLE | GUC_EXPLAIN }, &optimize_bounded_sort, true, @@ -1816,7 +1834,8 @@ static struct config_bool ConfigureNamesBool[] = { {"parallel_leader_participation", PGC_USERSET, RESOURCES_ASYNCHRONOUS, gettext_noop("Controls whether Gather and Gather Merge also run subplans."), - gettext_noop("Should gather nodes also run subplans, or just gather tuples?") + gettext_noop("Should gather nodes also run subplans, or just gather tuples?"), + GUC_EXPLAIN }, ¶llel_leader_participation, true, @@ -1826,7 +1845,8 @@ static struct config_bool ConfigureNamesBool[] = { {"jit", PGC_USERSET, QUERY_TUNING_OTHER, gettext_noop("Allow JIT compilation."), - NULL + NULL, + GUC_EXPLAIN }, &jit_enabled, true, @@ -1955,7 +1975,8 @@ static struct config_int ConfigureNamesInt[] = "are not collapsed."), gettext_noop("The planner will merge subqueries into upper " "queries if the resulting FROM list would have no more than " - "this many items.") + "this many items."), + GUC_EXPLAIN }, &from_collapse_limit, 8, 1, INT_MAX, @@ -1967,7 +1988,8 @@ static struct config_int ConfigureNamesInt[] = "constructs are not flattened."), gettext_noop("The planner will flatten explicit JOIN " "constructs into lists of FROM items whenever a " - "list of no more than this many items would result.") + "list of no more than this many items would result."), + GUC_EXPLAIN }, &join_collapse_limit, 8, 1, INT_MAX, @@ -1976,7 +1998,8 @@ static struct config_int ConfigureNamesInt[] = { {"geqo_threshold", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("Sets the threshold of FROM items beyond which GEQO is used."), - NULL + NULL, + GUC_EXPLAIN }, &geqo_threshold, 12, 2, INT_MAX, @@ -1985,7 +2008,8 @@ static struct config_int ConfigureNamesInt[] = { {"geqo_effort", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("GEQO: effort is used to set the default for other GEQO parameters."), - NULL + NULL, + GUC_EXPLAIN }, &Geqo_effort, DEFAULT_GEQO_EFFORT, MIN_GEQO_EFFORT, MAX_GEQO_EFFORT, @@ -1994,7 +2018,8 @@ static struct config_int ConfigureNamesInt[] = { {"geqo_pool_size", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("GEQO: number of individuals in the population."), - gettext_noop("Zero selects a suitable default value.") + gettext_noop("Zero selects a suitable default value."), + GUC_EXPLAIN }, &Geqo_pool_size, 0, 0, INT_MAX, @@ -2003,7 +2028,8 @@ static struct config_int ConfigureNamesInt[] = { {"geqo_generations", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("GEQO: number of iterations of the algorithm."), - gettext_noop("Zero selects a suitable default value.") + gettext_noop("Zero selects a suitable default value."), + GUC_EXPLAIN }, &Geqo_generations, 0, 0, INT_MAX, @@ -2117,7 +2143,7 @@ static struct config_int ConfigureNamesInt[] = {"temp_buffers", PGC_USERSET, RESOURCES_MEM, gettext_noop("Sets the maximum number of temporary buffers used by each session."), NULL, - GUC_UNIT_BLOCKS + GUC_UNIT_BLOCKS | GUC_EXPLAIN }, &num_temp_buffers, 1024, 100, INT_MAX / 2, @@ -2184,7 +2210,7 @@ static struct config_int ConfigureNamesInt[] = gettext_noop("This much memory can be used by each internal " "sort operation and hash table before switching to " "temporary disk files."), - GUC_UNIT_KB + GUC_UNIT_KB | GUC_EXPLAIN }, &work_mem, 4096, 64, MAX_KILOBYTES, @@ -2713,7 +2739,8 @@ static struct config_int ConfigureNamesInt[] = PGC_USERSET, RESOURCES_ASYNCHRONOUS, gettext_noop("Number of simultaneous requests that can be handled efficiently by the disk subsystem."), - gettext_noop("For RAID arrays, this should be approximately the number of drive spindles in the array.") + gettext_noop("For RAID arrays, this should be approximately the number of drive spindles in the array."), + GUC_EXPLAIN }, &effective_io_concurrency, #ifdef USE_PREFETCH @@ -2958,7 +2985,8 @@ static struct config_int ConfigureNamesInt[] = { {"max_parallel_workers_per_gather", PGC_USERSET, RESOURCES_ASYNCHRONOUS, gettext_noop("Sets the maximum number of parallel processes per executor node."), - NULL + NULL, + GUC_EXPLAIN }, &max_parallel_workers_per_gather, 2, 0, MAX_PARALLEL_WORKER_LIMIT, @@ -2968,7 +2996,8 @@ static struct config_int ConfigureNamesInt[] = { {"max_parallel_workers", PGC_USERSET, RESOURCES_ASYNCHRONOUS, gettext_noop("Sets the maximum number of parallel workers that can be active at one time."), - NULL + NULL, + GUC_EXPLAIN }, &max_parallel_workers, 8, 0, MAX_PARALLEL_WORKER_LIMIT, @@ -3058,7 +3087,7 @@ static struct config_int ConfigureNamesInt[] = gettext_noop("Sets the planner's assumption about the total size of the data caches."), gettext_noop("That is, the total size of the caches (kernel cache and shared buffers) used for PostgreSQL data files. " "This is measured in disk pages, which are normally 8 kB each."), - GUC_UNIT_BLOCKS, + GUC_UNIT_BLOCKS | GUC_EXPLAIN, }, &effective_cache_size, DEFAULT_EFFECTIVE_CACHE_SIZE, 1, INT_MAX, @@ -3069,7 +3098,7 @@ static struct config_int ConfigureNamesInt[] = {"min_parallel_table_scan_size", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the minimum amount of table data for a parallel scan."), gettext_noop("If the planner estimates that it will read a number of table pages too small to reach this limit, a parallel scan will not be considered."), - GUC_UNIT_BLOCKS, + GUC_UNIT_BLOCKS | GUC_EXPLAIN, }, &min_parallel_table_scan_size, (8 * 1024 * 1024) / BLCKSZ, 0, INT_MAX / 3, @@ -3080,7 +3109,7 @@ static struct config_int ConfigureNamesInt[] = {"min_parallel_index_scan_size", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the minimum amount of index data for a parallel scan."), gettext_noop("If the planner estimates that it will read a number of index pages too small to reach this limit, a parallel scan will not be considered."), - GUC_UNIT_BLOCKS, + GUC_UNIT_BLOCKS | GUC_EXPLAIN, }, &min_parallel_index_scan_size, (512 * 1024) / BLCKSZ, 0, INT_MAX / 3, @@ -3145,7 +3174,8 @@ static struct config_real ConfigureNamesReal[] = {"seq_page_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of a " "sequentially fetched disk page."), - NULL + NULL, + GUC_EXPLAIN }, &seq_page_cost, DEFAULT_SEQ_PAGE_COST, 0, DBL_MAX, @@ -3155,7 +3185,8 @@ static struct config_real ConfigureNamesReal[] = {"random_page_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of a " "nonsequentially fetched disk page."), - NULL + NULL, + GUC_EXPLAIN }, &random_page_cost, DEFAULT_RANDOM_PAGE_COST, 0, DBL_MAX, @@ -3165,7 +3196,8 @@ static struct config_real ConfigureNamesReal[] = {"cpu_tuple_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of " "processing each tuple (row)."), - NULL + NULL, + GUC_EXPLAIN }, &cpu_tuple_cost, DEFAULT_CPU_TUPLE_COST, 0, DBL_MAX, @@ -3175,7 +3207,8 @@ static struct config_real ConfigureNamesReal[] = {"cpu_index_tuple_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of " "processing each index entry during an index scan."), - NULL + NULL, + GUC_EXPLAIN }, &cpu_index_tuple_cost, DEFAULT_CPU_INDEX_TUPLE_COST, 0, DBL_MAX, @@ -3185,7 +3218,8 @@ static struct config_real ConfigureNamesReal[] = {"cpu_operator_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of " "processing each operator or function call."), - NULL + NULL, + GUC_EXPLAIN }, &cpu_operator_cost, DEFAULT_CPU_OPERATOR_COST, 0, DBL_MAX, @@ -3195,7 +3229,8 @@ static struct config_real ConfigureNamesReal[] = {"parallel_tuple_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of " "passing each tuple (row) from worker to master backend."), - NULL + NULL, + GUC_EXPLAIN }, ¶llel_tuple_cost, DEFAULT_PARALLEL_TUPLE_COST, 0, DBL_MAX, @@ -3205,7 +3240,8 @@ static struct config_real ConfigureNamesReal[] = {"parallel_setup_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Sets the planner's estimate of the cost of " "starting up worker processes for parallel query."), - NULL + NULL, + GUC_EXPLAIN }, ¶llel_setup_cost, DEFAULT_PARALLEL_SETUP_COST, 0, DBL_MAX, @@ -3215,7 +3251,8 @@ static struct config_real ConfigureNamesReal[] = { {"jit_above_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Perform JIT compilation if query is more expensive."), - gettext_noop("-1 disables JIT compilation.") + gettext_noop("-1 disables JIT compilation."), + GUC_EXPLAIN }, &jit_above_cost, 100000, -1, DBL_MAX, @@ -3225,7 +3262,8 @@ static struct config_real ConfigureNamesReal[] = { {"jit_optimize_above_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Optimize JITed functions if query is more expensive."), - gettext_noop("-1 disables optimization.") + gettext_noop("-1 disables optimization."), + GUC_EXPLAIN }, &jit_optimize_above_cost, 500000, -1, DBL_MAX, @@ -3235,7 +3273,8 @@ static struct config_real ConfigureNamesReal[] = { {"jit_inline_above_cost", PGC_USERSET, QUERY_TUNING_COST, gettext_noop("Perform JIT inlining if query is more expensive."), - gettext_noop("-1 disables inlining.") + gettext_noop("-1 disables inlining."), + GUC_EXPLAIN }, &jit_inline_above_cost, 500000, -1, DBL_MAX, @@ -3246,7 +3285,8 @@ static struct config_real ConfigureNamesReal[] = {"cursor_tuple_fraction", PGC_USERSET, QUERY_TUNING_OTHER, gettext_noop("Sets the planner's estimate of the fraction of " "a cursor's rows that will be retrieved."), - NULL + NULL, + GUC_EXPLAIN }, &cursor_tuple_fraction, DEFAULT_CURSOR_TUPLE_FRACTION, 0.0, 1.0, @@ -3256,7 +3296,8 @@ static struct config_real ConfigureNamesReal[] = { {"geqo_selection_bias", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("GEQO: selective pressure within the population."), - NULL + NULL, + GUC_EXPLAIN }, &Geqo_selection_bias, DEFAULT_GEQO_SELECTION_BIAS, @@ -3266,7 +3307,8 @@ static struct config_real ConfigureNamesReal[] = { {"geqo_seed", PGC_USERSET, QUERY_TUNING_GEQO, gettext_noop("GEQO: seed for random path selection."), - NULL + NULL, + GUC_EXPLAIN }, &Geqo_seed, 0.0, 0.0, 1.0, @@ -3714,7 +3756,7 @@ static struct config_string ConfigureNamesString[] = {"search_path", PGC_USERSET, CLIENT_CONN_STATEMENT, gettext_noop("Sets the schema search order for names that are not schema-qualified."), NULL, - GUC_LIST_INPUT | GUC_LIST_QUOTE + GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_EXPLAIN }, &namespace_search_path, "\"$user\", public", @@ -4167,7 +4209,8 @@ static struct config_enum ConfigureNamesEnum[] = {"constraint_exclusion", PGC_USERSET, QUERY_TUNING_OTHER, gettext_noop("Enables the planner to use constraints to optimize queries."), gettext_noop("Table scans will be skipped if their constraints" - " guarantee that no rows match the query.") + " guarantee that no rows match the query."), + GUC_EXPLAIN }, &constraint_exclusion, CONSTRAINT_EXCLUSION_PARTITION, constraint_exclusion_options, @@ -4403,7 +4446,8 @@ static struct config_enum ConfigureNamesEnum[] = { {"force_parallel_mode", PGC_USERSET, QUERY_TUNING_OTHER, gettext_noop("Forces use of parallel query facilities."), - gettext_noop("If possible, run query using a parallel worker and with parallel restrictions.") + gettext_noop("If possible, run query using a parallel worker and with parallel restrictions."), + GUC_EXPLAIN }, &force_parallel_mode, FORCE_PARALLEL_OFF, force_parallel_mode_options, @@ -4427,7 +4471,8 @@ static struct config_enum ConfigureNamesEnum[] = gettext_noop("Controls the planner's selection of custom or generic plan."), gettext_noop("Prepared statements can have custom and generic plans, and the planner " "will attempt to choose which is better. This can be set to override " - "the default behavior.") + "the default behavior."), + GUC_EXPLAIN }, &plan_cache_mode, PLAN_CACHE_MODE_AUTO, plan_cache_mode_options, @@ -4487,6 +4532,7 @@ static struct config_generic **guc_variables; /* Current number of variables contained in the vector */ static int num_guc_variables; +static int num_guc_explain_variables; /* Vector capacity */ static int size_guc_variables; @@ -4751,6 +4797,7 @@ build_guc_variables(void) { int size_vars; int num_vars = 0; + int num_explain_vars = 0; struct config_generic **guc_vars; int i; @@ -4761,6 +4808,9 @@ build_guc_variables(void) /* Rather than requiring vartype to be filled in by hand, do this: */ conf->gen.vartype = PGC_BOOL; num_vars++; + + if (conf->gen.flags & GUC_EXPLAIN) + num_explain_vars++; } for (i = 0; ConfigureNamesInt[i].gen.name; i++) @@ -4769,6 +4819,9 @@ build_guc_variables(void) conf->gen.vartype = PGC_INT; num_vars++; + + if (conf->gen.flags & GUC_EXPLAIN) + num_explain_vars++; } for (i = 0; ConfigureNamesReal[i].gen.name; i++) @@ -4777,6 +4830,9 @@ build_guc_variables(void) conf->gen.vartype = PGC_REAL; num_vars++; + + if (conf->gen.flags & GUC_EXPLAIN) + num_explain_vars++; } for (i = 0; ConfigureNamesString[i].gen.name; i++) @@ -4785,6 +4841,9 @@ build_guc_variables(void) conf->gen.vartype = PGC_STRING; num_vars++; + + if (conf->gen.flags & GUC_EXPLAIN) + num_explain_vars++; } for (i = 0; ConfigureNamesEnum[i].gen.name; i++) @@ -4793,6 +4852,9 @@ build_guc_variables(void) conf->gen.vartype = PGC_ENUM; num_vars++; + + if (conf->gen.flags & GUC_EXPLAIN) + num_explain_vars++; } /* @@ -4824,6 +4886,7 @@ build_guc_variables(void) free(guc_variables); guc_variables = guc_vars; num_guc_variables = num_vars; + num_guc_explain_variables = num_explain_vars; size_guc_variables = size_vars; qsort((void *) guc_variables, num_guc_variables, sizeof(struct config_generic *), guc_var_compare); @@ -8774,6 +8837,99 @@ ShowAllGUCConfig(DestReceiver *dest) end_tup_output(tstate); } +/* + * Returns an array of modified GUC options to show in EXPLAIN. Only options + * related to query planning (marked with GUC_EXPLAIN), with values different + * from built-in defaults. + */ +struct config_generic ** +get_explain_guc_options(int *num) +{ + int i; + struct config_generic **result; + + *num = 0; + + /* + * Allocate enough space to fit all GUC_EXPLAIN options. We may not + * need all the space, but there are fairly few such options so we + * don't waste a lot of memory. + */ + result = palloc(sizeof(struct config_generic *) * num_guc_explain_variables); + + for (i = 0; i < num_guc_variables; i++) + { + bool modified; + 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 explicitly marked for inclusion in explain */ + if (!(conf->flags & GUC_EXPLAIN)) + continue; + + /* return only options that were modified (w.r.t. config file) */ + modified = false; + + switch (conf->vartype) + { + case PGC_BOOL: + { + struct config_bool *lconf = (struct config_bool *) conf; + modified = (lconf->boot_val != *(lconf->variable)); + } + break; + + case PGC_INT: + { + struct config_int *lconf = (struct config_int *) conf; + modified = (lconf->boot_val != *(lconf->variable)); + } + break; + + case PGC_REAL: + { + struct config_real *lconf = (struct config_real *) conf; + modified = (lconf->boot_val != *(lconf->variable)); + } + break; + + case PGC_STRING: + { + struct config_string *lconf = (struct config_string *) conf; + modified = (strcmp(lconf->boot_val, *(lconf->variable)) != 0); + } + break; + + case PGC_ENUM: + { + struct config_enum *lconf = (struct config_enum *) conf; + modified = (lconf->boot_val != *(lconf->variable)); + } + break; + + default: + elog(ERROR, "unexcpected GUC type: %d", conf->vartype); + } + + /* skip GUC variables that match the built-in default */ + if (!modified) + continue; + + /* assign to the values array */ + result[*num] = conf; + *num = *num + 1; + + Assert(*num <= num_guc_explain_variables); + } + + 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 e8854db459..db48f29501 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 settings; /* print modified settings */ 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.h b/src/include/utils/guc.h index 2712a774f7..f73edc3edc 100644 --- a/src/include/utils/guc.h +++ b/src/include/utils/guc.h @@ -227,6 +227,8 @@ typedef enum #define GUC_UNIT_MIN 0x30000 /* value is in minutes */ #define GUC_UNIT_TIME 0xF0000 /* mask for time-related units */ +#define GUC_EXPLAIN 0x100000 /* include in explain */ + #define GUC_UNIT (GUC_UNIT_MEMORY | GUC_UNIT_TIME) diff --git a/src/include/utils/guc_tables.h b/src/include/utils/guc_tables.h index a0970b2e1c..2a74b30b2f 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_explain_guc_options(int *num); #endif /* GUC_TABLES_H */ --jho1yZJdad60DJr+--