PostgreSQLで「このSQLが遅い」と相談されたとき、EXPLAIN ANALYZEの出力を上から順に全部読むと時間を取られます。ネストした行とcost、rows、actual time、loopsの数字が混ざり、どこから手を付けるか迷うところ。見る順番を先に決めておけば、原因の多くは数分で絞り込めます。
この記事は要約版です。省略した詳しい解説や手順は、ブログの完全版にまとめています: PostgreSQL EXPLAIN ANALYZEの見方
実行コマンドは固定しておく
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS)
SELECT ...;
BUFFERSを付けないとI/O量が分からず、原因の半分を見落とします。SETTINGSは既定値から変更されたプランナ関連パラメータを出力するので、「開発環境では速いのに本番では遅い」の追跡に使えます。PostgreSQL 18以降はANALYZE指定時にBUFFERSが既定で有効になるなど、オプションの既定値はバージョンで変わります。手元のバージョンのドキュメントで確認してください。
確認する順番は4点
インデントの深い行から上へたどりながら、次の4点を順に当てていきます。
1. 推定rowsと実測actual rowsのズレ
判断基準は、推定と実測の比が10倍以上であること。方向はどちら向きでもかまいません。推定1行に対して実測12,500行のようなズレが出ると、プランナは誤った選択率で上位ノードの結合方式を決めてしまいます。遅いSQLで最も多いパターンです。
Rows Removed by Filter が大きい場合は、スキャンした行の大半を捨てています。条件列へのインデックス追加や部分インデックスが効くケースです。
2. loopsの膨らみ
loops=1000 のように繰り返されるノードはNested Loopの内側です。表示されるactual timeは1回あたりの平均なので、実コストは「actual timeの上限値 × loops」で概算します。1回0.3msでもloops=50,000なら15秒かかります。
Nested Loop自体が悪いわけではなく、外側の推定行数が外れたことが原因です。対処はANALYZEや拡張統計で推定を正すのが先で、結合方式の強制は診断用に限ります。
3. Sort・Hashのディスク溢れ
Sort Method が external merge Disk: 98304kB と出たら、work_memに収まらず一時ファイルへ書き出しています。quicksort Memory: や top-N heapsort Memory: ならメモリ内で完結。Hashノードは Batches: 1 がメモリ内で、2以上はバッチ分割です。
work_memをセッション単位で上げて再測定するか、ソート対象の行数そのものを減らします。ORDER BY created_at DESC LIMIT 50 でSortが出ているなら、順序の一致するインデックスを作ってSortノードを消すのがいちばん効果の大きい対処です。
4. BUFFERSのshared read
shared hit は共有バッファで見つかったブロック数、shared read はストレージから読んだブロック数です。1ブロックは既定で8KBなので、read=610000 なら約4.7GBを読んだことになります。shared readが大きくactual timeも比例して大きければI/O律速で、読むブロック数を減らす方向で対処します。Buffersが小さいのにactual timeが大きければCPU律速で、関数呼び出しや型変換、正規表現、JSONBの展開などを疑います。
時間は累計表示である点に注意
各ノードのactual timeは、自分と子ノードの合計で表示されます。
Hash Join (actual time=120.500..980.200 rows=50000 loops=1)
-> Seq Scan on orders (actual time=0.015..300.100 rows=500000 loops=1)
-> Hash (actual time=119.800..119.810 rows=1000 loops=1)
-> Seq Scan on customers (actual time=0.010..60.200 rows=1000 loops=1)
Hash Joinの980msには子の300msと120msが含まれます。ノード自身の処理時間は差分で、この例なら980 − 300 − 120 = 560ms程度。親と子の差が大きいノードを原因と見ます。loops>1のノードを親と比べるときは、actual time × loopsに換算してください。
統計情報の鮮度を確認するSQL
推定行数のズレを見つけたら、まず最終ANALYZE時刻を確認します。
SELECT relname, last_analyze, last_autoanalyze, n_live_tup, n_dead_tup, n_mod_since_analyze
FROM pg_stat_user_tables
WHERE relname = 'orders';
n_mod_since_analyze が n_live_tup に対して無視できない割合なら、ANALYZE VERBOSE orders; で改善するか試します。列の分布が偏る場合は列単位でSTATISTICSを上げ、複数列が相関する場合は拡張統計を作成します。
診断用のスイッチ
結合方式の選択が妥当か裏付けを取りたいときは、セッション単位で切り替えて比較します。
-- 原因調査・緊急回避の用途に限る。恒久設定にはしない
SET enable_nestloop = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_nestloop;
これで速くなれば、Hash JoinやMerge Joinのほうが適切なのに選ばれていないと判断できます。恒久対応は統計情報とインデックスで行ってください。同じ発想で enable_seqscan = off を使えば、インデックスがあるのにSeq Scanが選ばれる理由を切り分けられます。
なお、EXPLAIN ANALYZEはクエリを実際に実行します。本番で流す前に SET statement_timeout = '30s'; を設定しておくと暴走を防げます。更新系はBEGINとROLLBACKで囲んで測定します。
詳しくは
元記事では、推定行数がズレる3つの原因別の対処、型の不一致や左辺の関数などSeq Scanが選ばれる具体的な条件、Bitmap Heap Scanのlossy、track_io_timingやJITの読み方まで扱っています。あわせてpg_stat_statementsでの定点観測、log_min_duration_statementとauto_explainの設定値、テーブル単位のautovacuum閾値調整といった再発防止の運用も載せています。実行計画が毎回変わる理由やprepared statementのプラン切り替えも含めて、PostgreSQL EXPLAIN ANALYZEの見方にまとめました。