原文地址：https://www.citusdata.com/blog/2019/02/08/the-most-useful-postgres-extension-pg-stat-statements/

Postgresql的Extensions能夠延伸，更改和推進Postgres的行為。怎麼樣？通過hooking Postgres 到底層的 API hooks。
開源的Citus資料庫水平擴展了Postgres，它本身就是一個PostgreSQL擴展，它允許Citus保持最新的Postgres版本，而不會像其他Postgres分支那樣落後。
儘管我以前已經寫過各種類型的擴展，但是今天我想更深入地瞭解最有用的Postgres擴展:pg_stat_statements。

如你所見，我剛從FOSDEM回來。 FOSDEM是在布魯塞爾舉行的年度免費開源軟體會議，在活動中，我在PostgreSQL開發室中發表了有關Postgres擴展的演講。
到今天結束時，Postgres開發室中進行的一半以上的討論都提到了pg_stat_statements：
Most frequently dispensed #PostgreSQL tip-of-the-day here in the Postgres devroom at #FOSDEM? Use pg_stat_statements! @Xof’s talk on Breaking PostgreSQL at Scale is the 4th talk today to drive this point home HT @craig @net_snow @magnushagander pic.twitter.com/Tcwkhy8W8h
— Claire Giordano (@clairegiordano) February 3, 2019

如果您使用Postgres，但尚未使用pg_stat_statements，則必須將其添加到工具箱中。即使您很熟悉，也可能值得回顧一下。

pg_stat_statements入門

pg_stat_statements是所謂的contrib擴展名，可以在PostgreSQL發行版的contrib目錄中找到。
這意味著它已經隨Postgres一起提供了，您不必從源代碼構建它或安裝軟體包。如果尚未啟用資料庫，則可能必須啟用它。這很簡單：

CREATE EXTENSION pg_stat_statements;

如果您在主要的雲提供商上運行，則很有可能他們已經為您安裝並啟用了它。
一旦安裝了pg_stat_statements，它就會開始悄悄地在後臺運行。 Pg_stat_statements記錄針對您的資料庫運行的查詢，從中刪除許多變數，然後保存有關該查詢的數據，例如花費了多長時間以及基礎讀/寫發生了什麼。
註意：它不會保存每個查詢，而是對其進行參數化，然後保存彙總結果

讓我們來看幾個示例。假設我們執行以下查詢：

SELECT order_details.qty,
       order_details.item_id,
       order_details.item_price
FROM order_details,
     customers
WHERE customers.id = order_details.customer_id
  AND customers.email = '[email protected]'

它將查詢轉換為：

SELECT order_details.qty,
       order_details.item_id,
       order_details.item_price
FROM order_details,
     customers
WHERE customers.id = order_details.customer_id
  AND customers.email = '?'

如果這是我在應用程式中經常執行的查詢，以獲取諸如零售訂單歷史記錄之類的訂單詳細信息，那麼它不會簡化我為每個用戶運行該訂單的頻率數據，而是通過彙總視圖來簡化存儲。

觀察pg_stat_statements數據

SELECT * 
FROM pg_stat_statements;

userid              | 16384
dbid                | 16388
query               | select * from users where email = ?;
calls               | 2
total_time          | 0.000268
rows                | 2
shared_blks_hit     | 16
shared_blks_read    | 0
shared_blks_dirtied | 0
shared_blks_written | 0
local_blks_hit      | 0
local_blks_read     | 0
local_blks_dirtied  | 0
local_blks_written  | 0

彙總pg_stat_statements歷史記錄

現在，這裡有大量有價值的信息，作為高級用戶，有時它們都可以證明是有價值的。
但是，即使沒有開始瞭解資料庫的內部結構，您仍然可以通過以某些方式查詢pg_stat_statements來獲得一些真正強大的見解。
通過查看total_time和每個查詢被調用一次的次數，我們可以非常快速地瞭解哪些查詢經常運行以及它們平均消耗了多少：

SELECT 
  (total_time / 1000 / 60) as total, 
  (total_time/calls) as avg, 
  query 
FROM pg_stat_statements 
ORDER BY 1 DESC 
LIMIT 100;

您可以採用多種不同的方式來對此進行過濾和排序，您可能只希望關註運行1000次以上的查詢。或平均超過100毫秒的查詢。
上面的查詢向我們顯示了資料庫消耗的總時間（以分鐘為單位）以及平均時間（以毫秒為單位）。
通過上面的查詢，我會得到如下所示的內容：

   total  |   avg  |        query
  --------+--------+-------------------------
   295.76 |  10.13 | SELECT id FROM users...
   219.13 |  80.24 | SELECT * FROM ...
  (2 rows)

根據經驗，我知道在快速獲取記錄時，PostgreSQL應該能夠在1ms內返回。
鑒於此，我可以開始優化工作。在上面的內容中，我看到將第一個查詢降低到1ms會有所改善，但是優化第二個查詢將使我的系統整體性能得到更大的提升。

特別說明：如果要構建多租戶應用，則可能不希望pg_stat_statements參數化tenant_id。為瞭解決這個問題，我們構建了citus_stat_statements來為每個租戶提供見解。

如果您從未（甚至在過去的一個月中）都沒有查看過pg_stat_statements中的數據，那麼今天對您來說是個好日子。
它可以告訴您哪些地方可以優化？我們希望聽到您發現@citusdata的內容。

享受您正在閱讀的內容嗎？
如果您有興趣閱讀我們團隊的更多帖子，請註冊我們的每月時事通訊，並將最新內容直接發送到您的收件箱。

譯者註：

1，原來類似功能就叫做“Extensions”，打開後就是記錄歷史SQL執行代價的彙總信息。
2，Postgresql中的pg_stat_statements有點類似於MySQL中的general query log，或者sqlserver中的執行計劃緩存sys.dm_exec_query_stats，他是基於移除參數的sql做模板，直接彙總了一個SQL的調用以及消耗情況
3，不清楚pg_stat_statements可以設置篩選條件，類似於sqlserver中的擴展事件，比如可以基於庫，用戶，或者語句，或者超出一定時間的語句做篩選，目前這種記錄方式說實話參考意義有限。
4，開啟了pg_stat_statements之後，對性能有多大的影響？
5，如何清理或者自定義的方式重置pg_stat_statements中的歷史記錄？select pg_stat_statements_reset();