网站首页编程语言正文

PostgreSQL查看带有绑定变量SQL的通用方法详解_PostgreSQL

作者：foucus、更新时间： 2022-11-08 编程语言

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

原文链接：https://foucus.blog.csdn.net/article/details/126838323

上一篇：Python Pandas教程之series 上的转换操作_
下一篇：Python Panda中索引和选择 series 的数据_

网站首页 编程语言 正文

PostgreSQL查看带有绑定变量SQL的通用方法详解_PostgreSQL

相关推荐

网站首页编程语言正文