嘗試使用 pg_store_plans，可以對標 v-sql_pla
前言
在 Oracle 數據庫中，我們可以通過 v$sql_plan 查看執行計劃，但是在 PostgreSQL 中，等同於這個視圖的功能竟然沒有。在網上搜索了一下，發現 pg_store_plans 可以實現這個功能。
安裝
我們從 github 上下載軟件包來進行編譯安裝。
wget -c https://github.com/ossc-db/pg_store_plans/archive/refs/tags/1.5.zip
--解壓後，進入解壓後的文件夾，執行make & make install。
unzip 1.5.zip
make USE_PGXS=1
make USE_PGXS=1 install編譯安裝完成後，需要在 shared_preload_libraries 中設置參數 pg_store_plans。
shared_preload_libraries='pg_store_plans'最後再創建 extension 就可以用了
create extension pg_store_plans;建完就會有一個新的視圖 pg_store_plans，並且已經開始存儲執行計劃的數據了。
test=# \d pg_store_plans
View "public.pg_store_plans"
Column | Type | Collation | Nullable | Default
-------------------------+--------------------------+-----------+----------+---------
userid | oid | | |
dbid | oid | | |
queryid | bigint | | |
planid | bigint | | |
queryid_stat_statements | bigint | | |
plan | text | | |
calls | bigint | | |
total_time | double precision | | |
min_time | double precision | | |
max_time | double precision | | |
mean_time | double precision | | |
stddev_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
first_call | timestamp with time zone | | |
last_call | timestamp with time zone | | |這個視圖有部分信息和 pg_stat_statements 重疊了。
user_id : 執行語句的用戶 id。
dbid: 執行語句的數據庫的 id。
queryid ：內部哈希碼，根據語句的查詢字符串計算。
planid：執行計劃的 planid，類似 oracle 中的 plan_hash_value。
queryid_stat_statements ：語句的 queryid，類似 oracle 中的 sql_id，可以和 pg_stat_statements 進行關聯查看詳細的 sql 語句。
plan : 執行計劃的文本。格式由參數pg_store_plans.plan_format指定。
calls：執行次數。
total_time：語句使用的計劃花費的總時間，以毫秒爲單位
min_time：語句使用的計劃花費的最短時間，以毫秒爲單位
max_time：語句使用的計劃花費的最長時間，以毫秒爲單位
mean_time：語句使用的計劃平均時間，以毫秒爲單位
stddev_time：語句使用的計劃花費時間的總體標準偏差，以毫秒爲單位
rows：語句使用的計劃查詢和影響的總行數。
shared_blks_hit：語句使用的計劃在共享塊緩存命中總數。
shared_blks_read：語句使用的計劃讀取的共享塊總數。
shared_blks_dirtied：語句使用的計劃讀取髒共享塊總數。
shared_blks_written：語句使用的計劃寫入的共享塊總數。
local_blks_hit：語句使用的計劃命中的本地塊緩存總數
local_blks_read：語句使用的計劃讀取的本地塊總數
local_blks_dirtied ：語句使用的計劃弄髒的本地塊總數
local_blks_written：語句使用的計劃寫入的本地塊總數
temp_blks_read：語句使用計劃讀取的臨時塊總數
temp_blks_written：語句使用計劃寫入的臨時塊總數
blk_read_time：語句使用計劃讀取塊所花費的總時間，以毫秒爲單位（需要啓用 track_io_timing，否則爲 0）
blk_write_time: 語句使用計劃寫入塊所花費的總時間，以毫秒爲單位 (需要啓用 track_io_timing，否則爲 0)
first_call：語句使用計劃首次調用的時間
last_call: 語句使用計劃最後一次調用的時間
例如：我們想查詢某 SQL 語句和它的執行計劃，只需要將 pg_stat_statements 和 pg_store_plans 進行關聯就可以了。
test=# select a.query,b.plan,b.calls from pg_stat_statements a, pg_store_plans b where a.queryid=b.queryid_stat_statements and b.queryid_stat_statements=-235294569018386844 and a.dbid=13591;
-[ RECORD 1 ]------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
query | SELECT e.extname AS "Name", e.extversion AS "Version", n.nspname AS "Schema", c.description AS "Description"
| FROM pg_catalog.pg_extension e LEFT JOIN pg_catalog.pg_namespace n ON n.oid = e.extnamespace LEFT JOIN pg_catalog.pg_description c ON | c.objoid = e.oid AND c.classoid = $1::pg_catalog.regclass ORDER BY 1
plan | Sort (cost=10.46..10.47 rows=1 width=158)
| Sort Key: e.extname
| -> Nested Loop Left (cost=0.28..10.45 rows=1 width=158)
| Join Filter: (n.oid = e.extnamespace)
| -> Nested Loop Left (cost=0.28..9.32 rows=1 width=98)
| -> Seq Scan on pg_extension e (cost=0.00..1.01 rows=1 width=76)
| -> Index Scan using pg_description_o_c_o_index on pg_description c (cost=0.28..8.30 rows=1 width=30)
| Index Cond: ((objoid = e.oid) AND (classoid = '3079'::oid))
| -> Seq Scan on pg_namespace n (cost=0.00..1.06 rows=6 width=68)
calls | 1提供的函數
除了提供視圖方便查詢之外，還提供了一系列的函數。
pg_store_plans_reset：丟棄到目前爲止由pg_store_plans所有收集的統計信息。默認情況下，只有超級用戶才能執行此功能。
pg_store_plans：pg_store_plans 視圖的定義是基於 pg_store_plans 函數的。
pg_store_hash_query：該函數計算查詢文本的哈希值。計算 pg_store_plans 的查詢 id 使用相同的算法，該函數可以與 pg_store_plans 結合使用。
test=# select public.pg_store_plans_hash_query('select 1');
-[ RECORD 1 ]-------------+-----------
pg_store_plans_hash_query | 2042559815pg_store_plans_textplan ：當你把參數 pg_store_plans.plan_formats 設置爲'raw'的時候會顯示短格式 json，短格式 json 是 pg_store_plans 中的計劃的內部格式，這個函數可以將短格式的 json 轉換成正常的 text 格式。
pg_store_plans_jsonplan：類似同上，只是將短格式的 json 轉換成正常的 json 格式表示。
pg_store_plans_xmlplan ：類似同上，將短格式的 json 轉換成 xml 格式表示。
pg_store_plans_yamlplan ：類似同上，將短格式的 json 轉換 yaml 格式表示。
控制參數
pg_store_plans.max: pg_store_plans 視圖中的最大行數，默認值爲 1000，不頻繁使用的執行計劃的信息將被丟棄。該參數只能在服務器啓動時設置。
pg_store_plans.track: 控制對哪些語句進行計數。指定top跟蹤頂級語句 (由客戶端直接發出的語句),all還跟蹤嵌套語句 (例如函數內調用的語句)，指定none以禁用語句統計信息收集。默認值爲top。只有超級用戶才能更改此設置。
pg_store_plans.plan_format: 控制 pg_store_plans 中存儲執行計劃的格式。text爲默認值並以普通文本表示形式顯示，json、xml 和yaml以相應格式顯示。raw提供的是內部格式。
pg_store_plans.min_duration: 語句最小執行時間，以毫秒爲單位。如果設置爲 0(默認值)，則記錄所有執行計劃。只有超級用戶才能更改此設置。
pg_store_plans.log_analyze: 保存的執行計劃中包含 EXPLAIN ANALYZE 輸出，而不僅僅是 EXPLAIN 輸出。這個參數默認值爲 off。
pg_store_plans.log_buffers: 保存的執行計劃中包含 EXPLAIN (ANALYZE, BUFFERS) 輸出，而不僅僅是 EXPLAIN 輸出，該參數默認值爲 off。
pg_store_plans.log_timing: 設置爲 false 會禁用記錄實際時間。在某些系統上，重複讀取系統時鐘的開銷會顯著降低查詢的速度，因此當只需要實際行數而不是每個執行節點的精確執行時間時，將此參數設置爲 FALSE 可能很有用。當pg_store_plans.log_analyze設置爲 TRUE 時，始終測量整個語句的運行時間。它默認爲 TRUE。
pg_store_plans.log_triggers: 在記錄的執行計劃中包含觸發器執行統計信息。除非打開pg_store_plans.log_analyze，否則此參數無效。
pg_store_plans.verbose: 保存的執行計劃中包含 EXPLAIN VERBOSE 輸出，而不僅僅是 EXPLAIN 輸出。該參數默認值爲 off。
pg_store_plans.save: 指定是否在服務器關閉時保存計劃統計信息，默認值爲 on。如果設置爲 off，則統計信息不會在關閉時保存，也不會在服務器啓動時重新加載。此參數只能在postgresql.conf文件或服務器命令行中設置。
使用這個插件需要與
pg_store_plans.max成比例的額外共享內存 。請注意，無論何時加載模塊，都會消耗此內存，即使pg_store_plans.track設置爲none。
測試試用
先看下我們當前的設置情況。
test=# SELECT name, setting FROM pg_settings WHERE name LIKE 'pg_store_plans.%';
name | setting
-----------------------------+---------
pg_store_plans.log_analyze | off
pg_store_plans.log_buffers | off
pg_store_plans.log_timing | on
pg_store_plans.log_triggers | off
pg_store_plans.log_verbose | off
pg_store_plans.max | 1000
pg_store_plans.min_duration | 0
pg_store_plans.plan_format | text
pg_store_plans.save | on
pg_store_plans.track | top運行 pgbench 來生點數據和造點執行計劃。在測試之前我們先清空 pg_store_plans。
test=# SELECT pg_store_plans_reset();
pg_store_plans_reset
----------------------
(1 row)
pgbench -U root -p 18804 -i -s 10 --unlogged-tables test -q
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
1000000 of 1000000 tuples (100%) done (elapsed 0.69 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.查看 pg_stort_plans 視圖。
test=# SELECT queryid, plan,calls FROM pg_store_plans where dbid=6673065;
queryid | plan | calls
------------+------------------------------------------------------------------------+-------
329411391 | Function Scan on pg_store_plans (cost=0.00..10.00 rows=1000 width=24) | 1
3137955951 | Result (cost=0.00..0.01 rows=1 width=4) | 1
1446640908 | Insert on pgbench_branches (cost=0.00..0.01 rows=1 width=40) +| 10
| -> Result (cost=0.00..0.01 rows=1 width=40) |
3770510668 | Function Scan on pg_store_plans (cost=0.00..12.50 rows=5 width=40) +| 1
| Filter: (dbid = '6673065'::oid) |
3126036813 | Insert on pgbench_tellers (cost=0.00..0.01 rows=1 width=44) +| 100
| -> Result (cost=0.00..0.01 rows=1 width=44) |
791401476 | Function Scan on pg_store_plans (cost=0.00..12.50 rows=5 width=24) +| 1
| Filter: (dbid = '6673065'::oid) |這裏可以看到記錄了兩個 insert 語句的執行計劃，分別是插入 pgbench_branches 和 pgbench_tellers。但是像 pgbench_accounts 這樣的表就沒有記錄，這是因爲它是 copy 語句而不是 dml。
手動執行一條查詢語句，然後查看該語句的執行計劃，已經記錄進去了。
SELECT abalance FROM pgbench_accounts WHERE aid=123;
test=# SELECT queryid, plan,calls FROM pg_store_plans where dbid=6673065;
queryid | plan | calls
------------+----------------------------------------------------------------------------------------------+-------
3137955951 | Result (cost=0.00..0.01 rows=1 width=4) | 1
1446640908 | Insert on pgbench_branches (cost=0.00..0.01 rows=1 width=40) +| 10
| -> Result (cost=0.00..0.01 rows=1 width=40) |
3126036813 | Insert on pgbench_tellers (cost=0.00..0.01 rows=1 width=44) +| 100
| -> Result (cost=0.00..0.01 rows=1 width=44) |
2032872830 | Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost=0.42..8.44 rows=1 width=4)+| 1
| Index Cond: (aid = 123)後記
pg_store_plans可以幫助我們實現像 Oracle 一樣的 v$sql_plan 的功能。但是它同樣也會消耗一定的共享內存，特別是當你 max 設置的比較大的時候。
本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/6jMLrVdLQTNqCywIRyLZAA