快捷導(dǎo)航

PostgreSQL查看帶有綁定變量SQL的通用方法詳解

更新時(shí)間：2022年09月16日 10:12:27 作者：foucus、

今天我們要探討的是 custom執(zhí)行計(jì)劃和通用執(zhí)行計(jì)劃。這一技術(shù)在 Oracle中被稱為綁定變量窺視。但 Postgresql中并沒(méi)有這樣的定義，更嚴(yán)格地說(shuō)，Postgresql叫做custom執(zhí)行計(jì)劃和通用執(zhí)行計(jì)劃

當(dāng)我們?cè)赑ostgreSQL中分析一些歷史的SQL問(wèn)題時(shí)，往往看到的SQL都是帶有綁定變量的。而對(duì)于pg，我們沒(méi)法像Oracle一樣通過(guò)例如dba_hist_sqlbind之類的視圖去獲取歷史的綁定變量值。不僅如此，對(duì)于這些帶有綁定變量的SQL，我們甚至沒(méi)法像在Oracle中一樣獲取一個(gè)預(yù)估的執(zhí)行計(jì)劃。

在pg中使用explain去執(zhí)行則會(huì)報(bào)錯(cuò)：

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;

我們似乎只能去通過(guò)帶入值去獲取相應(yīng)的執(zhí)行計(jì)劃了，這對(duì)于那些綁定變量很多的SQL來(lái)說(shuō)無(wú)疑是十分繁瑣的。那有沒(méi)有什么方法能像Oracle中那樣，即使是有綁定變量的SQL，在plsql developer中一個(gè)F5就顯示了預(yù)估的執(zhí)行計(jì)劃呢？

我們可以使用prepare語(yǔ)句來(lái)實(shí)現(xiàn)想要的功能。

例如：

bill=# prepare p1 as select * from t1 where id = $1 and info = $2;
PREPARE

可以看到上面的SQL有兩個(gè)變量，那么我們?cè)诓恢雷兞康那闆r下怎么去獲取執(zhí)行計(jì)劃呢？

可以用null，因?yàn)檫@適用于任何數(shù)據(jù)類型。

但事實(shí)往往沒(méi)有那么樂(lè)觀：

bill=# explain execute p1(null,null);
                QUERY PLAN
------------------------------------------
 Result  (cost=0.00..0.00 rows=0 width=0)
   One-Time Filter: false
(2 rows)

可以看到優(yōu)化器十分聰明，知道查詢的結(jié)果中沒(méi)有行，甚至都不去掃描表了。對(duì)于這種情況，我們只需要執(zhí)行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)

當(dāng)然，如果你的版本是pg12之后的，那么就沒(méi)必要這么麻煩了，直接設(shè)置plan_cache_mode來(lái)控制就好。

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之前的，那么只能執(zhí)行5次然后等到第6次生成通用的執(zhí)行計(jì)劃了。當(dāng)然還有點(diǎn)需要注意的，如果估計(jì)成本高于先前執(zhí)行的平均成本時(shí)就不會(huì)選擇通用計(jì)劃了，所以我們可以人為的控制前5次的平均成本，讓其達(dá)到一個(gè)很高的值，這一點(diǎn)我們可以增加cpu_operator_cost的值來(lái)實(shí)現(xiàn)。

bill=# prepare p1 as select * from t1 where id =$1 and info = $2;
bill=# set local cpu_operator_cost=999999; --設(shè)置成一個(gè)很大的值
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); --生成通用執(zhí)行計(jì)劃

到此這篇關(guān)于PostgreSQL查看帶有綁定變量SQL的通用方法詳解的文章就介紹到這了,更多相關(guān)PostgreSQL綁定變量SQL內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: