原文地址：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();    

標籤：PostgreSQL

上一篇：Postgresql統計資訊概述

下一篇：PostgreSQL中的死鎖和鎖等待