Posted inDatabase

pg_stat_statements PostgreSQL：リソースを消費するSQLクエリの特定と最適化

Database tutorial - IT technology blog
Database tutorial - IT technology blog

データベースが「重い」のに原因がわからないとき

PostgreSQLのパフォーマンス低下はよくある話です。CPUが100%に張り付き、レスポンスタイムが急増しているのに、ログを見ても何が原因なのかさっぱりわかりません。EXPLAIN ANALYZEで個別クエリを分析したことがある方なら、その威力はわかっているはずです。ただ問題は、どのクエリを先に分析すべきかを事前に把握していなければならないという点です。

pg_stat_statementsはまさにこの問題を解決するために生まれました。このエクステンションはデータベースの稼働中、すべてのSQLの実行統計を追跡し続けます。サンプリングでも、ログを有効にして手動でgrepするようなものでもありません。すべてのクエリはテンプレート単位で集計され、「最も時間を消費しているクエリはどれか」「最もブロックを読み込んでいるのはどれか」「1日に何千回も実行されていて平均実行時間が気になるのはどれか」といった問いに正確に答えられます。

EXPLAIN ANALYZEは特定のクエリをその場で分析するものですが、pg_stat_statementsはバックグラウンドで静かに動き続け、データを蓄積していきます。問題が発生したときには、すでに調査に必要なデータが手元にあります。問題を再現する必要はありません。

エクステンションのインストール

このエクステンションはPostgreSQLに同梱されており、追加パッケージのインストールは不要です。セットアップは以下の3ステップを順番に実行します。

ステップ1：shared_preload_librariesに追記する

postgresql.confの場所を確認します：

sudo -u postgres psql -c "SHOW config_file;"
# Ubuntu/Debianでは通常: /etc/postgresql/15/main/postgresql.conf
# CentOS/RHELでは通常: /var/lib/pgsql/15/data/postgresql.conf

ファイルを開き、以下の行を追加または修正します：

# ライブラリが何も設定されていない場合：
shared_preload_libraries = 'pg_stat_statements'

# 他のライブラリがすでにある場合はカンマ区切りで追記：
shared_preload_libraries = 'pg_stat_statements,other_library_name'

この手順は必須です。エクステンションはPostgreSQLの起動時にロードされる必要があるため、CREATE EXTENSIONだけではなく、必ずこの設定が必要です。

ステップ2：PostgreSQLを再起動する

sudo systemctl restart postgresql

ステップ3：監視対象のデータベースにエクステンションを作成する

-- 監視対象のデータベースに接続する
\c target_database

CREATE EXTENSION pg_stat_statements;

-- 動作確認
SELECT * FROM pg_stat_statements LIMIT 5;

データが返ってきた（数行でも）場合、エクステンションは正常に動作しています。「could not open file」というエラーが表示された場合は、ステップ2の再起動を忘れている可能性が高いです。

詳細設定

インストールしたらすぐに使えます。ただし、postgresql.confで用途に応じていくつかのパラメータを調整しておくとより便利です：

# 保存するクエリテンプレートの最大数（デフォルト：5000）
pg_stat_statements.max = 10000

# TOP: クライアントから直接呼ばれたクエリのみ（軽量で、ほとんどのケースで十分）
# ALL: ストアドプロシージャ/ファンクション内のネストされたクエリも含む
pg_stat_statements.track = top

# VACUUM、ANALYZE、CREATE INDEXなどのユーティリティコマンドも含める
pg_stat_statements.track_utility = on

# プランニング時間を別途追跡する（PostgreSQL 13以降）
pg_stat_statements.track_planning = on

変更後は再起動不要でリロードできます：

sudo -u postgres psql -c "SELECT pg_reload_conf();"

通常のproduction環境ではtrack = topにしています。軽量で、ほとんどの問題を見つけるには十分です。特定のストアドプロシージャ内のパフォーマンスをデバッグする必要がある場合にだけallに切り替え、終わったら元に戻しています。

pg_stat_statements.maxについて：上限に達すると、出現頻度の低いクエリが統計から除外されます。実際のデータは失われず、統計レコードが削除されるだけです。クエリの種類が多いシステム（数百のAPIエンドポイントを持つマイクロサービスなど）では、10000〜20000に増やすことをお勧めします。

確認とモニタリング

pg_stat_statementsビューには多数の列があります。以下は調査目的別の実践的なクエリです。

合計実行時間が最も長いTop 10クエリ

SELECT 
    round(total_exec_time::numeric, 2) AS total_ms,
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round((100 * total_exec_time / sum(total_exec_time) OVER ())::numeric, 2) AS pct_cpu,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

このクエリは「データベースが遅い」という報告を受けたときに真っ先に実行するものです。pct_cpu列を見ればすぐに犯人がわかります。実際のproductionシステムでは、TOP 3のクエリが全体の80%以上を占めることは珍しくありません。

平均実行時間が最も遅いTop 10クエリ

SELECT
    calls,
    round(mean_exec_time::numeric, 2) AS mean_ms,
    round(total_exec_time::numeric, 2) AS total_ms,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE calls > 10
ORDER BY mean_exec_time DESC
LIMIT 10;

calls > 10でフィルタリングすることで、1〜2回しか実行されないadminやメンテナンス用クエリによる誤検知を防ぎます。1回実行して30秒かかるクエリより、10,000回実行して平均50msかかるクエリの方がよほど危険です。

最もブロック読み込みが多いクエリ — I/O問題の特定

SELECT
    calls,
    shared_blks_hit,
    shared_blks_read,
    round(
        shared_blks_hit * 100.0 / 
        nullif(shared_blks_hit + shared_blks_read, 0)
    , 2) AS cache_hit_pct,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE shared_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

cache_hit_pctが90%を下回っている場合は、クエリがshared bufferではなくディスクから多くのデータを読み込んでいるサインです。よくある原因は、インデックス不足によるテーブル全体のsequential scan、またはshared_buffersがworking setに対して小さすぎる場合です。

プランニング時間が長いクエリ（PostgreSQL 13以降）

SELECT
    calls,
    round(mean_plan_time::numeric, 2) AS plan_ms,
    round(mean_exec_time::numeric, 2) AS exec_ms,
    left(query, 100) AS query_snippet
FROM pg_stat_statements
WHERE mean_plan_time > 1
ORDER BY mean_plan_time DESC
LIMIT 10;

プランニング時間が長い（数ミリ秒以上）場合は、複雑なJOINが多いクエリか、テーブル統計情報が古くてplannerが多くの計算を必要としているケースがほとんどです。ANALYZE テーブル名;を実行して統計情報を更新してみてください。このコマンドだけでプランニング時間が数十msから1ms以下に下がるケースも多くあります。

統計のリセット（再測定が必要な場合）

-- 全体をリセット（修正をデプロイした後や設定変更後に使用）
SELECT pg_stat_statements_reset();

-- 特定のクエリのみリセット（PostgreSQL 14以降）
SELECT pg_stat_statements_reset(0, 0, queryid);
-- queryidはpg_stat_statementsビューのqueryid列から取得

現在実行中のクエリをリアルタイムで確認する

今この瞬間に何が実行されているかを確認したい場合は、pg_stat_activityと組み合わせます：

SELECT 
    pid,
    now() - query_start AS duration,
    state,
    left(query, 120) AS query_snippet
FROM pg_stat_activity
WHERE query_start IS NOT NULL
  AND state != 'idle'
ORDER BY duration DESC;

クエリが長時間止まっていて確実に停止が必要な場合：

SELECT pg_cancel_backend(pid);    -- キャンセルリクエストを送信（graceful、こちらを優先）
SELECT pg_terminate_backend(pid); -- キャンセルが効かない場合はforce terminate

パフォーマンス調査の実践的なワークフロー

「データベースが遅い」という報告を受けたときに私が使う手順です：

  1. 合計実行時間クエリを実行 → リソースを最も消費しているTOP 3クエリを特定
  2. クエリの全文を取得 → それぞれにEXPLAIN (ANALYZE, BUFFERS)を実行
  3. キャッシュヒット率を確認 → 低い場合はインデックスを見直すかshared_buffersを増やす
  4. pg_stat_activityを確認してブロックされているクエリがないかチェック
  5. 統計をリセット → 修正をデプロイ → 24時間後に再測定して改善を確認

pg_stat_statementsの結果をオフラインで処理する場合（psqlからCSVエクスポートしてスクリプトで分析するケース）、toolcraft.app/ja/tools/data/csv-to-jsonのコンバーターをよく使っています。Pythonに取り込む前にJSONに変換できて、ブラウザ上で完結するのでクエリログデータが外部に送信されるリスクがありません。

運用時の注意点

  • オーバーヘッドは低い：約1〜5%と小さく、productionで常時有効にしておける
  • クエリは自動的に正規化される：リテラル値は$1、$2…に置き換えられ、同じテンプレートのクエリが1つのレコードにまとめられます。これは意図的な設計で、1000通りのIDで1000回実行したSELECT * FROM users WHERE id = $1も統計上は1行になります
  • 再起動すると統計はリセットされる：PostgreSQLを再起動すると統計データは消えます。履歴を保持したい場合は、定期メンテナンスの前にファイルにエクスポートしておきましょう
  • アクセス権限：スーパーユーザーはすべてのユーザーのクエリを参照できます。一般ユーザーは自分自身のクエリのみ参照可能（PostgreSQL 14以降）

Share:
Tags: