PostgreSQL クエリ最適化詳細ガイド：EXPLAIN ANALYZE、インデックス戦略、実戦チューニング
数据库
本番環境を停止させたクエリ
午前2時47分。オンコールの電話が鳴る。通常12msで応答するAPIエンドポイントが30秒でタイムアウトしている。データベースのCPU使用率が100%に張り付いている。調査の結果、以前は500行をスキャンしていたクエリが4700万行をスキャンしていることが判明。8億行のテーブルに対する ORDER BY created_at DESC LIMIT 20 —— 3日前のマイグレーションで、それを支えるはずのインデックスが削除されていたのだ。テーブルが臨界点を超えるまで、誰も気づかなかった。
PostgreSQLクエリ最適化はチェックリストの暗記ではない。クエリプランナがどう考え、なぜ特定の判断を下し、どうすれば正しい情報を与えられるかを理解することだ。この記事では、原理、ツール、そして本番システムからの5つの実戦最適化ケースをカバーする。
EXPLAIN ANALYZEを読む：唯一重要なスキル
すべてのPostgreSQL最適化は EXPLAIN ANALYZE から始まる。EXPLAIN ではない — EXPLAIN ANALYZE だ。違い：EXPLAIN はコストを推定する。EXPLAIN ANALYZE は実際にクエリを実行し、実時間を報告する。
EXPLAIN出力の構造
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id
ORDER BY order_count DESC
LIMIT 10;
Limit (cost=12543.28..12543.31 rows=10 width=44)
(actual time=342.112..342.115 rows=10 loops=1)
-> Sort (cost=12543.28..12554.78 rows=4600 width=44)
(actual time=342.110..342.112 rows=10 loops=1)
Sort Key: (count(o.id)) DESC
Sort Method: top-N heapsort Memory: 26kB
-> HashAggregate (cost=12320.50..12366.50 rows=4600 width=44)
(actual time=338.450..340.120 rows=4523 loops=1)
Group Key: u.id
Batches: 1 Memory Usage: 1169kB
-> Hash Right Join (cost=4560.20..11980.30 rows=68040 width=40)
(actual time=125.340..310.280 rows=67200 loops=1)
Hash Cond: (o.user_id = u.id)
-> Seq Scan on orders o (cost=0.00..6540.00 rows=500000 width=8)
(actual time=0.015..85.400 rows=500000 loops=1)
-> Hash (cost=4520.20..4520.20 rows=3200 width=36)
(actual time=125.110..125.112 rows=3200 loops=1)
Buckets: 4096 Batches: 1 Memory Usage: 253kB
-> Seq Scan on users u
(cost=0.00..4520.20 rows=3200 width=36)
(actual time=0.020..124.500 rows=3200 loops=1)
Filter: (created_at > '2025-01-01'::date)
Rows Removed by Filter: 96800
Planning Time: 1.234 ms
Execution Time: 342.356 ms
各数値の意味
| フィールド | 意味 | 注目点 |
|---|---|---|
cost=x..y |
起動コスト..総コスト（任意単位） | 推定行数と実績の差が大きい場合 |
actual time=x..y |
最初の行を返すms..全行を返すms | 最初の行の時間が高い = 出力開始が遅い |
rows=N（推定） |
プランナの行数推定 | 実績と10倍以上の差 = 問題 |
rows=N（実績） |
実際に処理された行数 | 推定と比較 |
loops=N |
このノードの実行回数 | Nested Loopの内部：高ループ数 + 高コスト/回 |
Buffers: shared hit=N |
バッファキャッシュから読み取り | 高い = メモリ圧力？ |
Buffers: shared read=N |
ディスクから読み取り | 高い = インデックス不足かコールドキャッシュ |
Rows Removed by Filter |
スキャン後フィルタで除去 | 高い = 有効なインデックス不足 |
最も重要な3つの数値
- 推定行数 vs 実績行数の比率：プランナが100行と推定して実際は50,000行なら、下流のすべてのノードが誤った判断を下す。悪いクエリプランの根本原因のほとんどがこれだ。
- Rows Removed by Filter：「フィルタで除去された」各行はディスクから読み取られ、処理され、破棄されている。この数値が数百万で実績行数が数百なら、インデックスが必要だ。
- Buffers: shared read：ディスク読み取り。適切にチューニングされたシステムでは、ほとんどが
shared hit（キャッシュから）であるべき。繰り返し実行されるクエリで高いreadがある場合、shared_buffersが小さすぎるか、ワーキングセットがメモリを超えている。
インデックス戦略：PostgreSQLが持つものを知る
PostgreSQLのインデックスタイプは多くの開発者が認識している以上に豊富だ。選択を誤ると、プランナはインデックスを無視する。
インデックスタイプ決定マトリックス
| インデックスタイプ | 最適な用途 | ストレージオーバーヘッド | 書き込みペナルティ | 制限事項 |
|---|---|---|---|---|
| B-tree | 等値、範囲、ソート、LIKE 'abc%' | 中程度（インデックス列の約2倍） | 低 | 前方ワイルドカード LIKE '%abc' で失敗 |
| Hash | 等値のみ（=） |
低 | 非常に低 | 範囲クエリ、ソート不可 |
| GIN | 全文検索、配列、JSONB包含 | 高（転置インデックス） | 高 | 完全一致が遅い、書き込みペナルティ大 |
| GiST | 幾何データ、全文、範囲型 | 中程度 | 中程度 | 単純型ではB-treeより読み取り遅い |
| BRIN | 物理的相関のある超大規模テーブル | 最小 | 最小 | データが物理的に順序付けられている場合のみ有効 |
各インデックスタイプの実践
B-tree — 90%のソリューション：
-- 標準B-tree：=, <, >, BETWEEN, ORDER BY, LIKE 'prefix%' をサポート
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
-- 複合インデックス：列の順序が重要
-- クエリ：WHERE status = 'active' AND created_at > '2025-01-01'
CREATE INDEX idx_orders_status_created ON orders(status, created_at);
-- statusを先に（等値）、created_atを後に（範囲）— 最適
-- カバリングインデックス（INCLUDE）：index-only scanを実現
CREATE INDEX idx_orders_user_status ON orders(user_id, status)
INCLUDE (total_amount, created_at);
GIN — JSONBと全文検索：
-- JSONBインデックス：@> (含む), ? (キー存在), ?| (いずれかのキー)
CREATE INDEX idx_events_payload ON events USING GIN (payload jsonb_path_ops);
-- 全文検索
CREATE INDEX idx_posts_search ON posts
USING GIN (to_tsvector('english', title || ' ' || body));
SELECT * FROM posts
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('postgresql & optimization');
BRIN — 10億行テーブルの救世主：
-- BRIN：Block Range INdex — ブロック範囲ごとにmin/maxを保存
-- 超巨大な追記型テーブル向けの極小インデックス（多くの場合テーブルサイズの1%未満）
CREATE INDEX idx_events_timestamp_brin ON events
USING BRIN (created_at)
WITH (pages_per_range = 32);
-- 1TBテーブル → 50MB BRINインデックス → サブ秒の範囲クエリ
SELECT * FROM events
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-02';
ケーススタディ1：N+1ジェネレーター
症状
ORM生成クエリが8.2秒かかっていた。開発者は理由がわからなかった — 「100記事とその著者を取得しているだけなのに。」
診断
-- ORMが実際に生成したSQL（簡略化）
-- 記事用に1クエリ、その後著者用に100の個別クエリ
SELECT * FROM articles ORDER BY published_at DESC LIMIT 100;
-- 各記事に対して：
SELECT * FROM users WHERE id = $1; -- 100回実行
修正
-- 101クエリを2クエリに置き換え
SELECT * FROM articles ORDER BY published_at DESC LIMIT 100;
-- 著者を一括取得
SELECT * FROM users WHERE id = ANY($1);
修正後：42ms。ORMのeager loading機能を使うのが答えだった。常にORMが生成するSQLを確認すること。
ケーススタディ2：OFFSETの罠
症状
ページ1は速い（20ms）、ページ10は遅くなる（200ms）、ページ500はタイムアウト（30s+）。
修正：キーセットページネーション
-- ページ1
SELECT * FROM events
WHERE user_id = 456
ORDER BY created_at DESC, id DESC
LIMIT 21;
-- 最後の行：created_at = '2025-03-15 14:23:01', id = 88234
-- ページ2（ページ1の最後の行をカーソルとして使用）
SELECT * FROM events
WHERE user_id = 456
AND (created_at, id) < ('2025-03-15 14:23:01', 88234)
ORDER BY created_at DESC, id DESC
LIMIT 21;
CREATE INDEX idx_events_user_cursor ON events(user_id, created_at DESC, id DESC);
修正後：安定して3-5ms/ページ。ページの深さに関係なく一貫したパフォーマンス。
ケーススタディ3：関数によるインデックス無効化
症状
5000万行のprofilesテーブルでlocationにインデックスがあるにもかかわらず全表スキャン。
修正
-- B-treeをGiST空間インデックスに置き換え
CREATE INDEX idx_profiles_location_gist ON profiles
USING GIST (location);
修正後：12ms（以前は4.2s）。PostGIS、pg_trgmなどの拡張を使用する場合、インデックスタイプが演算子と一致することを確認すること。
ケーススタディ4：10億行テーブルの時間範囲クエリ
症状
8億行のイベントテーブルに対する「直近7日間」クエリが45秒。
修正：パーティショニング + BRIN
-- 月次パーティショニング
CREATE TABLE analytics_events (
id BIGSERIAL,
event_type TEXT NOT NULL,
duration_ms INTEGER,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (created_at);
-- 各パーティションにBRINインデックス
CREATE INDEX idx_events_brin_created
ON analytics_events USING BRIN (created_at)
WITH (pages_per_range = 32);
修正後：800ms（以前は45s）。パーティションプルーニングで24パーティション中23を即座に除外。BRINインデックスは2MB（以前の38GBから削減）— 完全にメモリにキャッシュ。
ケーススタディ5：統計情報の盲点
症状
ステージングで20msのクエリが本番で8秒。同じスキーマ、同じインデックス。
修正
-- 統計情報を更新
ANALYZE shipments;
-- 相関列用の拡張統計を作成
CREATE STATISTICS shipments_status_warehouse (dependencies)
ON status, warehouse_id FROM shipments;
ANALYZE shipments;
-- 複合インデックス
CREATE INDEX idx_shipments_warehouse_status_created
ON shipments(warehouse_id, status, created_at DESC);
修正後：2ms（以前は8s）。
PostgreSQL バージョン機能改善
| 機能 | バージョン | 影響 |
|---|---|---|
| 並列クエリ実行 | PG 9.6+ | seq scan、hash joinに複数ワーカー |
| 並列B-treeインデックススキャン | PG 11+ | ワーカーがインデックススキャンの進捗を共有 |
| 増分ソート | PG 13+ | 部分的にソート済みデータをより高速にソート |
| 拡張統計 | PG 10+（13+で改善） | 相関列の認識 |
| LZ4圧縮（TOAST） | PG 14+ | PGLZより高速な展開 |
| 並列hash join | PG 14+ | マルチワーカーでのハッシュテーブル構築 |
| MERGEコマンド | PG 15+ | 単一文のupsert |
| 並列full & right hash join | PG 16+ | より多くの並列joinタイプ |
| pg_stat_io | PG 16+ | I/Oタイミング統計 |
接続プーリング：見落とされがちなパフォーマンス層
# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
pool_mode = transaction # 重要：トランザクションプーリングを使用
default_pool_size = 25 # CPUコア数 * 2 が目安
max_client_conn = 500
reserve_pool_size = 5
| pool_mode | 動作 | 最適な用途 |
|---|---|---|
session |
クライアントセッションごとに1つのサーバー接続 | セッション状態を使用するレガシーアプリ |
transaction |
各トランザクション後に接続を解放 | REST API、ステートレスサービス |
statement |
各ステートメント後に接続を解放 | 極端なスケーリング、トランザクション不要 |
最適化デバッグツールキット
-- 現在実行中のクエリ
SELECT pid, now() - pg_stat_activity.query_start AS duration,
query, state, wait_event_type, wait_event
FROM pg_stat_activity
WHERE state != 'idle'
AND pid != pg_backend_pid()
ORDER BY duration DESC;
-- 最も遅いクエリ（pg_stat_statementsが必要）
SELECT queryid, calls,
mean_exec_time::numeric(10,1) AS avg_ms,
total_exec_time::numeric(10,1) AS total_ms,
left(query, 100) AS query_preview
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;
-- 未使用インデックス
SELECT schemaname, tablename, indexname,
idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;
-- キャッシュヒット率（>99%が目標）
SELECT sum(heap_blks_read) AS heap_read,
sum(heap_blks_hit) AS heap_hit,
round(100.0 * sum(heap_blks_hit) /
NULLIF(sum(heap_blks_hit) + sum(heap_blks_read), 0), 1) AS cache_hit_ratio
FROM pg_statio_user_tables;
本番設定クイックリファレンス
# postgresql.conf — 16GB/8コアサーバー向け変更
shared_buffers = 4GB # RAMの25%
effective_cache_size = 12GB # RAMの75%
work_mem = 64MB
maintenance_work_mem = 512MB
random_page_cost = 1.1 # SSD用（デフォルト4.0はHDD想定）
effective_io_concurrency = 200
default_statistics_target = 500
max_parallel_workers_per_gather = 4
max_parallel_workers = 8
max_wal_size = 8GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
まとめ：PostgreSQLクエリ最適化はサイクルだ：
EXPLAIN ANALYZEで測定 → ボトルネックを特定（ディスクI/O、行数推定のずれ、インデックス不足、統計情報の古さ）→ 最小限の修正を適用 → 再度測定。影響度順の最適化：適切なインデックスを追加 → N+1クエリを修正 → 統計情報を更新 → 大規模テーブルをパーティション → 接続プーリングを設定 → サーバーパラメータをチューニング。すべての最適化は、前後のEXPLAIN ANALYZE (BUFFERS)出力で証明すること。
オンラインツール
- JSONフォーマッター — パフォーマンスデバッグ中にJSONクエリ結果を検査
- ハッシュ計算機 — B-treeが適合しない大規模テキスト列のハッシュインデックス値を生成
ブラウザローカルツールを無料で試す →
#PostgreSQL#查询优化#EXPLAIN#索引#性能#数据库#2026