亲宝软件园·资讯

展开

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); --生成通用执行计划

加载全部内容

相关教程
猜你喜欢
用户评论