PostgreSQL 数据库性能绑定变量窥探机制

#PostgreSQL #数据库性能 #绑定变量窥探机制

1 本章背景知识

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

2 KingbaseES 窥探机制

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

前5次执行时，每次都会根据实际传入的实际绑定变量新生成执行计划进行执行，即每次都是硬解析，同时会记录这5次的执行计划；
当第6次开始执行时，会生成一个通用的执行计划（generic plan），同时与前5次的执行计划进行比较，如果比较的结果是通用执行计划不比前5次的执行计划差，以后就会把这个通用的执行计划固定下来，这之后即使传入的值发生变化后，执行计划也不再变化。这就相当于Oracle打开了绑定变量窥视的功能。
当然，当第6次开始执行时，如果通用的执行计划（generic plan）比前5次的某一个执行计划差，则以后则每次都重新生成执行计划，即以后永远都是硬解析了。

3 测试验证

3.1 构建测试数据

CREATE TABLE t01(id integer,name text);
INSERT INTO t01 SELECT 1,repeat('a',100) 
FROM generate_series(1,1000000);
INSERT INTO t01 SELECT 2,repeat('b',100) ;
CREATE INDEX idx_t1_id on t1(id);
ANALYZE t01;

3.2 `PREPARE`创建预准备语句

 PREPARE t01_plan(integer) 
 AS SELECT count(*) 
 FROM t01 where id=$1;

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

3.3 窥探机制测试

3.3.1 通用执行计划

1、 PREPARE 创建预准备语句

PREPARE t1_plan(integer) AS SELECT * FROM t01 WHERE id=$1;

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)

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)

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)

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)

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)
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)

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)

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)登录后复制

Note

结论：可以看到，第6次执行时，执行计划显示变为 id=$1，说明执行计划变成通用执行计划了。后续，即使传入的值是 2，也不会走索引。

3.3.2 非通用执行计划

PREPARE t1_plan(integer) AS SELECT * FROM t01 WHERE id=$1;
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)

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)

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)

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)

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)

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)
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)

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)

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)

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)

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)

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)
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)

Note

结论：如果第6次与前5次执行计划是不一致的，后续都不会走通用的执行计划。本例中，哪怕后续连续超过 5次传入同一值，都不会固定执行计划。

4 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。