aboutsummaryrefslogtreecommitdiff
diff options
context:
space:
mode:
Diffstat
-rw-r--r--doc/src/sgml/config.sgml30
-rw-r--r--src/backend/utils/cache/plancache.c8
-rw-r--r--src/backend/utils/misc/guc.c19
-rw-r--r--src/backend/utils/misc/postgresql.conf.sample1
-rw-r--r--src/include/utils/plancache.h11
-rw-r--r--src/test/regress/expected/plancache.out77
-rw-r--r--src/test/regress/sql/plancache.sql33
7 files changed, 179 insertions, 0 deletions
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e307bb4e8e9..4d48d93305a 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4616,6 +4616,36 @@ SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
+ <varlistentry id="guc-plan-cache_mode" xreflabel="plan_cache_mode">
+ <term><varname>plan_cache_mode</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>plan_cache_mode</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Prepared statements (either explicitly prepared or implicitly
+ generated, for example in PL/pgSQL) can be executed using custom or
+ generic plans. A custom plan is replanned for a new parameter value,
+ a generic plan is reused for repeated executions of the prepared
+ statement. The choice between them is normally made automatically.
+ This setting overrides the default behavior and forces either a custom
+ or a generic plan. This can be used to work around performance
+ problems in specific cases. Note, however, that the plan cache
+ behavior is subject to change, so this setting, like all settings that
+ force the planner's hand, should be reevaluated regularly.
+ </para>
+
+ <para>
+ The allowed values are <literal>auto</literal>,
+ <literal>force_custom_plan</literal> and
+ <literal>force_generic_plan</literal>. The default value is
+ <literal>auto</literal>. The setting is applied when a cached plan is
+ to be executed, not when it is prepared.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
</sect1>
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 0ad3e3c7366..7271b5880b8 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -106,6 +106,8 @@ static void PlanCacheRelCallback(Datum arg, Oid relid);
static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
+/* GUC parameter */
+int plan_cache_mode;
/*
* InitPlanCache: initialize module during InitPostgres.
@@ -1033,6 +1035,12 @@ choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
if (IsTransactionStmtPlan(plansource))
return false;
+ /* Let settings force the decision */
+ if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_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)
return false;
diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c
index 17292e04fed..a88ea6cfc9e 100644
--- a/src/backend/utils/misc/guc.c
+++ b/src/backend/utils/misc/guc.c
@@ -405,6 +405,13 @@ static const struct config_enum_entry force_parallel_mode_options[] = {
{NULL, 0, false}
};
+static const struct config_enum_entry plan_cache_mode_options[] = {
+ {"auto", PLAN_CACHE_MODE_AUTO, false},
+ {"force_generic_plan", PLAN_CACHE_MODE_FORCE_GENERIC_PLAN, false},
+ {"force_custom_plan", PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN, false},
+ {NULL, 0, false}
+};
+
/*
* password_encryption used to be a boolean, so accept all the likely
* variants of "on", too. "off" used to store passwords in plaintext,
@@ -4150,6 +4157,18 @@ static struct config_enum ConfigureNamesEnum[] =
NULL, NULL, NULL
},
+ {
+ {"plan_cache_mode", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Controls the planner's selection of custom or generic plan."),
+ gettext_noop("Prepared statements can have custom and generic plans, and the planner "
+ "will attempt to choose which is better. This can be set to override "
+ "the default behavior.")
+ },
+ &plan_cache_mode,
+ PLAN_CACHE_MODE_AUTO, plan_cache_mode_options,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/backend/utils/misc/postgresql.conf.sample b/src/backend/utils/misc/postgresql.conf.sample
index 657c3f81f8f..c0d3fb84911 100644
--- a/src/backend/utils/misc/postgresql.conf.sample
+++ b/src/backend/utils/misc/postgresql.conf.sample
@@ -350,6 +350,7 @@
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#force_parallel_mode = off
+#plan_cache_mode = auto
#------------------------------------------------------------------------------
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index ab20aa04b0a..5fc7903a068 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -182,4 +182,15 @@ extern CachedPlan *GetCachedPlan(CachedPlanSource *plansource,
QueryEnvironment *queryEnv);
extern void ReleaseCachedPlan(CachedPlan *plan, bool useResOwner);
+/* possible values for plan_cache_mode */
+typedef enum
+{
+ PLAN_CACHE_MODE_AUTO,
+ PLAN_CACHE_MODE_FORCE_GENERIC_PLAN,
+ PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN
+} PlanCacheMode;
+
+/* GUC parameter */
+extern int plan_cache_mode;
+
#endif /* PLANCACHE_H */
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index 3086c685660..7d289b8c5e7 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -278,3 +278,80 @@ drop table pc_list_part_1;
execute pstmt_def_insert(1);
drop table pc_list_parted, pc_list_part_null;
deallocate pstmt_def_insert;
+-- Test plan_cache_mode
+create table test_mode (a int);
+insert into test_mode select 1 from generate_series(1,1000) union all select 2;
+create index on test_mode (a);
+analyze test_mode;
+prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
+-- up to 5 executions, custom plan is used
+explain (costs off) execute test_mode_pp(2);
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using test_mode_a_idx on test_mode
+ Index Cond: (a = 2)
+(3 rows)
+
+-- force generic plan
+set plan_cache_mode to force_generic_plan;
+explain (costs off) execute test_mode_pp(2);
+ QUERY PLAN
+-----------------------------
+ Aggregate
+ -> Seq Scan on test_mode
+ Filter: (a = $1)
+(3 rows)
+
+-- get to generic plan by 5 executions
+set plan_cache_mode to auto;
+execute test_mode_pp(1); -- 1x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_mode_pp(1); -- 2x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_mode_pp(1); -- 3x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_mode_pp(1); -- 4x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_mode_pp(1); -- 5x
+ count
+-------
+ 1000
+(1 row)
+
+-- we should now get a really bad plan
+explain (costs off) execute test_mode_pp(2);
+ QUERY PLAN
+-----------------------------
+ Aggregate
+ -> Seq Scan on test_mode
+ Filter: (a = $1)
+(3 rows)
+
+-- but we can force a custom plan
+set plan_cache_mode to force_custom_plan;
+explain (costs off) execute test_mode_pp(2);
+ QUERY PLAN
+----------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using test_mode_a_idx on test_mode
+ Index Cond: (a = 2)
+(3 rows)
+
+drop table test_mode;
diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql
index d9439b83ab5..fa218c8d217 100644
--- a/src/test/regress/sql/plancache.sql
+++ b/src/test/regress/sql/plancache.sql
@@ -177,3 +177,36 @@ drop table pc_list_part_1;
execute pstmt_def_insert(1);
drop table pc_list_parted, pc_list_part_null;
deallocate pstmt_def_insert;
+
+-- Test plan_cache_mode
+
+create table test_mode (a int);
+insert into test_mode select 1 from generate_series(1,1000) union all select 2;
+create index on test_mode (a);
+analyze test_mode;
+
+prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
+
+-- up to 5 executions, custom plan is used
+explain (costs off) execute test_mode_pp(2);
+
+-- force generic plan
+set plan_cache_mode to force_generic_plan;
+explain (costs off) execute test_mode_pp(2);
+
+-- get to generic plan by 5 executions
+set plan_cache_mode to auto;
+execute test_mode_pp(1); -- 1x
+execute test_mode_pp(1); -- 2x
+execute test_mode_pp(1); -- 3x
+execute test_mode_pp(1); -- 4x
+execute test_mode_pp(1); -- 5x
+
+-- we should now get a really bad plan
+explain (costs off) execute test_mode_pp(2);
+
+-- but we can force a custom plan
+set plan_cache_mode to force_custom_plan;
+explain (costs off) execute test_mode_pp(2);
+
+drop table test_mode;
generated by cgit v1.2.3 (git 2.25.1) at 2025-05-25 18:18:26 +0000