On Wed, 2022-10-12 at 00:03 +0800, Julien Rouhaud wrote: > On Tue, Oct 11, 2022 at 09:49:14AM -0400, Tom Lane wrote: > > I think it might be better to drive it off an explicit EXPLAIN option, > > perhaps > > > > EXPLAIN (GENERIC_PLAN) SELECT * FROM tab WHERE col = $1; > > > > If you're trying to investigate custom-plan behavior, then you > > need to supply concrete parameter values somewhere, so I think > > this approach is fine for that case. (Shoehorning parameter > > values into EXPLAIN options seems like it'd be a bit much.) > > However, investigating generic-plan behavior this way is tedious, > > since you have to invent irrelevant parameter values, plus mess > > with plan_cache_mode or else run the explain half a dozen times. > > So I can get behind having a more convenient way for that. > > One common use case is tools identifying a slow query using > pg_stat_statements, > identifying some missing indexes and then wanting to check whether the index > should be useful using some hypothetical index. > > FTR I'm working on such a project and for now we have to go to great lengths > trying to "unjumble" such queries, so having a way to easily get the answer > for > a generic plan would be great.
Thanks for the suggestions and the encouragement. Here is a patch that implements it with an EXPLAIN option named GENERIC_PLAN. Yours, Laurenz Albe
From 85991f35f0de6e4e0a0b5843373e2ba3d5976c85 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Tue, 25 Oct 2022 11:01:53 +0200 Subject: [PATCH] Add EXPLAIN option GENERIC_PLAN This allows EXPLAIN to generate generic plans for parameterized statements (that have parameter placeholders like $1 in the statement text). Author: Laurenz Albe Discussion: https://postgr.es/m/0a29b954b10b57f0d135fe12aa0909bd41883eb0.camel%40cybertec.at --- doc/src/sgml/ref/explain.sgml | 15 ++++++++++++++ src/backend/commands/explain.c | 9 +++++++++ src/backend/parser/analyze.c | 13 +++++++++++++ src/backend/parser/parse_coerce.c | 15 ++++++++++++++ src/backend/parser/parse_expr.c | 16 +++++++++++++++ src/include/commands/explain.h | 1 + src/include/parser/parse_node.h | 2 ++ src/test/regress/expected/explain.out | 28 +++++++++++++++++++++++++++ src/test/regress/sql/explain.sql | 16 +++++++++++++++ 9 files changed, 115 insertions(+) diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index d4895b9d7d..659d5c51b6 100644 --- a/doc/src/sgml/ref/explain.sgml +++ b/doc/src/sgml/ref/explain.sgml @@ -40,6 +40,7 @@ EXPLAIN [ ANALYZE ] [ VERBOSE ] <replaceable class="parameter">statement</replac VERBOSE [ <replaceable class="parameter">boolean</replaceable> ] COSTS [ <replaceable class="parameter">boolean</replaceable> ] SETTINGS [ <replaceable class="parameter">boolean</replaceable> ] + GENERIC_PLAN [ <replaceable class="parameter">boolean</replaceable> ] BUFFERS [ <replaceable class="parameter">boolean</replaceable> ] WAL [ <replaceable class="parameter">boolean</replaceable> ] TIMING [ <replaceable class="parameter">boolean</replaceable> ] @@ -167,6 +168,20 @@ ROLLBACK; </listitem> </varlistentry> + <varlistentry> + <term><literal>GENERIC_PLAN</literal></term> + <listitem> + <para> + Generate a generic plan for the statement (see <xref linkend="sql-prepare"/> + for details about generic plans). The statement can contain parameter + placeholders like <literal>$1</literal> and must be a statement that can + use parameters. This option cannot be used together with + <likeral>ANALYZE</literal>, since a statement with unknown parameters + cannot be executed. + </para> + </listitem> + </varlistentry> + <varlistentry> <term><literal>BUFFERS</literal></term> <listitem> diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c index f86983c660..7b7ca3f90a 100644 --- a/src/backend/commands/explain.c +++ b/src/backend/commands/explain.c @@ -190,6 +190,8 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, es->wal = defGetBoolean(opt); else if (strcmp(opt->defname, "settings") == 0) es->settings = defGetBoolean(opt); + else if (strcmp(opt->defname, "generic_plan") == 0) + es->generic = defGetBoolean(opt); else if (strcmp(opt->defname, "timing") == 0) { timing_set = true; @@ -227,6 +229,13 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt, parser_errposition(pstate, opt->location))); } + /* check that GENERIC_PLAN is not used with EXPLAIN ANALYZE */ + if (es->generic && es->analyze) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("EXPLAIN ANALYZE cannot be used with GENERIC_PLAN"))); + + /* check that WAL is used with EXPLAIN ANALYZE */ if (es->wal && !es->analyze) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 6688c2a865..c849765151 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -27,6 +27,7 @@ #include "access/sysattr.h" #include "catalog/pg_proc.h" #include "catalog/pg_type.h" +#include "commands/defrem.h" #include "miscadmin.h" #include "nodes/makefuncs.h" #include "nodes/nodeFuncs.h" @@ -2894,6 +2895,18 @@ static Query * transformExplainStmt(ParseState *pstate, ExplainStmt *stmt) { Query *result; + ListCell *lc; + bool generic_plan; + + foreach(lc, stmt->options) + { + DefElem *opt = (DefElem *) lfirst(lc); + + if (strcmp(opt->defname, "generic_plan") == 0) + generic_plan = defGetBoolean(opt); + /* don't "break", as we want the last value */ + } + pstate->p_generic_explain = generic_plan; /* transform contained query, allowing SELECT INTO */ stmt->query = (Node *) transformOptionalSelectInto(pstate, stmt->query); diff --git a/src/backend/parser/parse_coerce.c b/src/backend/parser/parse_coerce.c index c4e958e4aa..171d8c60a8 100644 --- a/src/backend/parser/parse_coerce.c +++ b/src/backend/parser/parse_coerce.c @@ -369,6 +369,21 @@ coerce_type(ParseState *pstate, Node *node, return result; } + /* + * If we are to generate a generic plan for EXPLAIN, simply let the + * parameter be of the desired type. + */ + if (IsA(node, Param) && + pstate != NULL && pstate->p_generic_explain) + { + Param *param = (Param *) node; + + param->paramtype = targetTypeId; + param->paramtypmod = targetTypeMod; + param->location = location; + + return node; + } if (IsA(node, Param) && pstate != NULL && pstate->p_coerce_param_hook != NULL) { diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 7aaf1c673f..75a24ed9b2 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -815,6 +815,22 @@ transformParamRef(ParseState *pstate, ParamRef *pref) { Node *result; + /* for "generic_plan" EXPLAIN, supply an unknown parameter */ + if (pstate->p_generic_explain) + { + Param *param; + + param = makeNode(Param); + param->paramkind = PARAM_EXTERN; + param->paramid = pref->number; + param->paramtype = UNKNOWNOID; + param->paramtypmod = -1; + param->paramcollid = InvalidOid; + param->location = pref->location; + + return (Node *)param; + } + /* * The core parser knows nothing about Params. If a hook is supplied, * call it. If not, or if the hook returns NULL, throw a generic error. diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index 9ebde089ae..524d355961 100644 --- a/src/include/commands/explain.h +++ b/src/include/commands/explain.h @@ -46,6 +46,7 @@ typedef struct ExplainState bool timing; /* print detailed node timing */ bool summary; /* print total planning and execution timing */ bool settings; /* print modified settings */ + bool generic; /* generate a generic plan */ 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/parser/parse_node.h b/src/include/parser/parse_node.h index 962ebf65de..0095fdb347 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -202,6 +202,8 @@ struct ParseState * with FOR UPDATE/FOR SHARE */ bool p_resolve_unknowns; /* resolve unknown-type SELECT outputs as * type text */ + bool p_generic_explain; /* accept undefined parameter + * placeholders */ QueryEnvironment *p_queryEnv; /* curr env, incl refs to enclosing env */ diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index 48620edbc2..53bb024813 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -517,3 +517,31 @@ select explain_filter('explain (verbose) select * from int8_tbl i8'); Query Identifier: N (3 rows) +-- Test EXPLAIN (GENERIC_PLAN) +EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1; + QUERY PLAN +------------------------------------------------- + Bitmap Heap Scan on tenk1 + Recheck Cond: (thousand = $1) + -> Bitmap Index Scan on tenk1_thous_tenthous + Index Cond: (thousand = $1) +(4 rows) + +-- should fail +EXPLAIN (COSTS OFF, GENERIC_PLAN, ANALYZE) SELECT unique1 FROM tenk1 WHERE thousand = $1; +ERROR: EXPLAIN ANALYZE cannot be used with GENERIC_PLAN +-- should also work in PL/pgSQL +DO +$$DECLARE + t text; +BEGIN + FOR t IN + EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1 + LOOP + RAISE NOTICE '%', t; + END LOOP; +END;$$; +NOTICE: Bitmap Heap Scan on tenk1 +NOTICE: Recheck Cond: (thousand = $1) +NOTICE: -> Bitmap Index Scan on tenk1_thous_tenthous +NOTICE: Index Cond: (thousand = $1) diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index ae3f7a308d..c2438efdcc 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -128,3 +128,19 @@ select explain_filter('explain (verbose) select * from t1 where pg_temp.mysin(f1 -- Test compute_query_id set compute_query_id = on; select explain_filter('explain (verbose) select * from int8_tbl i8'); + +-- Test EXPLAIN (GENERIC_PLAN) +EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1; +-- should fail +EXPLAIN (COSTS OFF, GENERIC_PLAN, ANALYZE) SELECT unique1 FROM tenk1 WHERE thousand = $1; +-- should also work in PL/pgSQL +DO +$$DECLARE + t text; +BEGIN + FOR t IN + EXPLAIN (COSTS OFF, GENERIC_PLAN) SELECT unique1 FROM tenk1 WHERE thousand = $1 + LOOP + RAISE NOTICE '%', t; + END LOOP; +END;$$; -- 2.37.3
