チューニング

PostgreSQL の実行計画

PostgreSQL の EXPLAIN 活用法。ANALYZE・BUFFERS・推定乖離の読み方と auto_explain。

EXPLAIN と EXPLAIN ANALYZE の違い

PostgreSQL の EXPLAIN には2 つのモードがあります:

EXPLAIN: クエリは実行しない。オプティマイザが出した推定コスト / 推定行数だけを表示
EXPLAIN ANALYZE: クエリを実際に実行し、actual time・actual rows・ループ回数を表示

チューニングでは基本的に EXPLAIN ANALYZE を使います。ただし UPDATE / DELETE も本当に実行される点に注意（ロールバックしたい場合は BEGIN; ... ROLLBACK; で囲む）。

sql

-- 推定だけ (高速、副作用なし)
EXPLAIN SELECT * FROM orders WHERE user_id = 42;

-- 実測込み (実際に実行される)
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 42;

-- UPDATE を実行せずに計画を見たい
BEGIN;
EXPLAIN ANALYZE UPDATE orders SET status='x' WHERE id = 1;
ROLLBACK;

BUFFERS オプションで I/O を見る

EXPLAIN (ANALYZE, BUFFERS) を使うと、各ノードで何ブロック読んだかが見えるようになります。キャッシュに乗っているか（shared hit）、ディスクから読んだか（shared read）を区別できるので、「初回は遅いが 2 回目は速い」といった現象の原因が分かります。

shared hit: 共有バッファ（キャッシュ）ヒット。速い
shared read: ディスク（または OS キャッシュ）から読み込み。遅い
temp read / written: 一時ファイル使用。メモリ不足のサイン（work_mem 不足）

2 回連続で EXPLAIN ANALYZE を実行し、2 回目は全部 shared hit になるかを見るのが便利です。

sql

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM orders WHERE user_id = 42;

-- 出力例:
--  Index Scan using idx_orders_user on orders
--    (actual time=0.021..0.058 rows=12 loops=1)
--    Buffers: shared hit=14
--  Planning Time: 0.123 ms
--  Execution Time: 0.081 ms

推定と実測の乖離を見る

EXPLAIN ANALYZE の各ノードには rows=推定値 と (actual ... rows=実測値) の両方が出ます。この 2 つが桁違いに離れているら、それは問題のサインです:

統計情報が古い → ANALYZE table_name; を実行
相関のある列（例: city と zip） → CREATE STATISTICS で複数列統計を作る
パラメータスニッフィング問題 → 準備済み文で再現するかチェック

乖離が大きいと、オプティマイザが「Nested Loop で十分」と思って組んだ計画が、実は 100 万行の結合になっていた、というような悲劇が起きます。

ノード種別のチートシート

よく見るノードと「何を意味するか」の早見表:

ノード	意味	見るときのポイント
Seq Scan	フルテーブル走査	小テーブル、または大半の行を読むなら OK。大テーブルで出たら要注意
Index Scan	インデックス経由で行を特定し、テーブル本体（Heap）も読む	選択率が高いと効率が落ちる（1 行につき 1 回のランダム I/O）
Index Only Scan	インデックスだけで完結（カバリング成功）	VACUUM が足りないと Heap Fetches が増える。副作用で遅くなることがある
Bitmap Heap Scan + Bitmap Index Scan	複数条件の候補をビットマップで合成して絞り込み	複数のインデックスを AND/OR で組み合わせるときに登場
Hash Join / Merge Join / Nested Loop	JOIN アルゴリズムの選択	Nested Loop は内側が小さいときだけ速い。大量 × 大量は Hash/Merge が有利
Sort	`ORDER BY` 等の明示的ソート	`Sort Method: external merge` が出たら work_mem 不足でディスクに溢れている
Gather / Parallel Seq Scan	並列実行	max_parallel_workers_per_gather の効果を確認
CTE Scan / Subplan	サブクエリ / CTE の評価	CTE は PostgreSQL 12 以降で自動インライン化対象（`MATERIALIZED` 指定で固定化可）

auto_explain で本番のスロークエリを捕まえる

本番環境で「なぜか遅い 1 クエリ」を再現するのは難しいものです。auto_explain モジュールを使うと、一定時間以上かかったクエリの実行計画を自動的にログ出力できます。

設定例（postgresql.conf または ALTER SYSTEM）:

shared_preload_libraries = 'auto_explain' またはセッションごとに LOAD 'auto_explain'
auto_explain.log_min_duration = '500ms': 500ms 以上のクエリをログ
auto_explain.log_analyze = on: ANALYZE 情報も出す（負荷に注意）
auto_explain.log_buffers = on: BUFFERS 情報も出す

log_analyze は本番で on にすると各クエリにオーバーヘッドが載るため、pg_stat_statements で上位を絞ってから、必要な時だけ有効化するのが安全です。

sql

-- セッションで試す
LOAD 'auto_explain';
SET auto_explain.log_min_duration = '100ms';
SET auto_explain.log_analyze = on;

-- 以降、100ms 超のクエリは
-- サーバーログに EXPLAIN ANALYZE が出力される