From d25192892d61419278fbb216e695cb070c332092 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Thu, 15 Jan 2015 13:18:16 -0500 Subject: [PATCH] Improve performance of EXPLAIN with large range tables. As of 9.3, ruleutils.c goes to some lengths to ensure that table and column aliases used in its output are unique. Of course this takes more time than was required before, which in itself isn't fatal. However, EXPLAIN was set up so that recalculation of the unique aliases was repeated for each subexpression printed in a plan. That results in O(N^2) time and memory consumption for large plan trees, which did not happen in older branches. Fortunately, the expensive work is the same across a whole plan tree, so there is no need to repeat it; we can do most of the initialization just once per query and re-use it for each subexpression. This buys back most (not all) of the performance loss since 9.2. We need an extra ExplainState field to hold the precalculated deparse context. That's no problem in HEAD, but in the back branches, expanding sizeof(ExplainState) seems risky because third-party extensions might have local variables of that struct type. So, in 9.4 and 9.3, introduce an auxiliary struct to keep sizeof(ExplainState) the same. We should refactor the APIs to avoid such local variables in future, but that's material for a separate HEAD-only commit. Per gripe from Alexey Bashtanov. Back-patch to 9.3 where the issue was introduced. --- src/backend/commands/explain.c | 29 +++++++------ src/backend/utils/adt/ruleutils.c | 71 +++++++++++++++++++++---------- src/include/commands/explain.h | 9 +++- src/include/utils/builtins.h | 5 ++- 4 files changed, 77 insertions(+), 37 deletions(-) diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index d99e5a86f0..032f5a4743 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -33,6 +33,10 @@ #include "utils/xml.h" +/* Crude hack to avoid changing sizeof(ExplainState) in released branches */ +#define grouping_stack extra->groupingstack +#define deparse_cxt extra->deparsecxt + /* Hook for plugins to get control in ExplainOneQuery() */ ExplainOneQuery_hook_type ExplainOneQuery_hook = NULL; @@ -262,6 +266,8 @@ ExplainInitState(ExplainState *es) es->costs = true; /* Prepare output buffer. */ es->str = makeStringInfo(); + /* Kluge to avoid changing sizeof(ExplainState) in released branches. */ + es->extra = (ExplainStateExtra *) palloc0(sizeof(ExplainStateExtra)); } /* @@ -562,6 +568,8 @@ ExplainPrintPlan(ExplainState *es, QueryDesc *queryDesc) es->rtable = queryDesc->plannedstmt->rtable; ExplainPreScanNode(queryDesc->planstate, &rels_used); es->rtable_names = select_rtable_names_for_explain(es->rtable, rels_used); + es->deparse_cxt = deparse_context_for_plan_rtable(es->rtable, + es->rtable_names); ExplainNode(queryDesc->planstate, NIL, NULL, NULL, es); } @@ -1653,10 +1661,9 @@ show_plan_tlist(PlanState *planstate, List *ancestors, ExplainState *es) return; /* Set up deparsing context */ - context = deparse_context_for_planstate((Node *) planstate, - ancestors, - es->rtable, - es->rtable_names); + context = set_deparse_context_planstate(es->deparse_cxt, + (Node *) planstate, + ancestors); useprefix = list_length(es->rtable) > 1; /* Deparse each result column (we now include resjunk ones) */ @@ -1685,10 +1692,9 @@ show_expression(Node *node, const char *qlabel, char *exprstr; /* Set up deparsing context */ - context = deparse_context_for_planstate((Node *) planstate, - ancestors, - es->rtable, - es->rtable_names); + context = set_deparse_context_planstate(es->deparse_cxt, + (Node *) planstate, + ancestors); /* Deparse the expression */ exprstr = deparse_expression(node, context, useprefix, false); @@ -1830,10 +1836,9 @@ show_sort_group_keys(PlanState *planstate, const char *qlabel, return; /* Set up deparsing context */ - context = deparse_context_for_planstate((Node *) planstate, - ancestors, - es->rtable, - es->rtable_names); + context = set_deparse_context_planstate(es->deparse_cxt, + (Node *) planstate, + ancestors); useprefix = (list_length(es->rtable) > 1 || es->verbose); for (keyno = 0; keyno < nkeys; keyno++) diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 143c8b268b..351c5d5c24 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2519,7 +2519,43 @@ deparse_context_for(const char *aliasname, Oid relid) } /* - * deparse_context_for_planstate - Build deparse context for a plan + * deparse_context_for_plan_rtable - Build deparse context for a plan's rtable + * + * When deparsing an expression in a Plan tree, we use the plan's rangetable + * to resolve names of simple Vars. The initialization of column names for + * this is rather expensive if the rangetable is large, and it'll be the same + * for every expression in the Plan tree; so we do it just once and re-use + * the result of this function for each expression. (Note that the result + * is not usable until set_deparse_context_planstate() is applied to it.) + * + * In addition to the plan's rangetable list, pass the per-RTE alias names + * assigned by a previous call to select_rtable_names_for_explain. + */ +List * +deparse_context_for_plan_rtable(List *rtable, List *rtable_names) +{ + deparse_namespace *dpns; + + dpns = (deparse_namespace *) palloc0(sizeof(deparse_namespace)); + + /* Initialize fields that stay the same across the whole plan tree */ + dpns->rtable = rtable; + dpns->rtable_names = rtable_names; + dpns->ctes = NIL; + + /* + * Set up column name aliases. We will get rather bogus results for join + * RTEs, but that doesn't matter because plan trees don't contain any join + * alias Vars. + */ + set_simple_column_names(dpns); + + /* Return a one-deep namespace stack */ + return list_make1(dpns); +} + +/* + * set_deparse_context_planstate - Specify Plan node containing expression * * When deparsing an expression in a Plan tree, we might have to resolve * OUTER_VAR, INNER_VAR, or INDEX_VAR references. To do this, the caller must @@ -2538,37 +2574,28 @@ deparse_context_for(const char *aliasname, Oid relid) * most-closely-nested first. This is needed to resolve PARAM_EXEC Params. * Note we assume that all the PlanStates share the same rtable. * - * The plan's rangetable list must also be passed, along with the per-RTE - * alias names assigned by a previous call to select_rtable_names_for_explain. - * (We use the rangetable to resolve simple Vars, but the plan inputs are - * necessary for Vars with special varnos.) + * Once this function has been called, deparse_expression() can be called on + * subsidiary expression(s) of the specified PlanState node. To deparse + * expressions of a different Plan node in the same Plan tree, re-call this + * function to identify the new parent Plan node. + * + * The result is the same List passed in; this is a notational convenience. */ List * -deparse_context_for_planstate(Node *planstate, List *ancestors, - List *rtable, List *rtable_names) +set_deparse_context_planstate(List *dpcontext, + Node *planstate, List *ancestors) { deparse_namespace *dpns; - dpns = (deparse_namespace *) palloc0(sizeof(deparse_namespace)); - - /* Initialize fields that stay the same across the whole plan tree */ - dpns->rtable = rtable; - dpns->rtable_names = rtable_names; - dpns->ctes = NIL; - - /* - * Set up column name aliases. We will get rather bogus results for join - * RTEs, but that doesn't matter because plan trees don't contain any join - * alias Vars. - */ - set_simple_column_names(dpns); + /* Should always have one-entry namespace list for Plan deparsing */ + Assert(list_length(dpcontext) == 1); + dpns = (deparse_namespace *) linitial(dpcontext); /* Set our attention on the specific plan node passed in */ set_deparse_planstate(dpns, (PlanState *) planstate); dpns->ancestors = ancestors; - /* Return a one-deep namespace stack */ - return list_make1(dpns); + return dpcontext; } /* diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index d56beaac15..8629fa8dc4 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -24,6 +24,13 @@ typedef enum ExplainFormat EXPLAIN_FORMAT_YAML } ExplainFormat; +/* Crude hack to avoid changing sizeof(ExplainState) in released branches */ +typedef struct ExplainStateExtra +{ + List *groupingstack; /* format-specific grouping state */ + List *deparsecxt; /* context list for deparsing expressions */ +} ExplainStateExtra; + typedef struct ExplainState { StringInfo str; /* output buffer */ @@ -40,7 +47,7 @@ typedef struct ExplainState List *rtable; /* range table */ List *rtable_names; /* alias names for RTEs */ int indent; /* current indentation level */ - List *grouping_stack; /* format-specific grouping state */ + ExplainStateExtra *extra; /* pointer to additional data */ } ExplainState; /* Hook for plugins to get control in ExplainOneQuery() */ diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index bbb5d398a7..f0c68ac33c 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -694,8 +694,9 @@ extern Datum pg_get_function_arg_default(PG_FUNCTION_ARGS); extern char *deparse_expression(Node *expr, List *dpcontext, bool forceprefix, bool showimplicit); extern List *deparse_context_for(const char *aliasname, Oid relid); -extern List *deparse_context_for_planstate(Node *planstate, List *ancestors, - List *rtable, List *rtable_names); +extern List *deparse_context_for_plan_rtable(List *rtable, List *rtable_names); +extern List *set_deparse_context_planstate(List *dpcontext, + Node *planstate, List *ancestors); extern List *select_rtable_names_for_explain(List *rtable, Bitmapset *rels_used); extern const char *quote_identifier(const char *ident); -- 2.40.0