資料庫是較大型的應用，對於繁忙的資料庫，需要消耗大量的記憶體、CPU、IO 和網路資源。SQL 最佳化是資料庫最佳化的手段之一，而為了達到 SQL 最佳化的最佳效果，您首先需要瞭解最消耗資源的 SQL（Top SQL），例如 IO 消耗最高的 SQL。
資料庫資源分為多個維度，包括CPU、記憶體、IO 等，為了能夠從各個維度層面尋找最消耗資料庫資源的 SQL，您可以使用 pg_stat_statements 外掛程式統計資料庫的資源開銷和分析 Top SQL。
本文將通過樣本介紹如何建立 pg_stat_statements 外掛程式、如何分析 Top SQL 以及如何重設統計資訊。
您可以開通SQL洞察和審計功能，以記錄和彙總分析SQL語句的執行情況。開啟後，該功能將自動記錄來自資料庫核心的SQL語句，以及SQL語句的執行詳情、執行帳號、IP地址等資訊，並且對執行個體效能不產生影響。
執行如下命令，在需要查詢 TOP SQL 的資料庫中，建立 pg_stat_statements 外掛程式。
CREATE EXTENSION pg_stat_statements;pg_stat_statements 輸出內容介紹
通過查詢 pg_stat_statements 視圖，您可以得到資料庫資源開銷的統計資訊。SQL 陳述式中的一些過濾條件在 pg_stat_statements 中會被替換成變數，可以減少重複顯示的問題。
pg_stat_statements 視圖包含了一些重要訊息，例如：
SQL 的調用次數、總耗時、最快執行時間、最慢執行時間、平均執行時間、執行時間的方差（反映抖動），總共掃描、返回或處理了多少行。
shared buffer 的使用方式：命中、未命中、產生髒塊、驅逐髒塊。
local buffer 的使用方式：命中、未命中、產生髒塊、驅逐髒塊。
temp buffer 的使用方式：讀了多少髒塊、驅逐髒塊。
資料區塊的讀寫時間。
下表列出了 pg_stat_statements 輸出內容中各參數的含義。
參數名稱 | 類型 | 參考 | 說明 |
userid | oid | pg_authid.oid | OID of user who executed the statement. |
dbid | oid | pg_database.oid | OID of database in which the statement was executed. |
queryid | bigint | 無 | Internal hash code, computed from the statement’s parse tree. |
query | text | 無 | Text of a representative statement. |
calls | bigint | 無 | Number of times executed. |
total_time | double precision | 無 | Total time spent in the statement, in milliseconds. |
min_time | double precision | 無 | Minimum time spent in the statement, in milliseconds. |
max_time | double precision | 無 | Maximum time spent in the statement, in milliseconds. |
mean_time | double precision | 無 | Mean time spent in the statement, in milliseconds. |
stddev_time | double precision | 無 | Population standard deviation of time spent in the statement, in milliseconds. |
rows | bigint | 無 | Total number of rows retrieved or affected by the statement. |
shared_blks_hit | bigint | 無 | Total number of shared block cache hits by the statement. |
shared_blks_read | bigint | 無 | Total number of shared blocks read by the statement. |
shared_blks_dirtied | bigint | 無 | Total number of shared blocks dirtied by the statement. |
shared_blks_written | bigint | 無 | Total number of shared blocks written by the statement. |
local_blks_hit | bigint | 無 | Total number of local block cache hits by the statement. |
local_blks_read | bigint | 無 | Total number of local blocks read by the statement. |
local_blks_dirtied | bigint | 無 | Total number of local blocks dirtied by the statement. |
local_blks_written | bigint | 無 | Total number of local blocks written by the statement. |
temp_blks_read | bigint | 無 | Total number of temp blocks read by the statement. |
temp_blks_written | bigint | 無 | Total number of temp blocks written by the statement. |
blk_read_time | double precision | 無 | Total time the statement spent reading blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). |
blk_write_time | double precision | 無 | Total time the statement spent writing blocks, in milliseconds (if track_io_timing is enabled, otherwise zero). |
分析 TOP SQL
最耗 IO SQL
執行如下命令，查詢單次調用最耗 IO SQL TOP 5。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time)/calls DESC LIMIT 5;執行如下命令，查詢總最耗 I/IO 的SQL TOP 5。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (blk_read_time+blk_write_time) DESC LIMIT 5;
最耗時 SQL
執行如下命令，查詢單次調用最耗時 SQL TOP 5。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;執行如下命令，查詢總最耗時 SQL TOP 5。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
回應時間抖動最嚴重 SQL
執行如下命令，查詢回應時間抖動最嚴重 SQL。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY stddev_time DESC LIMIT 5;最耗共用記憶體 SQL
執行如下命令，查詢最耗共用記憶體 SQL。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY (shared_blks_hit+shared_blks_dirtied) DESC LIMIT 5;最耗臨時空間 SQL
執行如下命令，查詢最耗臨時空間 SQL。
SELECT userid::regrole, dbid, query FROM pg_stat_statements ORDER BY temp_blks_written DESC LIMIT 5;
重設統計資訊
pg_stat_statements是累積統計，您可以通過執行如下命令，來定期清理歷史統計資訊。
SELECT pg_stat_statements_reset();