On Fri, 2023-02-03 at 15:11 -0500, Tom Lane wrote: > I can think of a couple of possible ways forward: > > * Fix things so that the generic parameters appear to have NULL > values when inspected during executor startup. I'm not sure > how useful that'd be though. In partition-pruning cases that'd > lead to EXPLAIN (GENERIC_PLAN) showing the plan with all > partitions pruned, other than the one for NULL values if there > is one. Doesn't sound too helpful. > > * Invent another executor flag that's a "stronger" version of > EXEC_FLAG_EXPLAIN_ONLY, and pass that when any generic parameters > exist, and check it in CreatePartitionPruneState to decide whether > to do startup pruning. This avoids changing behavior for existing > cases, but I'm not sure how much it has to recommend it otherwise. > Skipping the startup prune step seems like it'd produce misleading > results in another way, ie showing too many partitions not too few. > > This whole business of partition pruning dependent on the > generic parameters is making me uncomfortable. It seems like > we *can't* show a plan that is either representative of real > execution or comparable to what you get from more-traditional > EXPLAIN usage. Maybe we need to step back and think more.
I slept over it, and the second idea now looks like the the right approach to me. My idea of seeing a generic plan is that plan-time partition pruning happens, but not execution-time pruning, so that I get no "subplans removed". Are there any weird side effects of skipping the startup prune step? Anyway, attached is v7 that tries to do it that way. It feels fairly good to me. I invented a new executor flag EXEC_FLAG_EXPLAIN_GENERIC. To avoid having to change all the places that check EXEC_FLAG_EXPLAIN_ONLY to also check for the new flag, I decided that the new flag can only be used as "add-on" to EXEC_FLAG_EXPLAIN_ONLY. Yours, Laurenz Albe
From cd0b5a1a4f301bb7fad9088d5763989f5dde4636 Mon Sep 17 00:00:00 2001 From: Laurenz Albe <laurenz.a...@cybertec.at> Date: Sun, 5 Feb 2023 18:11:57 +0100 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). Invent a new executor flag EXEC_FLAG_EXPLAIN_GENERIC that disables runtime partition pruning for such plans: that would fail if the non-existing parameters are involved, and we don't want to remove subplans anyway. Author: Laurenz Albe Discussion: https://postgr.es/m/0a29b954b10b57f0d135fe12aa0909bd41883eb0.camel%40cybertec.at --- doc/src/sgml/ref/explain.sgml | 15 +++++++++ src/backend/commands/explain.c | 11 +++++++ src/backend/executor/execMain.c | 3 ++ src/backend/executor/execPartition.c | 9 ++++-- src/backend/parser/analyze.c | 29 +++++++++++++++++ src/include/commands/explain.h | 1 + src/include/executor/executor.h | 18 +++++++---- src/test/regress/expected/explain.out | 46 +++++++++++++++++++++++++++ src/test/regress/sql/explain.sql | 29 +++++++++++++++++ 9 files changed, 152 insertions(+), 9 deletions(-) diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml index d4895b9d7d..a1fdd31bc7 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> (but then it has to be a statement + that supports parameters). This option cannot be used together with + <literal>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 35c23bd27d..37ea4e5035 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), @@ -574,6 +583,8 @@ ExplainOnePlan(PlannedStmt *plannedstmt, IntoClause *into, ExplainState *es, eflags = EXEC_FLAG_EXPLAIN_ONLY; if (into) eflags |= GetIntoRelEFlags(into); + if (es->generic) + eflags |= EXEC_FLAG_EXPLAIN_GENERIC; /* call ExecutorStart to prepare the plan for execution */ ExecutorStart(queryDesc, eflags); diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c index a5115b9c1f..fea4314033 100644 --- a/src/backend/executor/execMain.c +++ b/src/backend/executor/execMain.c @@ -131,6 +131,9 @@ static void EvalPlanQualStart(EPQState *epqstate, Plan *planTree); void ExecutorStart(QueryDesc *queryDesc, int eflags) { + /* EXEC_FLAG_EXPLAIN_GENERIC can only occur with EXEC_FLAG_EXPLAIN_ONLY */ + Assert((eflags & EXEC_FLAG_EXPLAIN_ONLY) || + !(eflags & EXEC_FLAG_EXPLAIN_GENERIC)); /* * In some cases (e.g. an EXECUTE statement) a query execution will skip * parse analysis, which means that the query_id won't be reported. Note diff --git a/src/backend/executor/execPartition.c b/src/backend/executor/execPartition.c index 651ad24fc1..7fc2f0d1ab 100644 --- a/src/backend/executor/execPartition.c +++ b/src/backend/executor/execPartition.c @@ -2040,10 +2040,12 @@ CreatePartitionPruneState(PlanState *planstate, PartitionPruneInfo *pruneinfo) pprune->present_parts = bms_copy(pinfo->present_parts); /* - * Initialize pruning contexts as needed. + * Initialize pruning contexts as needed. Specifically, we want to + * skip execution-time partition pruning for EXPLAIN (GENERIC_PLAN). */ pprune->initial_pruning_steps = pinfo->initial_pruning_steps; - if (pinfo->initial_pruning_steps) + if (pinfo->initial_pruning_steps && + !(econtext->ecxt_estate->es_top_eflags & EXEC_FLAG_EXPLAIN_GENERIC)) { InitPartitionPruneContext(&pprune->initial_context, pinfo->initial_pruning_steps, @@ -2053,7 +2055,8 @@ CreatePartitionPruneState(PlanState *planstate, PartitionPruneInfo *pruneinfo) prunestate->do_initial_prune = true; } pprune->exec_pruning_steps = pinfo->exec_pruning_steps; - if (pinfo->exec_pruning_steps) + if (pinfo->exec_pruning_steps && + !(econtext->ecxt_estate->es_top_eflags & EXEC_FLAG_EXPLAIN_GENERIC)) { InitPartitionPruneContext(&pprune->exec_context, pinfo->exec_pruning_steps, diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index e892df9819..9143964e78 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" @@ -2906,10 +2907,38 @@ static Query * transformExplainStmt(ParseState *pstate, ExplainStmt *stmt) { Query *result; + bool generic_plan = false; + Oid *paramTypes = NULL; + int numParams = 0; + + /* + * If we have no external source of parameter definitions, and the + * GENERIC_PLAN option is specified, then accept variable parameter + * definitions (as occurs in PREPARE, for example). + */ + if (pstate->p_paramref_hook == NULL) + { + ListCell *lc; + + 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 */ + } + if (generic_plan) + setup_parse_variable_parameters(pstate, ¶mTypes, &numParams); + } /* transform contained query, allowing SELECT INTO */ stmt->query = (Node *) transformOptionalSelectInto(pstate, stmt->query); + /* make sure all is well with parameter types */ + if (generic_plan) + check_variable_parameters(pstate, (Query *) stmt->query); + /* represent the command as a utility Query */ result = makeNode(Query); result->commandType = CMD_UTILITY; diff --git a/src/include/commands/explain.h b/src/include/commands/explain.h index 7c1071ddd1..3d3e632a0c 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/executor/executor.h b/src/include/executor/executor.h index e7e25c057e..a6b6ad943c 100644 --- a/src/include/executor/executor.h +++ b/src/include/executor/executor.h @@ -36,6 +36,11 @@ * of startup should occur. However, error checks (such as permission checks) * should be performed. * + * EXPLAIN_GENERIC can only be used together with EXPLAIN_ONLY. It indicates + * that a generic plan is being calculated using EXPLAIN (GENERIC_PLAN), which + * means that missing parameters must be tolerated. Currently, the only effect + * is to suppress execution-time partition pruning. + * * REWIND indicates that the plan node should try to efficiently support * rescans without parameter changes. (Nodes must support ExecReScan calls * in any case, but if this flag was not given, they are at liberty to do it @@ -53,12 +58,13 @@ * mean that the plan can't queue any AFTER triggers; just that the caller * is responsible for there being a trigger context for them to be queued in. */ -#define EXEC_FLAG_EXPLAIN_ONLY 0x0001 /* EXPLAIN, no ANALYZE */ -#define EXEC_FLAG_REWIND 0x0002 /* need efficient rescan */ -#define EXEC_FLAG_BACKWARD 0x0004 /* need backward scan */ -#define EXEC_FLAG_MARK 0x0008 /* need mark/restore */ -#define EXEC_FLAG_SKIP_TRIGGERS 0x0010 /* skip AfterTrigger calls */ -#define EXEC_FLAG_WITH_NO_DATA 0x0020 /* rel scannability doesn't matter */ +#define EXEC_FLAG_EXPLAIN_ONLY 0x0001 /* EXPLAIN, no ANALYZE */ +#define EXEC_FLAG_REWIND 0x0002 /* need efficient rescan */ +#define EXEC_FLAG_BACKWARD 0x0004 /* need backward scan */ +#define EXEC_FLAG_MARK 0x0008 /* need mark/restore */ +#define EXEC_FLAG_SKIP_TRIGGERS 0x0010 /* skip AfterTrigger calls */ +#define EXEC_FLAG_WITH_NO_DATA 0x0020 /* rel scannability doesn't matter */ +#define EXEC_FLAG_EXPLAIN_GENERIC 0x0040 /* EXPLAIN (GENERIC_PLAN) */ /* Hook for plugins to get control in ExecutorStart() */ diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out index 48620edbc2..73922cbe9d 100644 --- a/src/test/regress/expected/explain.out +++ b/src/test/regress/expected/explain.out @@ -517,3 +517,49 @@ select explain_filter('explain (verbose) select * from int8_tbl i8'); Query Identifier: N (3 rows) +-- Test EXPLAIN (GENERIC_PLAN) +select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); + explain_filter +--------------------------------------------------------------------------------- + Bitmap Heap Scan on tenk1 (cost=N.N..N.N rows=N width=N) + Recheck Cond: (thousand = $N) + -> Bitmap Index Scan on tenk1_thous_tenthous (cost=N.N..N.N rows=N width=N) + Index Cond: (thousand = $N) +(4 rows) + +-- should fail +select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +ERROR: EXPLAIN ANALYZE cannot be used with GENERIC_PLAN +CONTEXT: PL/pgSQL function explain_filter(text) line 5 at FOR over EXECUTE statement +-- Test EXPLAIN (GENERIC_PLAN) with partition pruning +-- should prune at plan time, but not at execution time +create extension if not exists postgres_fdw; +create server loop42 foreign data wrapper postgres_fdw; +create user mapping for current_role server loop42 options (password_required 'false'); +create table gen_part ( + key1 integer not null, + key2 integer not null +) partition by list (key1); +create table gen_part_1 + partition of gen_part for values in (1) + partition by range (key2); +create foreign table gen_part_1_1 + partition of gen_part_1 for values from (1) to (2) + server loop42 options (table_name 'whatever_1_1'); +create foreign table gen_part_1_2 + partition of gen_part_1 for values from (2) to (3) + server loop42 options (table_name 'whatever_1_2'); +create foreign table gen_part_2 + partition of gen_part for values in (2) + server loop42 options (table_name 'whatever_2'); +select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1'); + explain_filter +------------------------------------------------------------------------------- + Append (cost=N.N..N.N rows=N width=N) + -> Foreign Scan on gen_part_1_1 gen_part_1 (cost=N.N..N.N rows=N width=N) + -> Foreign Scan on gen_part_1_2 gen_part_2 (cost=N.N..N.N rows=N width=N) +(3 rows) + +drop table gen_part; +drop server loop42 cascade; +NOTICE: drop cascades to user mapping for postgres on server loop42 diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql index ae3f7a308d..c39a7afaee 100644 --- a/src/test/regress/sql/explain.sql +++ b/src/test/regress/sql/explain.sql @@ -128,3 +128,32 @@ 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) +select explain_filter('explain (generic_plan) select unique1 from tenk1 where thousand = $1'); +-- should fail +select explain_filter('explain (analyze, generic_plan) select unique1 from tenk1 where thousand = $1'); +-- Test EXPLAIN (GENERIC_PLAN) with partition pruning +-- should prune at plan time, but not at execution time +create extension if not exists postgres_fdw; +create server loop42 foreign data wrapper postgres_fdw; +create user mapping for current_role server loop42 options (password_required 'false'); +create table gen_part ( + key1 integer not null, + key2 integer not null +) partition by list (key1); +create table gen_part_1 + partition of gen_part for values in (1) + partition by range (key2); +create foreign table gen_part_1_1 + partition of gen_part_1 for values from (1) to (2) + server loop42 options (table_name 'whatever_1_1'); +create foreign table gen_part_1_2 + partition of gen_part_1 for values from (2) to (3) + server loop42 options (table_name 'whatever_1_2'); +create foreign table gen_part_2 + partition of gen_part for values in (2) + server loop42 options (table_name 'whatever_2'); +select explain_filter('explain (generic_plan) select key1, key2 from gen_part where key1 = 1 and key2 = $1'); +drop table gen_part; +drop server loop42 cascade; -- 2.39.1
