PostgreSQL EXPLAIN ANALYZEをマスターする:実行計画の読み方とスロークエリの改善手法

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

クイックスタート(5分)

ピークトラフィック時にAPIエンドポイントのレイテンシが45msから3.2秒へと跳ね上がる――このような問題の9割は、高負荷でボトルネックに陥ったデータベースクエリが原因です。MySQLやMongoDBなどのツールでもクエリの調査は可能ですが、PostgreSQLではプランナの決定プロセスを極めて高い解像度で可視化できます。必要なのは、EXPLAIN ANALYZEの読み方を身につけることだけです。

psqlやデータベースGUIで任意のSQL文の先頭にEXPLAIN ANALYZEを付与するだけで、Postgresがクエリを実際にどう実行したかを確認できます:

EXPLAIN ANALYZE
SELECT id, customer_id, total_amount, created_at
FROM orders
WHERE status = 'completed'
  AND created_at >= '2026-01-01';

データベースがクエリを実行し、以下のような詳細な実行ツリーを出力します:

Seq Scan on orders  (cost=0.00..1845.00 rows=1420 width=36) (actual time=0.045..14.821 rows=1530 loops=1)
  Filter: ((created_at >= '2026-01-01'::date) AND ((status)::text = 'completed'::text))
  Rows Removed by Filter: 85470
Planning Time: 0.182 ms
Execution Time: 15.012 ms

まずは最下部から確認します。Planning Time(0.182 ms)は、オプティマイザが最もコストの低い実行パスを計算するのに費やした時間を表します。Execution Time(15.012 ms)は、実際のディスクI/O、行の取得、フィルタ処理にかかった実測時間です。通常のEXPLAINは理論上のコストモデルを表示するだけですが、ANALYZEを追加することで実際にクエリを実行し、プランナの見積もり値とともに真の実行時間(実績値)を取得できます。

詳細解説

実行計画は、内側から外側へ、下から上へと読み進めます。子ノードが先に実行され、その出力行が親ノードへと渡されます。

ノードのメトリクスを分解する

各プランノードには、推定値と実際の実行結果という2つの異なるメトリクスセットが表示されます。

  • cost=0.00..1845.00:ディスクページのフェッチやCPUサイクルに基づく任意の作業単位です。0.00はスタートアップコスト(ノードが最初の1行を返すまでの時間)、1845.00は一致するすべての行を処理するのに要する推定総コストです。
  • rows=1420:pg_statisticに格納された統計情報に基づいてオプティマイザが算出した行数の推定値です。
  • width=36:1行あたりの推定データサイズ(バイト単位)です。
  • actual time=0.045..14.821:ミリ秒単位の実経過時間(ウォールクロック時間)です。最初の行が0.045 msで生成され、全1,530行の処理が14.821 msで完了したことを示します。
  • rows=1530:実際に返された行数です。推定行数(1,420)と比較してください。数値が近ければ、統計情報が健全であることを意味します。
  • loops=1:反復回数です。ネストループ結合内の内部ループが50回実行された場合、報告された時間や行数を50倍して総リソース消費量を計算する必要があります。

スキャン戦略:シーケンシャル vs インデックス

Postgresは、テーブルのデータ量、インデックスの有無、カラムのカーディナリティに応じて、主に4つのスキャン方式から選択します:

  • Seq Scan(シーケンシャルスキャン):ディスクからテーブル全体をページごとに順次読み込みます。先ほどの実行計画にあるRows Removed by Filter: 85470に注目してください。エンジンはわずか1,530行を抽出するためだけに87,000行もの物理行を走査しており、98.2%の破棄率によってディスク帯域が無駄に消費されています。
  • Index Scan(インデックススキャン):B-Treeインデックスをトラバースして特定のタプルポインタ(TID)を特定し、ヒープページから個々のテーブル行を直接取得します。
  • Bitmap Index Scan / Bitmap Heap Scan:シーケンシャルスキャンとインデックススキャンの橋渡しとなる手法です。まずインデックススキャンで条件に一致する物理ページをマークしたメモリビットマップを構築します。続いてヒープスキャンがそれらのページを物理ストレージの順序に従って読み取るため、ランダムシークによる負荷を抑制できます。
  • Index Only Scan(インデックスオンスキャン):最も高速なスキャンパスです。必要なすべてのカラムがインデックスに含まれており、かつテーブルの可視性マップ(Visibility Map)でページがクリーンであると確認できる場合、Postgresはヒープへのアクセスを完全にスキップします。

高度な活用法

ノードのコストを読むだけでは道半ばです。本番環境のボトルネックを特定するには、メモリ溢れ(ディスクスピル)、バッファヒット、プランナのエッジケースまで掘り下げる必要があります。

BUFFERSによるメモリとディスクI/Oの追跡

スロークエリを調査する際は、必ずBUFFERSオプションを付与してください。データがRAM上にキャッシュされていたのか、それとも物理ストレージからの読み取りが必要だったのかを明確に把握できます:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.email, count(o.id) AS total_orders
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country_code = 'US'
GROUP BY u.email
ORDER BY total_orders DESC
LIMIT 10;

出力されたプランにバッファのメトリクスが表示されます:

Buffers: shared hit=412 read=1895 dirtied=0 written=0
  • shared hit (412):PostgreSQLのRAM上のshared_buffersから直接取得されたページ数です。高速でレイテンシはほぼゼロです。
  • shared read (1895):OSキャッシュまたはディスクからフェッチされたページ数です。デフォルトの1ページあたり8 kBで計算すると、1,895回の読み取りは約14.8 MBのディスクまたはキャッシュスループットに相当します。読み取り回数が多い場合は、コールドキャッシュの問題やインデックスの不足を示唆しています。

隠れたシーケンシャルスキャンの原因究明

created_atにインデックスを作成したにもかかわらず、プランナがフルテーブルスキャンを選択し続けることがあります。この挙動を引き起こす代表的な原因は次の3点です:

1. 型の不一致と暗黙の型キャスト

アプリケーションドライバが異なる型のパラメータを渡した場合(例:bigintカラムに対して文字列リテラルを渡す、異なるテキスト照合順序を比較するなど)、Postgresはカラムに関数を適用して型変換を行います。式インデックス(Expression Index)として明示的に構築されていない限り、B-Treeインデックスは式に対して機能しません。

-- 不適切な例:関数によるラップによりB-Treeインデックスが使用されない
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- 適切な例:完全一致によりemail列の標準インデックスが活用される
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- 代替策:大文字小文字を区別しない検索をサポートする式インデックスを作成する
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

2. WHERE句内でのカラム変換

タイムスタンプ列を日付関数でラップすると、インデックスが利用できなくなります。WHERE DATE(created_at) = '2026-03-01'のような記述は避け、代わりにSargable(検索引数化可能)な範囲指定を使用してください:

-- インデックス範囲スキャンをクリーンに利用
EXPLAIN ANALYZE 
SELECT id, total_amount 
FROM orders 
WHERE created_at >= '2026-03-01 00:00:00'
  AND created_at <  '2026-03-02 00:00:00';

3. 低い選択度(Selectivity)のしきい値

クエリプランナは、ランダムI/Oのオーバーヘッドがシーケンシャルアクセスのスループットを上回るかどうかを計算します。検索条件がテーブル全体の10%〜20%以上に一致する場合、インデックスを行き来するよりもシーケンシャルスキャンのほうが実際に高速なケースが多々あります。random_page_costがseq_page_costに近い(例:1.1)高速なNVMeドライブ環境ではこの損益分岐点が変わるものの、大きな結果セットに対するシーケンシャルスキャンは想定通りの正常な挙動です。

ソートやハッシュにおけるメモリ溢れの検出

出力内のソートノードの詳細を確認してください:

Sort Method: external merge  Disk: 18432kB

PostgreSQLではデフォルトでwork_memが4 MBに設定されています。ソート処理やハッシュテーブルのサイズがこの割り当てを超えると、エンジンはディスクに一時データを書き出すため、スループットが大幅に低下します。アクティブなセッションに対してwork_memを引き上げることで、データベース全体の再起動を必要とせずにディスクスピルを解消できます:

SET work_mem = '64MB';
-- クエリを再実行すると以下のような結果になります:
-- Sort Method: quicksort  Memory: 4120kB

実践的なヒント

本番環境で安全にデバッグを行うには、適切な運用のガードレールを設ける必要があります:

  • 更新・削除処理における実データの保護:EXPLAIN ANALYZEは実際にステートメントを実行します。DELETEやUPDATEを渡すと、行が即座に変更されてしまいます。データ変更を伴うクエリはトランザクションブロックで囲み、ロールバックを実行してください:
BEGIN;
EXPLAIN ANALYZE
DELETE FROM sessions WHERE expired_at < NOW();
ROLLBACK;
  • 古い統計情報の更新:推定行数(10)と実際の行数(50,000)が大きく乖離していると、オプティマイザは効率的なハッシュ結合ではなく非効率なネストループ結合を選択してしまいます。ANALYZE orders;を実行してカラムの統計情報を再収集し、正確な実行計画を復元してください。
  • スロークエリログの自動化:クエリの手動キャプチャは不要です。postgresql.confでauto_explainモジュールを読み込めば、設定したパフォーマンスSLAを超えるすべてのクエリの実行計画を自動的にダンプできます:
# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = '250ms'
auto_explain.log_analyze = true
auto_explain.log_buffers = true
  • 巨大な実行ツリーの可視化:15以上のテーブルを結合するような複雑なクエリでは、実行計画が数百行に及ぶことがあります。EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)でプランをエクスポートし、PEV2やDaliboのExplainといった視覚的診断ツールに読み込ませてください。最も負荷のかかっているボトルネックノードを瞬時に特定できます。
Share: