crt oracle查看执行计划,PostgreSQL自定义执行计划缓存模式——plan_cache_mode

PostgreSQL12中新增plan_cache_mode参数，用来改变执行计划的缓存模式。

详细介绍：

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.

该参数有四个可选的值：auto、default、force_custom_plan、force_generic_plan。

#plan_cache_mode = auto # auto, force_generic_plan or

# force_custom_plan

其中default值就是auto，即自动修正执行计划。

force_custom_plan：为每个指定的参数重新创建新的执行计划。

force_generic_plan：不依赖与参数的值，执行计划可以在各个语句间重复使用。

https://www.cndba.cn/foucus/article/4137

例子：

先构建一张测试表：https://www.cndba.cn/foucus/article/4137

create table test (id int , info text, crt_time timestamp);

insert into test select generate_series(1,1000000);

insert into test select 1 from generate_series(1,10000000);

create index idx_test on test(id);

显然该表查询id=1会走全表扫描(大部分记录id都为1)，而id为其他值可以走索引扫描。

我们先看看plan_cache_mode=auto，即默认情况下：https://www.cndba.cn/foucus/article/4137https://www.cndba.cn/foucus/article/4137

bill@bill=>prepare plan1 as select * from test where id = ($1);

PREPARE

bill@bill=>explain execute plan1(1);

QUERY PLAN

-----------------------------------------------------------------

Seq Scan on test (cost=0.00..186172.24 rows=10012511 width=44)

Filter: (id = 1)

(2 rows)

bill@bill=>explain execute plan1(2);

QUERY PLAN

--------------------------------------------------------------------------

Index Scan using idx_test on test (cost=0.43..328.59 rows=334 width=44)

Index Cond: (id = 2)

(2 rows)

果然如我们预测的一样，id=1和id=2一个是全表扫描一个是索引扫描，说明针对不同的值生成了不同的执行计划。

将参数值改成force_generic_plan再看看：https://www.cndba.cn/foucus/article/4137

bill@bill=>set plan_cache_mode TO force_generic_plan;

SET

bill@bill=>explain execute plan1(1);

QUERY PLAN

----------------------------------------------------------------------------

Index Scan using idx_test on test (cost=0.43..3529.93 rows=3717 width=44)

Index Cond: (id = $1)

(2 rows)

bill@bill=>explain execute plan1(2);

QUERY PLAN

----------------------------------------------------------------------------

Index Scan using idx_test on test (cost=0.43..3529.93 rows=3717 width=44)

Index Cond: (id = $1)

(2 rows)

这回查询id=1也是索引扫描，却也使用了索引扫描，说明无论参数值是什么执行计划都被固定了。https://www.cndba.cn/foucus/article/4137

另外我们可以针对不同的user和database来设置该参数，如：https://www.cndba.cn/foucus/article/4137https://www.cndba.cn/foucus/article/4137

bill@bill=>alter user bill set plan_cache_mode to force_generic_plan ;

ALTER ROLE

bill@bill=>alter database bill set plan_cache_mode to force_custom_plan ;

ALTER DATABASE

小结：

1、对于OLAP(复杂分析查询)由于并发低，同时每一次请求的条件输入评估选择性可能差异较大，每条SQL(只是输入参数，WHERE条件不一样)也许使用不同的执行计划才能达到最佳的执行效率，使用同一个执行计划并不适合这类OLAP SQL，因此OLAP系统，建议可以使用force_custom_plan。sql bill@bill=>alter user bill set plan_cache_mode to force_generic_plan ; ALTER ROLE bill@bill=>alter database bill set plan_cache_mode to force_custom_plan ; ALTER DATABASE

2、而对于OLTP请求，并发高，数据倾斜较少，建议使用AUTO。(自动修正)，如果数据可以保证完全不可能倾斜，可以采用force_generic_plan。

https://www.cndba.cn/foucus/article/4137https://www.cndba.cn/foucus/article/4137

版权声明：本文为博主原创文章，未经博主允许不得转载。