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

Posted on by

當我們在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); --生成通用執行計劃

到此這篇關於PostgreSQL查看帶有綁定變量SQL的通用方法詳解的文章就介紹到這瞭,更多相關PostgreSQL綁定變量SQL內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：