こちらの記事の続きとなっています。
PostgreSQLのEXPLAIN ANALYZEは、クエリの実行計画を確認し、パフォーマンスを分析するためのツールです。本記事では、実行計画の読み方、よくある問題の特定と改善方法まで、実践的な活用方法を紹介します。
基本的な使い方
EXPLAINとEXPLAIN ANALYZEの違い
EXPLAINだけでも実行計画は見られますが、実際の実行時間や処理した行数は表示されません。ANALYZEを付けることで、実際にクエリを実行してより詳細な情報が得られます。
-- 実行計画のみを確認（クエリは実行されない）
EXPLAIN SELECT * FROM users WHERE id = 1;
-- 実際にクエリを実行して、実行時間や処理行数を表示
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
オプション
EXPLAIN ANALYZEには様々なオプションがあります：
-
BUFFERS: バッファ使用統計を表示 -
VERBOSE: 各ノードの出力列リストなど、より詳細な情報を表示 -
WAL: WALに関する統計情報を表示 -
SETTINGS: クエリプランナーが使用した設定を表示 -
FORMAT: JSONやYAML、XML形式で出力（FORMAT JSONなど）
使用例：
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) SELECT * FROM users WHERE id = 1;
EXPLAIN (ANALYZE, FORMAT JSON) SELECT * FROM users WHERE id = 1;
基本的な実行計画の読み方
実行計画は木構造（ツリー構造）で表示されます。各ノードには、以下のような情報が含まれます。
QUERY PLAN
────────────────────────────────────────────────────────────────────────────
Seq Scan on users (cost=0.00..22.50 rows=1000 width=36) (actual time=0.123..12.456 rows=1000 loops=1)
Filter: (id = 1)
Rows Removed by Filter: 999
Planning Time: 0.123 ms
Execution Time: 12.456 ms
重要な項目
-
cost（コスト）:
cost=0.00..22.50- 最初の数値は最初の行取得までのコスト、2番目は全体のコスト -
rows（推定行数）:
rows=1000- プランナーが推定した処理行数 -
width（幅）:
width=36- 1行あたりの平均バイト数 -
actual time（実際の実行時間）:
actual time=0.123..12.456- 最初の数値は最初の行取得までの時間、2番目は全体の実行時間（ミリ秒） -
actual rows（実際の行数）:
actual rows=1000- 実際に処理された行数。推定行数（rows）と比較して統計情報の正確性を判断します -
loops（ループ回数）:
loops=1- このノードが実行された回数 -
Planning Time（計画時間）: 実行計画を作成するのにかかった時間
-
Execution Time（実行時間）: 実際にクエリを実行するのにかかった時間
主要なスキャンタイプ
PostgreSQLには、データを読み取る方法がいくつかあります。それぞれの特徴を理解することが重要です。
1. Seq Scan
テーブル全体を順番に読み込む方法です。
Seq Scan on users (cost=0.00..22.50 rows=1000 width=36) (actual time=0.123..12.456 rows=1000 loops=1)
特徴：
- テーブル全体を読み込むため、データが多いと遅くなる
- インデックスが使えない場合や、テーブルの大部分を読み込む必要がある場合に使用
- 小さいテーブルでは高速
-- インデックスがない列での検索
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%example.com';
2. Index Scan
インデックスを使って必要な行だけを効率的に取得する方法です。
Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=36) (actual time=0.045..0.046 rows=1 loops=1)
Index Cond: (id = 1)
特徴：
- インデックスを使って特定の行を素早く見つける
- 主キーやユニークキーでの検索に最適
- 行数が少ない場合に効率的
-- 主キーでの検索（インデックスが使われる）
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
3. Bitmap Index Scan + Bitmap Heap Scan
インデックスを複数回参照して、行IDのリストを作成し、その後テーブルを読み込む方法です。
Bitmap Heap Scan on users (cost=4.29..14.85 rows=10 width=36) (actual time=0.123..0.456 rows=10 loops=1)
Recheck Cond: (status = 'active')
-> Bitmap Index Scan on idx_users_status (cost=0.00..4.29 rows=10 width=0) (actual time=0.098..0.098 rows=10 loops=1)
Index Cond: (status = 'active')
特徴：
- 複数の条件を組み合わせる場合や、範囲検索に適している
- Index Scanよりも多くの行を扱う場合に使用される
- インデックスで絞り込んだ後に、テーブルを読み込む
例：
-- 範囲検索（Bitmap Scanが使われることがある）
EXPLAIN ANALYZE SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
4. Index Only Scan
テーブルにアクセスせず、インデックスだけで必要な情報を取得する方法です。
Index Only Scan using idx_users_email on users (cost=0.29..4.30 rows=1 width=36) (actual time=0.023..0.024 rows=1 loops=1)
Index Cond: (email = 'user@example.com')
Heap Fetches: 0
特徴：
- 最も高速なスキャン方法
- 必要な列がすべてインデックスに含まれている場合に使用
- テーブルへのアクセスが不要なため、I/Oが少ない
-- インデックスに含まれる列のみを取得
EXPLAIN ANALYZE SELECT id, email FROM users WHERE email = 'user@example.com';
5. パラレルクエリ（並列実行）
複数のワーカープロセスを使ってクエリを並列実行する機能です。
Gather (cost=1000.00..12550.00 rows=100000 width=36) (actual time=0.123..150.456 rows=100000 loops=1)
Workers Planned: 4
Workers Launched: 4
-> Parallel Seq Scan on large_table (cost=0.00..10550.00 rows=25000 width=36) (actual time=0.045..120.234 rows=25000 loops=5)
Filter: (status = 'active')
特徴：
-
Workers Planned: 計画されたワーカーの数 -
Workers Launched: 実際に起動したワーカーの数 - 大きなテーブルのスキャンや集約処理で効果的
-
max_parallel_workers_per_gatherパラメータで制御可能
パラレルクエリを有効にする条件：
- テーブルサイズが一定以上（
min_parallel_table_scan_size） - ワーカープロセスが利用可能
- 実行するクエリが並列化可能
6. GINインデックス（全文検索などで使用）
全文検索で使用されるGINインデックスの実行計画です。
Bitmap Heap Scan on articles (cost=50.00..150.00 rows=10 width=100) (actual time=5.123..15.456 rows=10 loops=1)
Recheck Cond: (to_tsvector('japanese', content) @@ to_tsquery('japanese', '検索語'))
-> Bitmap Index Scan on articles_content_gin_idx (cost=0.00..50.00 rows=1000 width=0) (actual time=4.098..4.098 rows=1000 loops=1)
Index Cond: (to_tsvector('japanese', content) @@ to_tsquery('japanese', '検索語'))
全文検索の実行計画例：
-- 全文検索用のGINインデックスを作成
CREATE INDEX articles_content_gin_idx ON articles
USING GIN (to_tsvector('japanese', content));
-- 全文検索クエリの実行計画を確認
EXPLAIN ANALYZE
SELECT id, title, content
FROM articles
WHERE to_tsvector('japanese', content) @@ to_tsquery('japanese', '検索語');
特徴：
- 全文検索に適している
- 複数の語句を含む検索に効率的
- JSONBカラムの検索でも使用される
よくある非効率なパターンと改善方法
パターン1: インデックスが使われていない
問題のあるクエリ：
EXPLAIN ANALYZE SELECT * FROM users WHERE LOWER(email) = 'user@example.com';
実行計画：
Seq Scan on users (cost=0.00..25.50 rows=10 width=36) (actual time=0.123..15.456 rows=1 loops=1)
Filter: (lower(email) = 'user@example.com'::text)
Rows Removed by Filter: 999
問題点：
- 関数を使っているため、インデックスが使われない
- テーブル全体をスキャンしている
改善方法：
- 関数インデックスを作成する
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
- または、データを正規化して保存する
-- email_lowerカラムに小文字化した値を保存しておく
CREATE INDEX idx_users_email_lower ON users (email_lower);
パターン2: 不要な列をSELECTしている
問題のあるクエリ：
EXPLAIN ANALYZE SELECT * FROM users WHERE id = 1;
大きなテーブルでSELECT *を使うと、不要な列も読み込んでしまうため、メモリやI/Oの無駄になります。
改善方法：
-- 必要な列だけをSELECTする
EXPLAIN ANALYZE SELECT id, name FROM users WHERE id = 1;
パターン3: 統計情報が古い
実行計画：
Index Scan using idx_users_status on users (cost=0.29..8.30 rows=10 width=36) (actual time=0.045..0.456 rows=1000 loops=1)
Index Cond: (status = 'active')
問題点：
- 推定行数（rows=10）と実際の行数（actual rows=1000）が大きく異なる
- 統計情報が古いため、実行計画が最適化されていない可能性がある
改善方法：
-- 統計情報を更新する
ANALYZE users;
-- より詳細な統計情報を取得する
ALTER TABLE users ALTER COLUMN status SET STATISTICS 1000;
ANALYZE users;
パターン4: N+1問題（ループが多い）
実行計画：
Nested Loop (cost=0.29..1000.30 rows=100 width=72) (actual time=0.123..50.456 rows=100 loops=1)
-> Seq Scan on orders (cost=0.00..25.50 rows=100 width=36) (actual time=0.045..2.456 rows=100 loops=1)
-> Index Scan using users_pkey on users (cost=0.29..8.30 rows=1 width=36) (actual time=0.023..0.024 rows=1 loops=100)
Index Cond: (id = orders.user_id)
問題点：
-
loops=100となっており、usersテーブルへのスキャンが100回実行されている - 毎回インデックススキャンしているが、ループが多いと遅くなる
改善方法：
-- JOINを使うことで、一度のスキャンで取得できる
EXPLAIN ANALYZE
SELECT o.*, u.*
FROM orders o
JOIN users u ON u.id = o.user_id;
pg_stat_statementsと組み合わせた実践的分析
EXPLAIN ANALYZEは個別のクエリの分析に有効ですが、実際の運用ではpg_stat_statements拡張機能と組み合わせることで、より効果的な分析ができます。
pg_stat_statementsのセットアップ
pg_stat_statementsは追加の共有メモリを使うため、事前に有効化設定が必要です（反映には再起動が必要です）。
-
postgresql.conf（またはマネージドサービスのパラメータ）で設定
shared_preload_libraries = 'pg_stat_statements'
-
DBごとに拡張を作成
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
-- 最も時間がかかっているクエリを確認
SELECT
query,
calls,
total_exec_time,
mean_exec_time,
max_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
実行計画との組み合わせ
-- 1. 問題のあるクエリを特定
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE mean_exec_time > 100 -- 100ms以上かかるクエリ
ORDER BY mean_exec_time DESC;
-- 2. 特定したクエリの実行計画を確認
EXPLAIN (ANALYZE, BUFFERS)
-- ここに問題のあるクエリを貼り付ける
;
実践例：パフォーマンス改善の流れ
実際のクエリでパフォーマンス改善を行う流れを見てみましょう。
改善前のクエリ
-- ユーザー名で検索するクエリ
EXPLAIN ANALYZE
SELECT * FROM users WHERE name LIKE '%田中%';
実行計画：
Seq Scan on users (cost=0.00..25.50 rows=100 width=36) (actual time=0.123..15.456 rows=5 loops=1)
Filter: (name ~~ '%田中%'::text)
Rows Removed by Filter: 995
問題点：
- シーケンシャルスキャンで全件検索
- 実行時間が15.456ミリ秒と比較的遅い
改善案1: 前方一致に変更できる場合
-- 前方一致に変更（インデックスが使える）
CREATE INDEX idx_users_name ON users (name);
EXPLAIN ANALYZE
SELECT * FROM users WHERE name LIKE '田中%';
実行計画：
Index Scan using idx_users_name on users (cost=0.29..8.30 rows=5 width=36) (actual time=0.045..0.056 rows=5 loops=1)
Index Cond: ((name >= '田中'::text) AND (name < '田下'::text))
Filter: (name ~~ '田中%'::text)
改善効果：
- 実行時間が0.056ミリ秒に短縮（この例では約275倍高速化）
- インデックススキャンが使われる
※ 実際の改善効果はデータ量やデータ分布によって異なります。
改善案2: 全文検索を使う
部分一致がどうしても必要な場合は、全文検索（Full Text Search）を検討します。
-- 全文検索用のインデックスを作成
CREATE INDEX idx_users_name_gin ON users USING GIN (to_tsvector('japanese', name));
EXPLAIN ANALYZE
SELECT * FROM users
WHERE to_tsvector('japanese', name) @@ to_tsquery('japanese', '田中');
改善案3: JSONBカラムのインデックスを使う
JSONBデータを扱う場合も、適切なインデックスが重要です。
-- JSONBカラムにGINインデックスを作成
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name TEXT,
metadata JSONB -- タグや属性など
);
CREATE INDEX products_metadata_gin_idx ON products USING GIN (metadata);
-- JSONBクエリの実行計画を確認
EXPLAIN ANALYZE
SELECT id, name, metadata
FROM products
WHERE metadata @> '{"category": "electronics"}'::jsonb;
実行計画：
Bitmap Heap Scan on products (cost=10.00..50.00 rows=100 width=100) (actual time=0.123..2.456 rows=50 loops=1)
Recheck Cond: (metadata @> '{"category": "electronics"}'::jsonb)
-> Bitmap Index Scan on products_metadata_gin_idx (cost=0.00..10.00 rows=100 width=0) (actual time=0.098..0.098 rows=100 loops=1)
Index Cond: (metadata @> '{"category": "electronics"}'::jsonb)
特徴：
- JSONBデータの効率的な検索が可能
-
@>演算子で部分一致検索 - 柔軟なデータ構造を扱う際に便利
まとめ
EXPLAIN ANALYZEを活用についてまとめました。
私自身が調べながら書いた記事なので、初歩的な内容も含まれているかもしれません。
ポイントは以下です。
-
実行計画の基本項目を理解する: cost、actual time、actual rows、loopsなど、各項目の意味を理解する
-
スキャンタイプを把握する: Seq Scan、Index Scan、Bitmap Scanなど、それぞれの特徴と適切な使用場面を理解する
-
問題パターンを認識する: インデックスが使われていない、統計情報が古い、N+1問題など、よくある問題パターンに慣れる
-
組み合わせる:
pg_stat_statementsと組み合わせることで、実際の運用環境での問題クエリを効率的に特定できる