Posted inDatabase

EXPLAIN ANALYZEでPostgreSQLクエリを最適化する：実行計画の読み方とパフォーマンス改善

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

5分でできる：EXPLAIN ANALYZEを初めて実行してみよう

入社したばかりの頃、先輩から「このクエリが8秒かかってる、直してくれ」というチケットを渡されたことがある。SQLを眺めてもどこから手をつければいいかわからなかった。そこで初めて知ったのがEXPLAIN ANALYZE――PostgreSQLのクエリデバッグにおいて、最もシンプルかつ最も強力なツールだ。

さっそく試してみよう。手元にあるクエリにこのコマンドを実行するだけでいい：

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 42
AND status = 'pending';

PostgreSQLはこのようなテキストを返す：

Seq Scan on orders  (cost=0.00..1845.00 rows=12 width=156) (actual time=0.043..18.234 rows=12 loops=1)
  Filter: ((user_id = 42) AND ((status)::text = 'pending'::text))
  Rows Removed by Filter: 89988
Planning Time: 0.215 ms
Execution Time: 18.251 ms

意味がわからなくて当然だ。読み続けてほしい――各部分を丁寧に解説する。

実行計画の読み方：PostgreSQLが何をしているか理解する

実行計画の構造

実行計画は内側から外側へ、下から上へ読む。最も深い位置にあるノードが最初に実行される。各行の形式は次の通りだ：

[スキャンの種類]  (cost=X..Y rows=N width=W) (actual time=A..B rows=R loops=L)
  • cost=X..Y：推定コスト――Xは起動コスト、Yは総コスト（相対的な単位であり、ミリ秒ではない）
  • rows=N：PostgreSQLが推定する返却行数
  • actual time=A..B：実際の処理時間（ミリ秒）――Aは最初の行が返るまでの時間、Bは合計時間
  • rows=R：実際に返された行数
  • loops=L：このノードが実行された回数――actual timeと掛け合わせると実際の合計時間が得られる

スキャンの種類――速いものと遅いもの

実行計画を開いてまず確認するのがスキャンの種類だ。押さえておくべき4種類を紹介する：

  • Seq Scan：テーブルを先頭から末尾まで全件スキャンする。数百万行のテーブルでこれが出たら即対処が必要だ。
  • Index Scan：インデックスを使って行を検索する。大規模なテーブルではSeq Scanより大幅に速い。
  • Index Only Scan：最良の選択肢――インデックスから直接データを取得し、ヒープの読み込みが不要になる。
  • Bitmap Heap Scan：複数のインデックスを組み合わせる。WHERE句に複数の条件がある場合に効果的だ。

クエリに問題がある兆候

この2つの数値を見比べてほしい：

-- 推定: rows=1、実際: rows=50000
-- → 統計情報が古くなっている。ANALYZEが必要

-- 推定: rows=50000、実際: rows=1
-- → プランナーが誤った実行計画を選択している。不要なスキャンが発生

この2つの数値が10倍以上乖離している場合、プランナーが誤った実行計画を選んでいる――主に統計情報が古いことが原因だ。ANALYZE orders;を実行するのが最初の対処法だ。

実践：遅いクエリを最初から最後までデバッグする

テスト環境のセットアップ

-- 100万行のテストテーブルを作成
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT,
    product_id INT,
    status VARCHAR(20),
    total DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT NOW()
);

INSERT INTO orders (user_id, product_id, status, total)
SELECT
    (random() * 10000)::INT,
    (random() * 1000)::INT,
    CASE (random() * 3)::INT
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'completed'
        ELSE 'cancelled'
    END,
    (random() * 1000)::DECIMAL(10,2)
FROM generate_series(1, 1000000);

遅いクエリ――インデックスなし

EXPLAIN ANALYZE
SELECT id, total, status
FROM orders
WHERE user_id = 5000
AND created_at > '2024-01-01';

結果：

Seq Scan on orders  (cost=0.00..23334.00 rows=5 width=28) (actual time=2.341..145.672 rows=48 loops=1)
  Filter: ((user_id = 5000) AND (created_at > '2024-01-01'::timestamp))
  Rows Removed by Filter: 999952
Execution Time: 145.891 ms

100万行スキャンして48行しか取得しない――完全な無駄だ。インデックスを追加しよう：

CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);

-- 再実行
EXPLAIN ANALYZE
SELECT id, total, status
FROM orders
WHERE user_id = 5000
AND created_at > '2024-01-01';

インデックス追加後の結果：

Index Scan using idx_orders_user_created on orders
  (cost=0.42..18.63 rows=48 width=28) (actual time=0.052..0.312 rows=48 loops=1)
  Index Cond: ((user_id = 5000) AND (created_at > '2024-01-01'::timestamp))
Execution Time: 0.387 ms

145msから0.4msへ。これが適切なインデックスの威力だ。

応用編：EXPLAINの便利なオプション

EXPLAIN (ANALYZE, BUFFERS) ― 実際のI/Oを確認する

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed'
GROUP BY u.id, u.name
HAVING COUNT(o.id) > 10;

BUFFERSオプションを使うと、ディスクから読んだブロック数（read）とキャッシュから読んだブロック数（hit）が確認できる。readが多い場合はデータがキャッシュされていない証拠――CPUではなくI/Oがボトルネックになっている。

JOINの分析：Hash Join vs Nested Loop vs Merge Join

PostgreSQLには3種類のJOIN戦略があり、プランナーがテーブルサイズ、既存のインデックス、統計情報に基づいて自動選択する：

  • Hash Join：小さい方のテーブルからハッシュテーブルを構築し、大きいテーブルでプローブする。JOINカラムにインデックスがない場合に効果的だ。
  • Nested Loop：外側テーブルの各行に対して内側テーブルを検索する。内側テーブルが小さいか、良いインデックスがある場合に高速だ。
  • Merge Join：両テーブルがJOINキーでソートされている必要がある。両テーブルにソート済みのインデックスがある場合に最も効率的だ。
-- 特定の戦略を無効にしてパフォーマンスを比較する
SET enable_hashjoin = off;
EXPLAIN ANALYZE SELECT ...;
SET enable_hashjoin = on;

pgBadgerとauto_explain：遅いクエリを自動検出する

クエリを1つずつ手動でEXPLAINするのは時間がかかる。auto_explainを有効にすると、PostgreSQLが遅いクエリを自動でログに記録してくれる：

# postgresql.conf
shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 1000  # 1秒以上かかるクエリをログに記録
auto_explain.log_analyze = true
auto_explain.log_buffers = true

その後、pgBadgerでログを解析してHTMLレポートを生成できる：

pgbadger /var/log/postgresql/postgresql-14-main.log -o report.html

実務経験から得た実践的なTips

1. 推定行数と実際の行数を必ず確認する

プランナーの推定が大幅に外れている場合は、VACUUM ANALYZEを実行して統計情報を更新しよう。INSERT/DELETE/UPDATEが頻繁に発生するテーブルには、autovacuumの頻度を上げることを検討しよう：

ALTER TABLE orders SET (
    autovacuum_analyze_scale_factor = 0.01,  -- データの1%が変更されたらANALYZEを実行
    autovacuum_vacuum_scale_factor = 0.05
);

2. インデックスが常に有効とは限らない

PostgreSQLは、クエリが全体の約10〜15%以上の行を返すと推定した場合、自動的にSeq Scanを選択する。その場合はインデックスを経由するよりもテーブル全体をスキャンする方が実際に速い。無理にインデックスを使わせようとしないこと――このケースではプランナーがほぼ正しい判断をしている。

3. 複合インデックス――カラムの順序が重要

-- このインデックスは以下に効果的：WHERE user_id = ? AND status = ?
-- または単独で：WHERE user_id = ?
CREATE INDEX idx_user_status ON orders(user_id, status);

-- しかしこれには効果なし：WHERE status = ?（user_idなし）
-- 別途インデックスが必要：CREATE INDEX idx_status ON orders(status);

4. Index Only Scanを実現するカバリングインデックス

特定のカラムだけをSELECTするクエリがあるなら、カバリングインデックスを作成することでPostgreSQLがヒープを読まずに済む：

-- よく実行されるクエリ：
SELECT id, total FROM orders WHERE user_id = 42 AND status = 'pending';

-- カバリングインデックス（SELECTするカラムをINCLUDE）：
CREATE INDEX idx_orders_covering
ON orders(user_id, status)
INCLUDE (id, total);

5. explain.depesz.comで可視化する

EXPLAIN ANALYZEの出力をコピーしてexplain.depesz.comまたはexplain.dalibo.comにペーストすると、実行計画を視覚的に確認できる――ノードが多い複雑な計画を解析するのに特に便利だ。

6. 焦って最適化しない――まずプロファイリングを

最もよく見かける間違い：測定せずにインデックスを闇雲に追加すること。インデックスはストレージを消費し、INSERT/UPDATE/DELETEを遅くする。EXPLAIN ANALYZEで本当のボトルネックが確認できてから追加すること――そして追加後も必ず再計測して効果を確認しよう。

Share:
Tags: