Hi all,
I’d like to propose a patch that introduces a*referenced generic plan*mode.
The patch adds:
*
new EXPLAIN option|REF_GENERIC_PLAN|(not allowed with ANALYZE);
*
new|plan_cache_mode|values|ref_auto|and|force_ref_generic_plan|;
*
a flag to prevent constant folding of Params when building a generic
plan.
Generic and custom plan behavior stays unchanged unless the new option
is used.
Similar ideas exist in other systems: Oracle (Adaptive Cursor Sharing),
SQL Server (Parameter Sensitive Plan Optimization), and Db2 (REOPT).
I’ve attached the patch and a minimal regression test.
Best regards,
Vlada Pogozhelskaya
From 7383e7e57bca1a19ab05d52a7981152edce90192 Mon Sep 17 00:00:00 2001
From: Vlada Pogozhelskaya <pogozhelsk...@gmail.com>
Date: Wed, 3 Sep 2025 17:13:28 +0300
Subject: [PATCH] Referenced optimisation of Generic plans
---
src/backend/commands/explain.c | 25 ++++++++++++++--
src/backend/commands/explain_state.c | 9 ++++++
src/backend/optimizer/util/clauses.c | 6 ++++
src/backend/utils/cache/plancache.c | 43 +++++++++++++++++++++++----
src/backend/utils/misc/guc_tables.c | 2 ++
src/include/commands/explain_state.h | 1 +
src/include/nodes/params.h | 1 +
src/include/nodes/parsenodes.h | 1 +
src/include/utils/plancache.h | 2 ++
src/test/regress/expected/prepare.out | 28 +++++++++++++++++
src/test/regress/sql/prepare.sql | 31 +++++++++++++++++++
11 files changed, 141 insertions(+), 8 deletions(-)
diff --git a/src/backend/commands/explain.c b/src/backend/commands/explain.c
index 8345bc0264b..a83ee584fdd 100644
--- a/src/backend/commands/explain.c
+++ b/src/backend/commands/explain.c
@@ -220,8 +220,12 @@ ExplainQuery(ParseState *pstate, ExplainStmt *stmt,
/* Explain every plan */
foreach(l, rewritten)
{
+ int cursor_opts = CURSOR_OPT_PARALLEL_OK;
+ if (es->ref_generic)
+ cursor_opts |= CURSOR_OPT_REF_GENERIC_PLAN;
+
ExplainOneQuery(lfirst_node(Query, l),
- CURSOR_OPT_PARALLEL_OK,
NULL, es,
+ cursor_opts, NULL, es,
pstate, params);
/* Separate plans with an appropriate separator */
@@ -329,6 +333,23 @@ standard_ExplainOneQuery(Query *query, int cursorOptions,
MemoryContext planner_ctx = NULL;
MemoryContext saved_ctx = NULL;
+ ParamListInfo paramLI = params;
+
+ if (es->generic)
+ {
+ if (es->ref_generic && params)
+ {
+ ParamListInfo ref = copyParamList(params);
+ for (int i = 0; ref && i < ref->numParams; i++)
+ ref->params[i].pflags |= PARAM_FLAG_REFVALUE;
+ paramLI = ref;
+ }
+ else
+ {
+ paramLI = NULL;
+ }
+ }
+
if (es->memory)
{
/*
@@ -350,7 +371,7 @@ standard_ExplainOneQuery(Query *query, int cursorOptions,
INSTR_TIME_SET_CURRENT(planstart);
/* plan the query */
- plan = pg_plan_query(query, queryString, cursorOptions, params);
+ plan = pg_plan_query(query, queryString, cursorOptions, paramLI);
INSTR_TIME_SET_CURRENT(planduration);
INSTR_TIME_SUBTRACT(planduration, planstart);
diff --git a/src/backend/commands/explain_state.c
b/src/backend/commands/explain_state.c
index 60d98d63a62..89c3cdd1349 100644
--- a/src/backend/commands/explain_state.c
+++ b/src/backend/commands/explain_state.c
@@ -62,6 +62,7 @@ NewExplainState(void)
{
ExplainState *es = (ExplainState *) palloc0(sizeof(ExplainState));
+ es->ref_generic = false;
/* Set default options (most fields can be left as zeroes). */
es->costs = true;
/* Prepare output buffer. */
@@ -103,6 +104,8 @@ ParseExplainOptionList(ExplainState *es, List *options,
ParseState *pstate)
es->settings = defGetBoolean(opt);
else if (strcmp(opt->defname, "generic_plan") == 0)
es->generic = defGetBoolean(opt);
+ else if (strcmp(opt->defname, "ref_generic_plan") == 0)
+ es->ref_generic = defGetBoolean(opt);
else if (strcmp(opt->defname, "timing") == 0)
{
timing_set = true;
@@ -197,6 +200,12 @@ ParseExplainOptionList(ExplainState *es, List *options,
ParseState *pstate)
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("EXPLAIN options ANALYZE and
GENERIC_PLAN cannot be used together")));
+ /* check that REF_GENERIC_PLAN is not used with EXPLAIN ANALYZE */
+ if (es->ref_generic && es->analyze)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("EXPLAIN options ANALYZE and
REF_GENERIC_PLAN cannot be used together")));
+
/* if the summary was not set explicitly, set default value */
es->summary = (summary_set) ? es->summary : es->analyze;
diff --git a/src/backend/optimizer/util/clauses.c
b/src/backend/optimizer/util/clauses.c
index 6f0b338d2cd..b7e323823b8 100644
--- a/src/backend/optimizer/util/clauses.c
+++ b/src/backend/optimizer/util/clauses.c
@@ -2488,6 +2488,12 @@ eval_const_expressions_mutator(Node *node,
if (OidIsValid(prm->ptype) &&
prm->ptype == param->paramtype)
{
+ if (!context->estimate &&
+ (prm->pflags &
PARAM_FLAG_REFVALUE))
+ {
+ /* Keep as Param */
+ return (Node *)
copyObject(param);
+ }
/* OK to substitute parameter
value? */
if (context->estimate ||
(prm->pflags &
PARAM_FLAG_CONST))
diff --git a/src/backend/utils/cache/plancache.c
b/src/backend/utils/cache/plancache.c
index 6661d2c6b73..e0e50f33020 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -1186,13 +1186,15 @@ choose_custom_plan(CachedPlanSource *plansource,
ParamListInfo boundParams)
return false;
/* Let settings force the decision */
- if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_GENERIC_PLAN)
+ if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_GENERIC_PLAN ||
+ plan_cache_mode == PLAN_CACHE_MODE_FORCE_REF_GENERIC_PLAN)
return false;
if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN)
return true;
/* See if caller wants to force the decision */
- if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
+ if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN ||
+ plansource->cursor_options & CURSOR_OPT_REF_GENERIC_PLAN)
return false;
if (plansource->cursor_options & CURSOR_OPT_CUSTOM_PLAN)
return true;
@@ -1324,7 +1326,21 @@ GetCachedPlan(CachedPlanSource *plansource,
ParamListInfo boundParams,
else
{
/* Build a new generic plan */
- plan = BuildCachedPlan(plansource, qlist, NULL,
queryEnv);
+ if (boundParams &&
+ ((plansource->cursor_options &
CURSOR_OPT_REF_GENERIC_PLAN) ||
+ plan_cache_mode ==
PLAN_CACHE_MODE_FORCE_REF_GENERIC_PLAN ||
+ plan_cache_mode ==
PLAN_CACHE_MODE_REF_AUTO))
+ {
+ ParamListInfo pli = copyParamList(boundParams);
+ if (pli)
+ for (int i = 0; i < pli->numParams; i++)
+ pli->params[i].pflags |=
PARAM_FLAG_REFVALUE;
+ plan = BuildCachedPlan(plansource, qlist, pli,
queryEnv);
+ }
+ else
+ {
+ plan = BuildCachedPlan(plansource, qlist, NULL,
queryEnv);
+ }
/* Just make real sure plansource->gplan is clear */
ReleaseGenericPlan(plansource);
/* Link the new generic plan into the plansource */
@@ -1355,7 +1371,11 @@ GetCachedPlan(CachedPlanSource *plansource,
ParamListInfo boundParams,
* find it's a loser, but we don't want to actually
execute that
* plan.
*/
- customplan = choose_custom_plan(plansource,
boundParams);
+ if (plan_cache_mode ==
PLAN_CACHE_MODE_FORCE_GENERIC_PLAN ||
+ plan_cache_mode ==
PLAN_CACHE_MODE_FORCE_REF_GENERIC_PLAN)
+ customplan = false;
+ else
+ customplan = choose_custom_plan(plansource,
boundParams);
/*
* If we choose to plan again, we need to re-copy the
query_list,
@@ -1368,8 +1388,19 @@ GetCachedPlan(CachedPlanSource *plansource,
ParamListInfo boundParams,
if (customplan)
{
- /* Build a custom plan */
- plan = BuildCachedPlan(plansource, qlist, boundParams,
queryEnv);
+ /* Build a custom plan: inline params as Consts, purge REFVALUE
*/
+ ParamListInfo pli = boundParams;
+ if (pli)
+ {
+ pli = copyParamList(pli);
+ for (int i = 0; i < pli->numParams; i++)
+ {
+ pli->params[i].pflags |= PARAM_FLAG_CONST;
+ pli->params[i].pflags &= ~PARAM_FLAG_REFVALUE;
+ }
+ }
+ plan = BuildCachedPlan(plansource, qlist, pli, queryEnv);
+
/* Accumulate total costs of custom plans */
plansource->total_custom_cost += cached_plan_cost(plan, true);
diff --git a/src/backend/utils/misc/guc_tables.c
b/src/backend/utils/misc/guc_tables.c
index f137129209f..732fa82556f 100644
--- a/src/backend/utils/misc/guc_tables.c
+++ b/src/backend/utils/misc/guc_tables.c
@@ -406,7 +406,9 @@ static const struct config_enum_entry
debug_parallel_query_options[] = {
static const struct config_enum_entry plan_cache_mode_options[] = {
{"auto", PLAN_CACHE_MODE_AUTO, false},
+ {"ref_auto", PLAN_CACHE_MODE_REF_AUTO, false},
{"force_generic_plan", PLAN_CACHE_MODE_FORCE_GENERIC_PLAN, false},
+ {"force_ref_generic_plan", PLAN_CACHE_MODE_FORCE_REF_GENERIC_PLAN,
false},
{"force_custom_plan", PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN, false},
{NULL, 0, false}
};
diff --git a/src/include/commands/explain_state.h
b/src/include/commands/explain_state.h
index 32728f5d1a1..59a79d22119 100644
--- a/src/include/commands/explain_state.h
+++ b/src/include/commands/explain_state.h
@@ -62,6 +62,7 @@ typedef struct ExplainState
List *grouping_stack; /* format-specific grouping state */
/* state related to the current plan tree (filled by ExplainPrintPlan)
*/
PlannedStmt *pstmt; /* top of plan */
+ bool ref_generic; /* show reference-based generic
plan */
List *rtable; /* range table */
List *rtable_names; /* alias names for RTEs */
List *deparse_cxt; /* context list for deparsing
expressions */
diff --git a/src/include/nodes/params.h b/src/include/nodes/params.h
index 4321ca6329b..bdb755554fc 100644
--- a/src/include/nodes/params.h
+++ b/src/include/nodes/params.h
@@ -86,6 +86,7 @@ struct ParseState;
*/
#define PARAM_FLAG_CONST 0x0001 /* parameter is constant */
+#define PARAM_FLAG_REFVALUE 0x0002
typedef struct ParamExternData
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 86a236bd58b..0ab60446783 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -3391,6 +3391,7 @@ typedef struct SecLabelStmt
#define CURSOR_OPT_GENERIC_PLAN 0x0200 /* force use of generic plan */
#define CURSOR_OPT_CUSTOM_PLAN 0x0400 /* force use of custom plan */
#define CURSOR_OPT_PARALLEL_OK 0x0800 /* parallel mode OK */
+#define CURSOR_OPT_REF_GENERIC_PLAN 0x1000 /* force use of generic plan
(constants are treated as references) */
typedef struct DeclareCursorStmt
{
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 1baa6d50bfd..f0d9faa7a38 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -31,7 +31,9 @@ struct RawStmt;
typedef enum
{
PLAN_CACHE_MODE_AUTO,
+ PLAN_CACHE_MODE_REF_AUTO,
PLAN_CACHE_MODE_FORCE_GENERIC_PLAN,
+ PLAN_CACHE_MODE_FORCE_REF_GENERIC_PLAN,
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN,
} PlanCacheMode;
diff --git a/src/test/regress/expected/prepare.out
b/src/test/regress/expected/prepare.out
index 5815e17b39c..a3f8abdc3e8 100644
--- a/src/test/regress/expected/prepare.out
+++ b/src/test/regress/expected/prepare.out
@@ -192,3 +192,31 @@ SELECT name, statement, parameter_types FROM
pg_prepared_statements
------+-----------+-----------------
(0 rows)
+CREATE TABLE rg_test (x int, y int);
+INSERT INTO rg_test SELECT g, g FROM generate_series(1,1000) g;
+CREATE INDEX rg_test_x_idx ON rg_test(x);
+VACUUM ANALYZE rg_test;
+SET plan_cache_mode = 'force_ref_generic_plan';
+SET enable_seqscan = off;
+SET enable_indexonlyscan = off;
+PREPARE rgq(int) AS
+ SELECT * FROM rg_test WHERE x < $1;
+EXPLAIN (COSTS OFF, GENERIC_PLAN, REF_GENERIC_PLAN true)
+EXECUTE rgq(10);
+ QUERY PLAN
+-------------------------------------------
+ Index Scan using rg_test_x_idx on rg_test
+ Index Cond: (x < $1)
+(2 rows)
+
+DEALLOCATE rgq;
+PREPARE rgq2(int) AS
+ SELECT * FROM rg_test WHERE x > $1;
+EXPLAIN (ANALYZE, REF_GENERIC_PLAN true)
+EXECUTE rgq2(500);
+ERROR: EXPLAIN options ANALYZE and REF_GENERIC_PLAN cannot be used together
+DEALLOCATE rgq2;
+RESET enable_seqscan;
+RESET enable_indexonlyscan;
+RESET plan_cache_mode;
+DROP TABLE rg_test;
diff --git a/src/test/regress/sql/prepare.sql b/src/test/regress/sql/prepare.sql
index c6098dc95ce..fd218ecf58b 100644
--- a/src/test/regress/sql/prepare.sql
+++ b/src/test/regress/sql/prepare.sql
@@ -82,3 +82,34 @@ SELECT name, statement, parameter_types, result_types FROM
pg_prepared_statement
DEALLOCATE ALL;
SELECT name, statement, parameter_types FROM pg_prepared_statements
ORDER BY name;
+
+CREATE TABLE rg_test (x int, y int);
+INSERT INTO rg_test SELECT g, g FROM generate_series(1,1000) g;
+CREATE INDEX rg_test_x_idx ON rg_test(x);
+VACUUM ANALYZE rg_test;
+
+SET plan_cache_mode = 'force_ref_generic_plan';
+SET enable_seqscan = off;
+SET enable_indexonlyscan = off;
+
+PREPARE rgq(int) AS
+ SELECT * FROM rg_test WHERE x < $1;
+
+EXPLAIN (COSTS OFF, GENERIC_PLAN, REF_GENERIC_PLAN true)
+EXECUTE rgq(10);
+
+DEALLOCATE rgq;
+
+PREPARE rgq2(int) AS
+ SELECT * FROM rg_test WHERE x > $1;
+
+EXPLAIN (ANALYZE, REF_GENERIC_PLAN true)
+EXECUTE rgq2(500);
+
+DEALLOCATE rgq2;
+
+RESET enable_seqscan;
+RESET enable_indexonlyscan;
+RESET plan_cache_mode;
+DROP TABLE rg_test;
+
--
2.46.0
