diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/utils/cache/plancache.c | 8 | ||||
| -rw-r--r-- | src/backend/utils/misc/guc.c | 19 | ||||
| -rw-r--r-- | src/backend/utils/misc/postgresql.conf.sample | 1 | ||||
| -rw-r--r-- | src/include/utils/plancache.h | 11 | ||||
| -rw-r--r-- | src/test/regress/expected/plancache.out | 77 | ||||
| -rw-r--r-- | src/test/regress/sql/plancache.sql | 33 |
6 files changed, 149 insertions, 0 deletions
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; |