Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitf7cb284

Browse files
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 parenta06e56b commitf7cb284

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
staticvoidPlanCacheFuncCallback(Datumarg,intcacheid,uint32hashvalue);
107107
staticvoidPlanCacheSysCallback(Datumarg,intcacheid,uint32hashvalue);
108108

109+
/* GUC parameter */
110+
intplan_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+
staticconststructconfig_enum_entryplan_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
externvoidReleaseCachedPlan(CachedPlan*plan,booluseResOwner);
184184

185+
/* possible values for plan_cache_mode */
186+
typedefenum
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+
externintplan_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
droptable pc_list_parted, pc_list_part_null;
179179
deallocate pstmt_def_insert;
180+
181+
-- Test plan_cache_mode
182+
183+
createtabletest_mode (aint);
184+
insert into test_modeselect1from generate_series(1,1000)union allselect2;
185+
createindexon test_mode (a);
186+
analyze test_mode;
187+
188+
prepare test_mode_pp (int)asselectcount(*)from test_modewhere 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+
droptable test_mode;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp