Skip to content

Commit f7cb284

Browse files
peterepetere
committed
Add plan_cache_mode setting
This allows overriding the choice of custom or generic plan. Author: Pavel Stehule <pavel.stehule@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/CAFj8pRAGLaiEm8ur5DWEBo7qHRWTk9HxkuUAz00CZZtJj-LkCA%40mail.gmail.com
1 parent a06e56b commit f7cb284

File tree

7 files changed

+179
-0
lines changed

7 files changed

+179
-0
lines changed

‎doc/src/sgml/config.sgml

Lines changed: 30 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4616,6 +4616,36 @@ SELECT * FROM parent WHERE key = 2400;
46164616
</listitem>
46174617
</varlistentry>
46184618

4619+
<varlistentry id="guc-plan-cache_mode" xreflabel="plan_cache_mode">
4620+
<term><varname>plan_cache_mode</varname> (<type>enum</type>)
4621+
<indexterm>
4622+
<primary><varname>plan_cache_mode</varname> configuration parameter</primary>
4623+
</indexterm>
4624+
</term>
4625+
<listitem>
4626+
<para>
4627+
Prepared statements (either explicitly prepared or implicitly
4628+
generated, for example in PL/pgSQL) can be executed using custom or
4629+
generic plans. A custom plan is replanned for a new parameter value,
4630+
a generic plan is reused for repeated executions of the prepared
4631+
statement. The choice between them is normally made automatically.
4632+
This setting overrides the default behavior and forces either a custom
4633+
or a generic plan. This can be used to work around performance
4634+
problems in specific cases. Note, however, that the plan cache
4635+
behavior is subject to change, so this setting, like all settings that
4636+
force the planner's hand, should be reevaluated regularly.
4637+
</para>
4638+
4639+
<para>
4640+
The allowed values are <literal>auto</literal>,
4641+
<literal>force_custom_plan</literal> and
4642+
<literal>force_generic_plan</literal>. The default value is
4643+
<literal>auto</literal>. The setting is applied when a cached plan is
4644+
to be executed, not when it is prepared.
4645+
</para>
4646+
</listitem>
4647+
</varlistentry>
4648+
46194649
</variablelist>
46204650
</sect2>
46214651
</sect1>

‎src/backend/utils/cache/plancache.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -106,6 +106,8 @@ static void PlanCacheRelCallback(Datum arg, Oid relid);
106106
static void PlanCacheFuncCallback(Datum arg, int cacheid, uint32 hashvalue);
107107
static void PlanCacheSysCallback(Datum arg, int cacheid, uint32 hashvalue);
108108

109+
/* GUC parameter */
110+
int plan_cache_mode;
109111

110112
/*
111113
* InitPlanCache: initialize module during InitPostgres.
@@ -1033,6 +1035,12 @@ choose_custom_plan(CachedPlanSource *plansource, ParamListInfo boundParams)
10331035
if (IsTransactionStmtPlan(plansource))
10341036
return false;
10351037

1038+
/* Let settings force the decision */
1039+
if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_GENERIC_PLAN)
1040+
return false;
1041+
if (plan_cache_mode == PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN)
1042+
return true;
1043+
10361044
/* See if caller wants to force the decision */
10371045
if (plansource->cursor_options & CURSOR_OPT_GENERIC_PLAN)
10381046
return false;

‎src/backend/utils/misc/guc.c

Lines changed: 19 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -405,6 +405,13 @@ static const struct config_enum_entry force_parallel_mode_options[] = {
405405
{NULL, 0, false}
406406
};
407407

408+
static const struct config_enum_entry plan_cache_mode_options[] = {
409+
{"auto", PLAN_CACHE_MODE_AUTO, false},
410+
{"force_generic_plan", PLAN_CACHE_MODE_FORCE_GENERIC_PLAN, false},
411+
{"force_custom_plan", PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN, false},
412+
{NULL, 0, false}
413+
};
414+
408415
/*
409416
* password_encryption used to be a boolean, so accept all the likely
410417
* variants of "on", too. "off" used to store passwords in plaintext,
@@ -4150,6 +4157,18 @@ static struct config_enum ConfigureNamesEnum[] =
41504157
NULL, NULL, NULL
41514158
},
41524159

4160+
{
4161+
{"plan_cache_mode", PGC_USERSET, QUERY_TUNING_OTHER,
4162+
gettext_noop("Controls the planner's selection of custom or generic plan."),
4163+
gettext_noop("Prepared statements can have custom and generic plans, and the planner "
4164+
"will attempt to choose which is better. This can be set to override "
4165+
"the default behavior.")
4166+
},
4167+
&plan_cache_mode,
4168+
PLAN_CACHE_MODE_AUTO, plan_cache_mode_options,
4169+
NULL, NULL, NULL
4170+
},
4171+
41534172
/* End-of-list marker */
41544173
{
41554174
{NULL, 0, 0, NULL, NULL}, NULL, 0, NULL, NULL, NULL, NULL

‎src/backend/utils/misc/postgresql.conf.sample

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -350,6 +350,7 @@
350350
#join_collapse_limit = 8 # 1 disables collapsing of explicit
351351
# JOIN clauses
352352
#force_parallel_mode = off
353+
#plan_cache_mode = auto
353354

354355

355356
#------------------------------------------------------------------------------

‎src/include/utils/plancache.h

Lines changed: 11 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -182,4 +182,15 @@ extern CachedPlan *GetCachedPlan(CachedPlanSource *plansource,
182182
QueryEnvironment *queryEnv);
183183
extern void ReleaseCachedPlan(CachedPlan *plan, bool useResOwner);
184184

185+
/* possible values for plan_cache_mode */
186+
typedef enum
187+
{
188+
PLAN_CACHE_MODE_AUTO,
189+
PLAN_CACHE_MODE_FORCE_GENERIC_PLAN,
190+
PLAN_CACHE_MODE_FORCE_CUSTOM_PLAN
191+
} PlanCacheMode;
192+
193+
/* GUC parameter */
194+
extern int plan_cache_mode;
195+
185196
#endif /* PLANCACHE_H */

‎src/test/regress/expected/plancache.out

Lines changed: 77 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -278,3 +278,80 @@ drop table pc_list_part_1;
278278
execute pstmt_def_insert(1);
279279
drop table pc_list_parted, pc_list_part_null;
280280
deallocate pstmt_def_insert;
281+
-- Test plan_cache_mode
282+
create table test_mode (a int);
283+
insert into test_mode select 1 from generate_series(1,1000) union all select 2;
284+
create index on test_mode (a);
285+
analyze test_mode;
286+
prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
287+
-- up to 5 executions, custom plan is used
288+
explain (costs off) execute test_mode_pp(2);
289+
QUERY PLAN
290+
----------------------------------------------------------
291+
Aggregate
292+
-> Index Only Scan using test_mode_a_idx on test_mode
293+
Index Cond: (a = 2)
294+
(3 rows)
295+
296+
-- force generic plan
297+
set plan_cache_mode to force_generic_plan;
298+
explain (costs off) execute test_mode_pp(2);
299+
QUERY PLAN
300+
-----------------------------
301+
Aggregate
302+
-> Seq Scan on test_mode
303+
Filter: (a = $1)
304+
(3 rows)
305+
306+
-- get to generic plan by 5 executions
307+
set plan_cache_mode to auto;
308+
execute test_mode_pp(1); -- 1x
309+
count
310+
-------
311+
1000
312+
(1 row)
313+
314+
execute test_mode_pp(1); -- 2x
315+
count
316+
-------
317+
1000
318+
(1 row)
319+
320+
execute test_mode_pp(1); -- 3x
321+
count
322+
-------
323+
1000
324+
(1 row)
325+
326+
execute test_mode_pp(1); -- 4x
327+
count
328+
-------
329+
1000
330+
(1 row)
331+
332+
execute test_mode_pp(1); -- 5x
333+
count
334+
-------
335+
1000
336+
(1 row)
337+
338+
-- we should now get a really bad plan
339+
explain (costs off) execute test_mode_pp(2);
340+
QUERY PLAN
341+
-----------------------------
342+
Aggregate
343+
-> Seq Scan on test_mode
344+
Filter: (a = $1)
345+
(3 rows)
346+
347+
-- but we can force a custom plan
348+
set plan_cache_mode to force_custom_plan;
349+
explain (costs off) execute test_mode_pp(2);
350+
QUERY PLAN
351+
----------------------------------------------------------
352+
Aggregate
353+
-> Index Only Scan using test_mode_a_idx on test_mode
354+
Index Cond: (a = 2)
355+
(3 rows)
356+
357+
drop table test_mode;

‎src/test/regress/sql/plancache.sql

Lines changed: 33 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -177,3 +177,36 @@ drop table pc_list_part_1;
177177
execute pstmt_def_insert(1);
178178
drop table pc_list_parted, pc_list_part_null;
179179
deallocate pstmt_def_insert;
180+
181+
-- Test plan_cache_mode
182+
183+
create table test_mode (a int);
184+
insert into test_mode select 1 from generate_series(1,1000) union all select 2;
185+
create index on test_mode (a);
186+
analyze test_mode;
187+
188+
prepare test_mode_pp (int) as select count(*) from test_mode where a = $1;
189+
190+
-- up to 5 executions, custom plan is used
191+
explain (costs off) execute test_mode_pp(2);
192+
193+
-- force generic plan
194+
set plan_cache_mode to force_generic_plan;
195+
explain (costs off) execute test_mode_pp(2);
196+
197+
-- get to generic plan by 5 executions
198+
set plan_cache_mode to auto;
199+
execute test_mode_pp(1); -- 1x
200+
execute test_mode_pp(1); -- 2x
201+
execute test_mode_pp(1); -- 3x
202+
execute test_mode_pp(1); -- 4x
203+
execute test_mode_pp(1); -- 5x
204+
205+
-- we should now get a really bad plan
206+
explain (costs off) execute test_mode_pp(2);
207+
208+
-- but we can force a custom plan
209+
set plan_cache_mode to force_custom_plan;
210+
explain (costs off) execute test_mode_pp(2);
211+
212+
drop table test_mode;

0 commit comments

Comments
 (0)