假如在開發產品的時候，想要知道是否有 SQL 指令查詢效率很差，我們能有什麼工具來監控呢？慢查詢（Slow Query）是影響效能的主要瓶頸之一，如果有查詢執行時間過長，可能會影響系統整體運行速度，甚至可能導致 timeout。

今天要介紹的是在 PostgreSQL 中，兩種查詢和監控 Slow Query 的方式：

• pg_stat_statements：PostgreSQL 內建工具，會自動追蹤和匯總所有查詢的執行統計數據。
• 慢查詢日誌（Slow Query Log）：這是透過設定，將所有執行時間超過指定門檻的 SQL 語句完整記錄下來，方便我們逐一審視每條 SQL 的細節。

使用 pg_stat_statements

先來看第一種方式，需要使用到 pg_stat_statements 的 extension，使用的方式如下：

1. 要先在 postgresql.conf 的 shared_preload_libraries 加上 pg_stat_statements

shared_preload_libraries = 'pg_stat_statements'

如果不確定 postgresql.conf 的路徑，可以用 SHOW config_file 找到位置。

2. 重新啟動 PostgreSQL

brew services restart postgresql

3. 啟用 extension

CREATE EXTENSION pg_stat_statements;

4. 查看 pg_stat_statements

SELECT
    (total_exec_time / 1000 / calls) AS avg_ms,
    calls,
    query
FROM pg_stat_statements
ORDER BY avg_ms DESC
LIMIT 5;

• total_exec_time：這個指令總共查詢的時間（需要除以次數才是平均）
• calls：這個指令被呼叫的次數
• query：指令本身

所以這個 SQL 是找出平均時間花費最多的前五個 query。

啟用 Slow Query Log

第二種方式是使用 Slow Query Log 來查看執行時間過長的 SQL 指令。在 postgresql.conf 中找到 log_min_duration_statement 這個參數，可以設定查詢記錄的門檻，預設為 -1 的時候代表沒有啟用。

log_min_duration_statement: Causes the duration of each completed statement to be logged if the statement ran for at least the specified amount of time.

1. 調整 log_min_duration_statement

目前因為測試使用，先設為 0，代表所有 SQL 查詢都會被記錄。（實務上可以設為較長的時間，避免產生過多 log）

log_min_duration_statement = 0

2. 重新啟動 PostgreSQL

brew services restart postgresql

3. 查詢 PostgreSQL log

如果這時候在資料庫中查詢：

SELECT * FROM products

就可以在 log 的檔案中看到紀錄查詢的 duration 了～

AWS RDS 監控 Slow Query

如果 PostgreSQL 是部署在 AWS RDS，可以透過 Performance Insights 的 Top Query 來看 loading 最重的前幾筆 SQL：

重點回顧

• pg_stat_statements 會自動統計所有執行過的查詢，提供總執行時間、呼叫次數、平均時間等等資料。
• 啟用 Slow Query Log 可以幫助偵測影響系統效能的 SQL，設定 log_min_duration_statement 可以根據需求調整記錄的門檻
• 在 AWS RDS 可以使用 Top SQL 監控對資料庫 loading 影響最大的前 25 個查詢，幫助找需要優化的 SQL。

參考資料

https://www.postgresql.org/docs/current/runtime-config-logging.html
https://www.postgresql.org/docs/current/pgstatstatements.html