PostgreSQL pg_stat_statements

由該模塊收集的統(tǒng)計信息可以通過一個名為 pg_stat_statements的視圖使用。這個視圖為每 一個可區(qū)分的數(shù)據(jù)庫 ID、用戶 ID 和查詢 ID（最多到該模塊可以追 蹤的可區(qū)分語句的數(shù)量）的組合都包含一行。該視圖的列如 表 F.21中所示。

由于安全性原因，只有超級用戶和pg_read_all_stats 角色的成員被允許看到其他用戶執(zhí)行的查詢 的 SQL 文本或者queryid。 不過，如果該視圖被安裝在其他用戶的數(shù)據(jù)庫中，那么他們就能夠看見統(tǒng) 計信息。

只要可規(guī)劃的查詢（即SELECT、INSERT、UPDATE以及DELETE）根據(jù)一種內(nèi)部哈希計算具有相同的查詢結(jié)構(gòu)，它們就會被組合到一個單一的pg_stat_statements項。通常，對于這里的目的，如果兩個查詢除了查詢中的文本常量值之外在語義上等效，它們將會被認(rèn)為是相同的。不過，功能性命令（即所有其他命令）會嚴(yán)格地以它們的文本查詢字符串為基礎(chǔ)進(jìn)行比較。

當(dāng)為了把一個查詢與其他查詢匹配，常數(shù)值會被忽略， 在pg_stat_statements顯示中它會被一個參數(shù)符號， 比如$1所替換。查詢文本的剩余部分就是具有與該pg_stat_statements項相關(guān)的特定queryid哈希值的第一個查詢的文本。

在某些情況中，具有明顯不同文本的查詢可能會被融合到一個單一的pg_stat_statements項。通常這只會發(fā)生在語義等價的查詢身上，但是也有很小的機(jī)會因為哈希碰撞的原因?qū)е聼o關(guān)的查詢被融合到一個項中（不過，對于屬于不同用戶或數(shù)據(jù)庫的查詢來說不會發(fā)生這種情況）。

由于queryid哈希值是根據(jù)查詢被解析和分析后的表達(dá)計算的，對立的情況也可能存在：如果具有相同文本的查詢由于參數(shù)（如不同的search_path設(shè)置）的原因而具有不同的含義，它們就可能作為不同的項存在。

pg_stat_statements的使用者可能希望使用 queryid（也許會與dbid和userid組合）作為一個項比查詢文本更穩(wěn)定和可靠的標(biāo)識符。但是，有一點很重要的是，對于queryid哈希值穩(wěn)定性只有有限的保障。因為該標(biāo)識符是從解析分析后的樹得來的，它的值是以這種形式出現(xiàn)的內(nèi)部對象標(biāo)識符的函數(shù)。這有一些違背直覺的含義。例如，如果有兩個查詢引用了同一個表，但是該表在兩次查詢之間被刪除并且重建，顯然這兩個查詢是完全一致的，但是 pg_stat_statements將把它們認(rèn)為是不同的。哈希處理也對機(jī)器架構(gòu)以及平臺的其他方面的差別很敏感。更進(jìn)一步，認(rèn)為PostgreSQL的不同主版本之間queryid將會保持穩(wěn)定是不安全的。

根據(jù)經(jīng)驗，只有在底層服務(wù)器版本以及目錄元數(shù)據(jù)細(xì)節(jié)保持完全相同時，queryid值才能被假定為穩(wěn)定并且可比。兩臺參與到基于物理 WAL 重放的復(fù)制中的服務(wù)器會對相同的查詢給出一樣的queryid值。但是，邏輯復(fù)制模式并不保證在所有相關(guān)細(xì)節(jié)上都保持完全一樣的復(fù)制，因此在邏輯復(fù)制機(jī)之間計算代價時，queryid并非是一個有用的標(biāo)識符。如果有疑問，推薦直接進(jìn)行測試。

代表性查詢文本中用于替換常量的參數(shù)符號從原始查詢文本中最高的 $n參數(shù)之后的下一個數(shù)字開始， 如果沒有則為$1。值得注意的是，在某些情況下， 可能存在影響編號的隱藏參數(shù)符號。例如，PL/pgSQL 使用隱藏參數(shù)符號將函數(shù)局部變量的值插入到查詢中，以便像 SELECT i + 1 INTO j的PL/pgSQL 語句將具有像SELECT i + $2這樣的代表性文本。

有代表性的查詢文本被保存在一個外部磁盤文件中，并且不會消耗共享內(nèi)存。 因此，即便是很長的查詢文本也能被成功的存儲下來。不過，如果累積了很多 長的查詢文本，該外部文件也會增長到很大。作為一種恢復(fù)方法，如果這樣的 情況發(fā)生，pg_stat_statements可能會選擇丟棄這些查詢文本， 于是pg_stat_statements視圖中的所有現(xiàn)有項將會顯示空的 query域，不過與每個 queryid相關(guān)聯(lián)的 統(tǒng)計信息會被保留下來。如果發(fā)生這種情況，可以考慮減小 pg_stat_statements.max來防止復(fù)發(fā)。

plans和calls并不總是匹配的， 因為計劃和執(zhí)行統(tǒng)計信息在它們各自的結(jié)束階段更新，并且僅適用于成功的操作。 例如，如果一條語句計劃成功但在執(zhí)行階段失敗，則只會更新其計劃統(tǒng)計信息。 如果因為使用了緩存計劃而跳過計劃，則只會更新其執(zhí)行統(tǒng)計信息。

該模塊要求與pg_stat_statements.max成比例的額外共享內(nèi)存。注意只要該模塊被載入就會消耗這么多的內(nèi)存，即便pg_stat_statements.track被設(shè)置為none。

這些參數(shù)必須在postgresql.conf中設(shè)置。典型的用法可能是：

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

pg_stat_statements.max = 10000
pg_stat_statements.track = all

bench=# SELECT pg_stat_statements_reset();

$ pgbench -i bench
$ pgbench -c10 -t300 bench

bench=# \x
bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2
calls           | 3000
total_exec_time | 25565.855387
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------?------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 4 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 5 ]---+--------------------------------------------------?------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000


bench=# SELECT pg_stat_statements_reset(0,0,s.queryid) FROM pg_stat_statements AS s
            WHERE s.query = 'UPDATE pgbench_branches SET bbalance = bbalance + $1 WHERE bid = $2';

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_tellers SET tbalance = tbalance + $1 WHERE tid = $2
calls           | 3000
total_exec_time | 20756.669379
rows            | 3000
hit_percent     | 100.0000000000000000
-[ RECORD 2 ]---+--------------------------------------------------?------------------
query           | copy pgbench_accounts from stdin
calls           | 1
total_exec_time | 291.865911
rows            | 100000
hit_percent     | 100.0000000000000000
-[ RECORD 3 ]---+--------------------------------------------------?------------------
query           | UPDATE pgbench_accounts SET abalance = abalance + $1 WHERE aid = $2
calls           | 3000
total_exec_time | 271.232977
rows            | 3000
hit_percent     | 98.8454011741682975
-[ RECORD 4 ]---+--------------------------------------------------?------------------
query           | alter table pgbench_accounts add primary key (aid)
calls           | 1
total_exec_time | 160.588563
rows            | 0
hit_percent     | 100.0000000000000000
-[ RECORD 5 ]---+--------------------------------------------------?------------------
query           | vacuum analyze pgbench_accounts
calls           | 1
total_exec_time | 136.448116
rows            | 0
hit_percent     | 99.9201915403032721

bench=# SELECT pg_stat_statements_reset(0,0,0);

bench=# SELECT query, calls, total_exec_time, rows, 100.0 * shared_blks_hit /
               nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
          FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;
-[ RECORD 1 ]---+--------------------------------------------------?---------------------------
query           | SELECT pg_stat_statements_reset(0,0,0)
calls           | 1
total_exec_time | 0.189497
rows            | 1
hit_percent     | 
-[ RECORD 2 ]---+--------------------------------------------------?---------------------------
query           | SELECT query, calls, total_exec_time, rows, $1 * shared_blks_hit /          +
                |                nullif(shared_blks_hit + shared_blks_read, $2) AS hit_percent+
                |           FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT $3
calls           | 0
total_exec_time | 0
rows            | 0
hit_percent     |