PostgreSQL 12: 新增 plan_cache_mode 参数设置执行计划策略

PostgreSQL 执行 SQL 时,包括SQL语义分析、SQL解析、执行计划生成、执行SQL等阶段,当SQL语句需要经历以上所有阶段时,Oracle 中通常称之为硬解析,而软解析可以复用已有的SQL执行计划,避免了执行计划的生成。

执行计划的生成的代价通常是较大的,特别是同一条 SQL,若执行量较大,软件析提升的累积性能越明显。

类似的,PostgreSQL 执行计划分为两种类型,如下:

  • custom plan: 执行计划重新生成,执行计划中使用常量,相当于 Oracle 中的硬解析
  • generic plan: 执行计划重用,执行计划中使用变量,变量符为 $n,相当于 Oracle 中的软解析

默认情况下 PostgreSQL 会使用 custom plan 或 generic plan,PostgreSQL 12 新增 plan_cache_mode 参数,可以设置优化器策略,发行说明如下。

发行说明

Allow control over when generic plans are used for prepared statements (Pavel Stěhule)

This is controlled by the plan_cache_mode server variable.

补丁说明

1
2
3
4
5
6
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

参数说明

plan_cache_mode (enum)

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.

The allowed values are auto, force_custom_plan and force_generic_plan. The default value is auto. The setting is applied when a cached plan is to be executed, not when it is prepared.

12 版本新增的 plan_cache_mode 参数可以设置优化器策略,参数可选值为以下:

  • auto: 优化器策略为 custom plan 或 generic plan
  • force_custom_plan: 设置优化器策略为 custom plan
  • force_generic_plan: 设置优化器策略为 generic plan

环境准备

创建测试表并插入数据,如下:

1
2
3
4
CREATE TABLE t1 (id int4,name text);
CREATE TABLE
INSERT INTO t1(id,name) SELECT n, 'francs_'||n FROM generate_series(1,10000) n;
ALTER TABLE t1 ADD PRIMARY KEY (ID);

测试: plan_cache_mode = auto

plan_cache_mode 参数默认为 auto,做以下测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
postgres=# show plan_cache_mode ;
plan_cache_mode
-----------------
auto
(1 row)

postgres=# PREPARE s(int4) AS SELECT * FROM t1 WHERE id = $1;
PREPARE

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = 1)
(2 rows)

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = 1)
(2 rows)

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = 1)
(2 rows)

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = 1)
(2 rows)

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = 1)
(2 rows)

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = $1)
(2 rows)

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = $1)
(2 rows)

观察执行计划 Index Cound 这行,前 5 次执行计划中的条件使用的是常量,使用的是 custom plan,第 6 次开始执行计划中使用了变量 $1,使用的是 generic plan。

测试: plan_cache_mode = force_generic_plan

会话级设置 plan_cache_mode 参数为 force_generic_plan,做以下测试:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
postgres=# SET plan_cache_mode = force_generic_plan ;
SET

postgres=# DEALLOCATE s;
DEALLOCATE

postgres=# PREPARE s(int4) AS SELECT * FROM t1 WHERE id = $1;
PREPARE

postgres=# EXPLAIN EXECUTE s(1);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.29..8.30 rows=1 width=15)
Index Cond: (id = $1)
(2 rows)

plan_cache_mode 参数设置成 force_generic_plan 后,执行计划策略立刻调整为 generic plan。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%