Postgresql 绑定变量窥探机制

一、内容概述

对于数据严重倾斜的,制定执行计划时,如果不知道变量的值,可能导致生成的执行计划效率低下。极端如以下例子,不同的传入值,可能执行计划不同。对于绑定变量的情况,我们知道Oracle 有 _optim_peek_user_binds 参数,控制是否启用变量窥探。KingbaseES 也有类似参数,控制是否启用变量窥探。

二、KingbaseES 窥探机制

KingbaseES 采用以下判断机制,决定是否固定执行计划:

  • 前5次执行时,每次都会根据实际传入的实际绑定变量新生成执行计划进行执行,即每次都是硬解析,同时会记录这5次的执行计划;

  • 当第6次开始执行时,会生成一个通用的执行计划(generic plan),同时与前5次的执行计划进行比较,如果比较的结果是通用执行计划不比前5次的执行计划差,以后就会把这个通用的执行计划固定下来,这之后即使传入的值发生变化后,执行计划也不再变化。这就相当于Oracle打开了绑定变量窥视的功能。

  • 当然,当第6次开始执行时,如果通用的执行计划(generic plan)比前5次的某一个执行计划差,则以后则每次都重新生成执行计划,即以后永远都是硬解析了。

三、测试验证

1、构建测试数据

create table t1(id integer,name text);
insert into t1 select 1,repeat('a',100) from generate_series(1,1000000);
insert into t1 select 2,repeat('b',100) ;
create index ind_t1_id on t1(id);
analyze t1;
prepare t1_plan(integer) AS select count(*) from t1 where id=$1;

从构造的数据看,明显不同的传入值,需要采用不同的执行计划。

2、测试窥探机制

测试一:

test=# prepare t1_plan(integer) AS select * from t1 where id=$1;
PREPARE
test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = $1)
(2 rows)

test=# explain execute t1_plan(2);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = $1)
(2 rows)

test=# explain execute t1_plan(2);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = $1)
(2 rows)
结论:可以看到,第6次执行时,执行计划显示变为 id=$1,说明执行计划变成通用执行计划了。后续,即使传入的 值是 2,也不会走索引。

测试二:

test=# prepare t1_plan(integer) AS select * from t1 where id=$1;
PREPARE
test=# explain execute t1_plan(2);
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..4.44 rows=1 width=105)
   Index Cond: (id = 2)
(2 rows)

test=# explain execute t1_plan(2);
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..4.44 rows=1 width=105)
   Index Cond: (id = 2)
(2 rows)

test=# explain execute t1_plan(2);
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..4.44 rows=1 width=105)
   Index Cond: (id = 2)
(2 rows)

test=# explain execute t1_plan(2);
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..4.44 rows=1 width=105)
   Index Cond: (id = 2)
(2 rows)

test=# explain execute t1_plan(2);
                              QUERY PLAN
----------------------------------------------------------------------
 Index Scan using ind_t1_id on t1  (cost=0.42..4.44 rows=1 width=105)
   Index Cond: (id = 2)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)

test=# explain execute t1_plan(1);
                          QUERY PLAN
--------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..29742.01 rows=1000001 width=105)
   Filter: (id = 1)
(2 rows)
结论:如果第6次与前5次执行计划是不一致的,后续都不会走通用的执行计划。本例中,哪怕后续连续超过 5次 传入同一值,都不会固定执行计划。

四、KingbaseES 窥探机制总结

与Oracle 相比,KES需要前 5 次执行绑定变量的SQL,都会窥探变量值,只有在执行计划都一致时,第6次执行时才会固定执行计划。这种机制相比于Oracle,出现执行计划错误的概率更低,但是还是有一定的几率。为了解决该问题,KingbaseES提供参数,可以关闭变量窥探机制。

plan_cache_mode 参数控制是否固定执行计划(执行计划共享),还是永远进行硬解析。可以取以下三个值:

  • auto: 默认值,即根据以上的机制选择是否固定执行计划。
  • force_custom_plan: 关闭绑定变量窥视,永远进行硬解析。
  • force_generic_plan: 强制走通用的固定执行计划(generic plan),不存在需要第6次执行固定执行计划,第1次执行时就已经固定执行计划。比如:是否走索引是根据distinct 值的数量,而不是第一个传入的变量值。针对上例,不管传入何值,都是走Seq Scan。
posted @ 2023-08-22 15:56  数据库集中营  阅读(121)  评论(0编辑  收藏  举报