PostgreSQL绑定变量SQL
foucus、 人气:0当我们在PostgreSQL中分析一些历史的SQL问题时，往往看到的SQL都是带有绑定变量的。而对于pg，我们没法像Oracle一样通过例如dba_hist_sqlbind之类的视图去获取历史的绑定变量值。不仅如此，对于这些带有绑定变量的SQL，我们甚至没法像在Oracle中一样获取一个预估的执行计划。
在pg中使用explain去执行则会报错：
bill=# explain select * from t1 where id = $1 and info = $2; ERROR: there is no parameter $1 LINE 1: explain select * from t1 where id = $1 and info = $2;
我们似乎只能去通过带入值去获取相应的执行计划了，这对于那些绑定变量很多的SQL来说无疑是十分繁琐的。那有没有什么方法能像Oracle中那样，即使是有绑定变量的SQL，在plsql developer中一个F5就显示了预估的执行计划呢？
我们可以使用prepare语句来实现想要的功能。
例如：
bill=# prepare p1 as select * from t1 where id = $1 and info = $2; PREPARE
可以看到上面的SQL有两个变量，那么我们在不知道变量的情况下怎么去获取执行计划呢？
可以用null，因为这适用于任何数据类型。
但事实往往没有那么乐观：
bill=# explain execute p1(null,null);
QUERY PLAN
------------------------------------------
Result (cost=0.00..0.00 rows=0 width=0)
One-Time Filter: false
(2 rows)
可以看到优化器十分聪明，知道查询的结果中没有行，甚至都不去扫描表了。对于这种情况，我们只需要执行5次，让其生成generic plan。
bill=# explain execute p1(null,null);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.15..2.77 rows=1 width=36)
Index Cond: (id = $1)
Filter: (info = $2)
(3 rows)
当然，如果你的版本是pg12之后的，那么就没必要这么麻烦了，直接设置plan_cache_mode来控制就好。
bill=# prepare p1 as select * from t1 where id =$1 and info = $2;
PREPARE
bill=# set plan_cache_mode = force_generic_plan;
SET
bill=# explain execute p1(null,null);
QUERY PLAN
-------------------------------------------------------------------
Index Scan using t1_pkey on t1 (cost=0.15..2.77 rows=1 width=36)
Index Cond: (id = $1)
Filter: (info = $2)
(3 rows)
如果你的版本是pg12之前的，那么只能执行5次然后等到第6次生成通用的执行计划了。当然还有点需要注意的，如果估计成本高于先前执行的平均成本时就不会选择通用计划了，所以我们可以人为的控制前5次的平均成本，让其达到一个很高的值，这一点我们可以增加cpu_operator_cost的值来实现。
bill=# prepare p1 as select * from t1 where id =$1 and info = $2; bill=# set local cpu_operator_cost=999999; --设置成一个很大的值 bill=# explain execute p1(null,null); bill=# explain execute p1(null,null); bill=# explain execute p1(null,null); bill=# explain execute p1(null,null); bill=# explain execute p1(null,null); bill=# explain execute p1(null,null); --生成通用执行计划
加载全部内容