PostgreSQLのpg_stat_statementsで遅いクエリを特定する方法｜エクステンション設定から実行統計の分析まで

この記事のポイント

・pg_stat_statementsはPostgreSQLの実行SQLを正規化して統計集計する公式エクステンション
・postgresql.confに1行追加して再起動するだけで有効化でき、追加パッケージは不要
・累積実行時間・平均実行時間・バッファキャッシュヒット率でクエリをランキングできる
・pg_stat_statements_reset()で統計をリセットし、任意のタイミングから計測をやり直せる

# 設定ファイルの場所を確認する
$ sudo -u postgres psql -c "SHOW config_file;"
               config_file
-----------------------------------------
 /var/lib/pgsql/16/data/postgresql.conf
(1 row)

# /var/lib/pgsql/16/data/postgresql.conf の編集箇所
# 既存値がある場合はカンマ区切りで追記する（例: 'pg_stat_statements,auto_explain'）
shared_preload_libraries = 'pg_stat_statements'

# 収集する最大クエリ数（デフォルト: 5000）
pg_stat_statements.max = 10000

# 追跡対象: top（直接実行のみ）/ all（関数・ストアドプロシージャ内部も含む）
# 注意: all に設定すると関数・プロシージャ内のSQLも追跡するため、サービス規模によっては
#       オーバーヘッドが増加する。まずは top で運用し、必要に応じて変更するのが安全だ
pg_stat_statements.track = top

# Rocky Linux 9 / RHEL系（サービス名に版数が入る）
$ sudo systemctl restart postgresql-16

# Ubuntu 24.04 LTS
$ sudo systemctl restart postgresql

# postgresユーザーで対象データベースに接続してエクステンションを作成する
$ sudo -u postgres psql -d myapp
psql (16.4)
Type "help" for help.

myapp=# CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION
myapp=# \q

$ sudo -u postgres psql -d myapp -c "\dx pg_stat_statements"
                              List of installed extensions
        Name        | Version |   Schema   |              Description
--------------------+---------+------------+----------------------------------------
 pg_stat_statements |  1.10   | public     | track planning and execution statistics
(1 row)

SELECT
    left(query, 80)                         AS query_abbr,
    calls,
    round(total_exec_time::numeric, 2)      AS total_ms,
    round(mean_exec_time::numeric, 2)       AS mean_ms,
    round(max_exec_time::numeric, 2)        AS max_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

              query_abbr               | calls |  total_ms  | mean_ms  |  max_ms
---------------------------------------+-------+------------+----------+----------
 SELECT o.*, u.name FROM orders o JOIN | 8423  | 124538.47  |   14.79  |   312.45
 UPDATE sessions SET last_seen_at=$1 W | 52110 |  98721.33  |    1.89  |    18.22
 SELECT COUNT(*) FROM logs WHERE creat |   234 |  45892.11  |  196.11  |  2344.00
 INSERT INTO access_log (user_id, acti | 103201|  41230.55  |    0.40  |    12.34
(4 rows)

SELECT
    left(query, 80)                      AS query_abbr,
    calls,
    round(mean_exec_time::numeric, 2)    AS mean_ms,
    round(max_exec_time::numeric, 2)     AS max_ms
FROM pg_stat_statements
WHERE calls >= 100
ORDER BY mean_exec_time DESC
LIMIT 10;

SELECT
    left(query, 80)                              AS query_abbr,
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(
        100.0 * shared_blks_hit
        / NULLIF(shared_blks_hit + shared_blks_read, 0),
        1
    )                                            AS hit_rate_pct
FROM pg_stat_statements
WHERE shared_blks_hit + shared_blks_read > 0
  AND calls >= 10
ORDER BY hit_rate_pct ASC
LIMIT 10;

-- データベース全体の統計をリセットする（スーパーユーザー権限が必要）
myapp=# SELECT pg_stat_statements_reset();
 pg_stat_statements_reset
--------------------------

(1 row)

-- PostgreSQL 12以降: 第3引数にqueryidを指定して特定クエリだけリセットすることも可能
-- SELECT pg_stat_statements_reset(0, 0, 0);  -- 引数0は「すべて」を意味する

ERROR:  relation "pg_stat_statements" does not exist
LINE 1: SELECT * FROM pg_stat_statements LIMIT 5;
                      ^

-- 対処: 接続中のデータベースでエクステンションを作成する
myapp=# CREATE EXTENSION pg_stat_statements;
CREATE EXTENSION

-- 現在ロードされているライブラリを確認する
$ sudo -u postgres psql -c "SHOW shared_preload_libraries;"
 shared_preload_libraries
--------------------------
 pg_stat_statements
(1 row)

$ sudo -u postgres psql -c "SHOW pg_stat_statements.track;"
 pg_stat_statements.track
--------------------------
 top
(1 row)