Today you get
test=> EXPLAIN SELECT * FROM tab WHERE col = $1;
ERROR: there is no parameter $1
which makes sense. Nonetheless, it would be great to get a generic plan
for such a query. Sometimes you don't have the parameters (if you grab
the statement from "pg_stat_statements", or if it is from an error message
in the log, and you didn't enable "log_parameter_max_length_on_error").
Sometimes it is just very painful to substitute the 25 parameters from
the detail message.
With the attached patch you can get the following:
test=> SET plan_cache_mode = force_generic_plan;
SET
test=> EXPLAIN (COSTS OFF) SELECT * FROM pg_proc WHERE oid = $1;
QUERY PLAN
═══════════════════════════════════════════════
Index Scan using pg_proc_oid_index on pg_proc
Index Cond: (oid = $1)
(2 rows)
That's not the same as a full-fledged EXPLAIN (ANALYZE, BUFFERS),
but it can definitely be helpful.
I tied that behavior to the setting of "plan_cache_mode" where you
are guaranteed to get a generic plan; I couldn't think of a better way.
Yours,
Laurenz Albe
From 2bc91581acd478d4648176b58745cadb835d5fbc Mon Sep 17 00:00:00 2001
From: Laurenz Albe <[email protected]>
Date: Tue, 11 Oct 2022 13:05:31 +0200
Subject: [PATCH] Add EXPLAIN support for parameterized statements
If "plan_cache_mode = force_generic_plan", allow EXPLAIN to
generate generic plans for parameterized statements (that
have parameter placeholders like $1 in the statement text).
This repurposes hooks used by PL/pgSQL, so we better not try
to do that inside PL/pgSQL.
---
doc/src/sgml/ref/explain.sgml | 10 +++++
src/backend/parser/analyze.c | 53 +++++++++++++++++++++++++++
src/test/regress/expected/explain.out | 28 ++++++++++++++
src/test/regress/sql/explain.sql | 13 +++++++
4 files changed, 104 insertions(+)
diff --git a/doc/src/sgml/ref/explain.sgml b/doc/src/sgml/ref/explain.sgml
index d4895b9d7d..928d67b9b4 100644
--- a/doc/src/sgml/ref/explain.sgml
+++ b/doc/src/sgml/ref/explain.sgml
@@ -321,6 +321,16 @@ ROLLBACK;
execution, and on machines that have relatively slow operating
system calls for obtaining the time of day.
</para>
+
+ <para>
+ If <xref linkend="guc-plan-cache_mode"/> is set to
+ <literal>force_generic_plan</literal>, you can use <command>EXPLAIN</command>
+ to generate generic plans for statements that contain placeholders like
+ <literal>$1</literal> without knowing the actual parameter type or value.
+ Note that expressions like <literal>$1 + $2</literal> are ambiguous if you
+ don't specify the parameter data types, so you may have to add explicit type
+ casts in such cases.
+ </para>
</refsect1>
<refsect1>
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 6688c2a865..c481d45376 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -52,6 +52,7 @@
#include "utils/guc.h"
#include "utils/queryjumble.h"
#include "utils/rel.h"
+#include "utils/plancache.h"
#include "utils/syscache.h"
@@ -86,6 +87,10 @@ static Query *transformCallStmt(ParseState *pstate,
CallStmt *stmt);
static void transformLockingClause(ParseState *pstate, Query *qry,
LockingClause *lc, bool pushedDown);
+static Node * fakeUnknownParam(ParseState *pstate, ParamRef *pref);
+static Node * coerceUnknownParam(ParseState *pstate, Param *param,
+ Oid targetTypeId, int32 targetTypeMod,
+ int location);
#ifdef RAW_EXPRESSION_COVERAGE_TEST
static bool test_raw_expression_coverage(Node *node, void *context);
#endif
@@ -2895,6 +2900,22 @@ transformExplainStmt(ParseState *pstate, ExplainStmt *stmt)
{
Query *result;
+ /*
+ * If we EXPLAIN a statement and are certain to generate a generic plan,
+ * we can tolerate undefined parameters. For that purpose, supply
+ * parameters of type "unknown" and coerce them to the appropriate type
+ * as needed.
+ * If we are called from PL/pgSQL, the hooks are already set for the
+ * purpose of resolving variables, and we don't want to disturb that.
+ */
+ if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_GENERIC_PLAN &&
+ pstate->p_paramref_hook == NULL &&
+ pstate->p_coerce_param_hook == NULL)
+ {
+ pstate->p_paramref_hook = fakeUnknownParam;
+ pstate->p_coerce_param_hook = coerceUnknownParam;
+ }
+
/* transform contained query, allowing SELECT INTO */
stmt->query = (Node *) transformOptionalSelectInto(pstate, stmt->query);
@@ -3466,6 +3487,38 @@ applyLockingClause(Query *qry, Index rtindex,
qry->rowMarks = lappend(qry->rowMarks, rc);
}
+/*
+ * Return an "unknown" parameter for use with EXPLAIN of a parameterized
+ * statement.
+ */
+Node *
+fakeUnknownParam(ParseState *pstate, ParamRef *pref)
+{
+ 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;
+}
+
+/*
+ * Set the parameter's type from "unknown" to the target type.
+ */
+Node *
+coerceUnknownParam(ParseState *pstate, Param *param, Oid targetTypeId,
+ int32 targetTypeMod, int location)
+{
+ param->paramtype = targetTypeId;
+
+ return (Node *)param;
+}
+
/*
* Coverage testing for raw_expression_tree_walker().
*
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 48620edbc2..4f6755b172 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)
+reset compute_query_id;
+-- Test parameterized plans
+-- it will fail unless plan_cache_mode = force_generic_plan
+explain (costs off) select unique1 from tenk1 where thousand = $1;
+ERROR: there is no parameter $1
+LINE 1: ...n (costs off) select unique1 from tenk1 where thousand = $1;
+ ^
+set plan_cache_mode=force_generic_plan;
+explain (costs off) 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)
+
+-- it should also fail from PL/pgSQL
+do $_$declare
+ x text;
+begin
+ explain (costs off) select unique1 from tenk1 where thousand = $1 into x;
+end;$_$;
+ERROR: there is no parameter $1
+LINE 1: ...in (costs off) select unique1 from tenk1 where thousand = $1
+ ^
+QUERY: explain (costs off) select unique1 from tenk1 where thousand = $1
+CONTEXT: PL/pgSQL function inline_code_block line 4 at SQL statement
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index ae3f7a308d..9d4827d299 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -128,3 +128,16 @@ 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');
+reset compute_query_id;
+
+-- Test parameterized plans
+-- it will fail unless plan_cache_mode = force_generic_plan
+explain (costs off) select unique1 from tenk1 where thousand = $1;
+set plan_cache_mode=force_generic_plan;
+explain (costs off) select unique1 from tenk1 where thousand = $1;
+-- it should also fail from PL/pgSQL
+do $_$declare
+ x text;
+begin
+ explain (costs off) select unique1 from tenk1 where thousand = $1 into x;
+end;$_$;
--
2.37.3
