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

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

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

當我們在PostgreSQL中分析一些歷史的SQL問題時，往往看到的SQL都是帶有綁定變量的。而對于pg，我們沒法像Oracle一樣通過例如dba_hist_sqlbind之類的視圖去獲取歷史的綁定變量值。不僅如此，對于這些帶有綁定變量的SQL，我們甚至沒法像在Oracle中一樣獲取一個預估的執(zhí)行計劃。

在pg中使用explain去執(zhí)行則會報錯：

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;

我們似乎只能去通過帶入值去獲取相應的執(zhí)行計劃了，這對于那些綁定變量很多的SQL來說無疑是十分繁瑣的。那有沒有什么方法能像Oracle中那樣，即使是有綁定變量的SQL，在plsql developer中一個F5就顯示了預估的執(zhí)行計劃呢？

我們可以使用prepare語句來實現(xiàn)想要的功能。

例如：

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

可以看到上面的SQL有兩個變量，那么我們在不知道變量的情況下怎么去獲取執(zhí)行計劃呢？

可以用null，因為這適用于任何數(shù)據(jù)類型。

但事實往往沒有那么樂觀：

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)化器十分聰明，知道查詢的結果中沒有行，甚至都不去掃描表了。對于這種情況，我們只需要執(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)

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

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); --生成通用執(zhí)行計劃

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

您可能感興趣的文章: