Hi
2018-07-10 12:01 GMT+02:00 Peter Eisentraut <
peter.eisentr...@2ndquadrant.com>:
> On 23.01.18 17:08, Pavel Stehule wrote:
> > attached updated patch
>
> This appears to be the patch of record in this thread. I think there is
> general desire for adding a setting like this, and the implementation is
> simple enough.
>
> One change perhaps: How about naming the default setting "auto" instead
> of "default". That makes it clearer what it does.
>
I changed "default" to "auto"
updated patch attached
Regards
Pavel
>
> --
> Peter Eisentraut http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
commit 456e1f284cdc7b5a024004e69deb53effccf7427
Author: Pavel Stehule <pavel.steh...@gmail.com>
Date: Thu Jul 12 11:09:54 2018 +0200
initial
diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index e307bb4e8e..dc254b067c 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -4616,6 +4616,31 @@ SELECT * FROM parent WHERE key = 2400;
</listitem>
</varlistentry>
+ </variablelist>
+ </sect2>
+
+ <sect2 id="runtime-config-query-plancache">
+ <title>Plan Cache Options</title>
+
+ <variablelist>
+
+ <varlistentry id="guc-plancache_mode" xreflabel="plancache_mode">
+ <term><varname>plancache_mode</varname> (<type>enum</type>)
+ <indexterm>
+ <primary><varname>plancache_mode</varname> configuration parameter</primary>
+ </indexterm>
+ </term>
+ <listitem>
+ <para>
+ Prepared queries have custom and generic plans and the planner
+ will attempt to choose which is better; this can be set to override
+ the default behavior. 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>.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</sect2>
</sect1>
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index 0ad3e3c736..67cdfb63b1 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 plancache_mode;
/*
* InitPlanCache: initialize module during InitPostgres.
@@ -1033,6 +1035,12 @@ choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
if (IsTransactionStmtPlan(plansource))
return false;
+ /* See if settings wants to force the decision */
+ if (plancache_mode == PLANCACHE_FORCE_GENERIC_PLAN)
+ return false;
+ if (plancache_mode == PLANCACHE_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 17292e04fe..7b1a2921b4 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 plancache_mode_options[] = {
+ {"auto", PLANCACHE_AUTO, false},
+ {"force_generic_plan", PLANCACHE_FORCE_GENERIC_PLAN, false},
+ {"force_custom_plan", PLANCACHE_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
},
+ {
+ {"plancache_mode", PGC_USERSET, QUERY_TUNING_OTHER,
+ gettext_noop("Controls the planner's selection of custom or generic plan."),
+ gettext_noop("Prepared queries have custom and generic plans and the planner "
+ "will attempt to choose which is better; this can be set to override "
+ "the default behavior.")
+ },
+ &plancache_mode,
+ PLANCACHE_AUTO, plancache_mode_options,
+ NULL, NULL, NULL
+ },
+
/* End-of-list marker */
{
{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index ab20aa04b0..cf8a0dd020 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -182,4 +182,16 @@ extern CachedPlan *GetCachedPlan(CachedPlanSource *plansource,
QueryEnvironment *queryEnv);
extern void ReleaseCachedPlan(CachedPlan *plan, bool useResOwner);
+/* possible values for plancache_mode */
+typedef enum
+{
+ PLANCACHE_AUTO,
+ PLANCACHE_FORCE_GENERIC_PLAN,
+ PLANCACHE_FORCE_CUSTOM_PLAN
+} PlanCacheMode;
+
+
+/* GUC parameter */
+extern int plancache_mode;
+
#endif /* PLANCACHE_H */
diff --git a/src/test/regress/expected/plancache.out b/src/test/regress/expected/plancache.out
index 3086c68566..d8255915d6 100644
--- a/src/test/regress/expected/plancache.out
+++ b/src/test/regress/expected/plancache.out
@@ -278,3 +278,82 @@ 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 strategy
+--
+create table test_strategy(a int);
+insert into test_strategy select 1 from generate_series(1,1000) union all select 2;
+create index on test_strategy(a);
+analyze test_strategy;
+prepare test_strategy_pp(int) as select count(*) from test_strategy where a = $1;
+-- without 5 evaluation pg uses custom plan
+explain (costs off) execute test_strategy_pp(2);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using test_strategy_a_idx on test_strategy
+ Index Cond: (a = 2)
+(3 rows)
+
+-- we can force to generic plan
+set plancache_mode to force_generic_plan;
+explain (costs off) execute test_strategy_pp(2);
+ QUERY PLAN
+---------------------------------
+ Aggregate
+ -> Seq Scan on test_strategy
+ Filter: (a = $1)
+(3 rows)
+
+-- we can fix generic plan by 5 execution
+set plancache_mode to default;
+execute test_strategy_pp(1); -- 1x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_strategy_pp(1); -- 2x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_strategy_pp(1); -- 3x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_strategy_pp(1); -- 4x
+ count
+-------
+ 1000
+(1 row)
+
+execute test_strategy_pp(1); -- 5x
+ count
+-------
+ 1000
+(1 row)
+
+-- we should to get really bad plan
+explain (costs off) execute test_strategy_pp(2);
+ QUERY PLAN
+---------------------------------
+ Aggregate
+ -> Seq Scan on test_strategy
+ Filter: (a = $1)
+(3 rows)
+
+-- but we can force to custom plan
+set plancache_mode to force_custom_plan;
+explain (costs off) execute test_strategy_pp(2);
+ QUERY PLAN
+------------------------------------------------------------------
+ Aggregate
+ -> Index Only Scan using test_strategy_a_idx on test_strategy
+ Index Cond: (a = 2)
+(3 rows)
+
+drop table test_strategy;
diff --git a/src/test/regress/sql/plancache.sql b/src/test/regress/sql/plancache.sql
index d9439b83ab..f47e3b3279 100644
--- a/src/test/regress/sql/plancache.sql
+++ b/src/test/regress/sql/plancache.sql
@@ -177,3 +177,37 @@ 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 strategy
+--
+create table test_strategy(a int);
+insert into test_strategy select 1 from generate_series(1,1000) union all select 2;
+create index on test_strategy(a);
+analyze test_strategy;
+
+prepare test_strategy_pp(int) as select count(*) from test_strategy where a = $1;
+
+-- without 5 evaluation pg uses custom plan
+explain (costs off) execute test_strategy_pp(2);
+
+-- we can force to generic plan
+set plancache_mode to force_generic_plan;
+explain (costs off) execute test_strategy_pp(2);
+
+-- we can fix generic plan by 5 execution
+set plancache_mode to default;
+execute test_strategy_pp(1); -- 1x
+execute test_strategy_pp(1); -- 2x
+execute test_strategy_pp(1); -- 3x
+execute test_strategy_pp(1); -- 4x
+execute test_strategy_pp(1); -- 5x
+
+-- we should to get really bad plan
+explain (costs off) execute test_strategy_pp(2);
+
+-- but we can force to custom plan
+set plancache_mode to force_custom_plan;
+explain (costs off) execute test_strategy_pp(2);
+
+drop table test_strategy;
