PostgreSQL パフォーマンスチューニング実践ガイド — クエリ最適化、インデックス戦略、EXPLAIN 分析

はじめに
1. EXPLAIN — クエリ実行計画の読み方
2. インデックス戦略 — 正しいインデックス設計
3. クエリ最適化パターン
4. 設定チューニング — postgresql.conf
5. VACUUM と統計管理
6. 実践トラブルシューティング — 遅いクエリの発見
- pg_stat_statements でスロークエリを発見
- 現在実行中のクエリの確認
7. パフォーマンスチェックリスト
クイズ

はじめに

「クエリが遅い」— バックエンド開発者なら一度は耳にする言葉です。PostgreSQL は強力な RDBMS ですが、適切にチューニングしなければ、データが増えるにつれてパフォーマンスが急激に低下します。この記事では、実務ですぐに適用できる PostgreSQL パフォーマンスチューニング技法を、EXPLAIN 分析からインデックス戦略、クエリリファクタリングまで段階的に解説します。

1. EXPLAIN — クエリ実行計画の読み方

EXPLAIN vs EXPLAIN ANALYZE

-- 予想実行計画（実際には実行しない）
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- 実際に実行 + 実測時間を含む（注意：実際にクエリが実行される）
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- 最も詳細な分析（バッファ使用量を含む）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM users WHERE email = 'test@example.com';

EXPLAIN 出力の読み方

EXPLAIN ANALYZE
SELECT u.name, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.created_at > '2026-01-01';

Hash Join  (cost=1250.00..5680.50 rows=15000 width=48)
           (actual time=12.5..89.3 rows=14800 loops=1)
  Hash Cond: (o.user_id = u.id)
  -> Seq Scan on orders o  (cost=0.00..3500.00 rows=15000 width=16)
                           (actual time=0.02..45.1 rows=14800 loops=1)
       Filter: (created_at > '2026-01-01')
       Rows Removed by Filter: 85200
  -> Hash  (cost=1000.00..1000.00 rows=50000 width=36)
           (actual time=12.3..12.3 rows=50000 loops=1)
       -> Seq Scan on users u  (cost=0.00..1000.00 rows=50000 width=36)
                               (actual time=0.01..6.8 rows=50000 loops=1)
Planning Time: 0.25 ms
Execution Time: 95.8 ms

主要指標の解釈

指標	意味	注意点
cost	予想コスト (startup..total)	相対的な単位で、絶対値ではない
rows	予想行数	actual と大きく異なる場合は統計更新が必要
actual time	実際の所要時間 (ms)	startup..total
loops	繰り返し回数	actual time x loops = 実際の総時間
Rows Removed by Filter	フィルタで除去された行数	この数値が大きい場合はインデックスが必要
Buffers: shared hit/read	キャッシュヒット/ディスク読み取り	read が大きい場合はメモリ不足

危険信号

# 大容量テーブルの Seq Scan → インデックスが必要
Seq Scan on orders  (rows=1000000)

# 大量 rows の Nested Loop → Join 方式の変更が必要
Nested Loop  (actual loops=50000)

# 外部ディスクを使用した Sort → work_mem 不足
Sort Method: external merge  Disk: 128000kB

# 予想 rows と実際 rows の差異 → ANALYZE が必要
(rows=100) ... (actual rows=50000)

2. インデックス戦略 — 正しいインデックス設計

B-Tree インデックス（デフォルト）

-- 単一カラムインデックス
CREATE INDEX idx_users_email ON users(email);

-- 複合インデックス（順序が重要！）
CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at DESC);

-- 部分インデックス（条件付きインデックス）
CREATE INDEX idx_orders_active
ON orders(user_id)
WHERE status = 'active';

-- ユニークインデックス
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

複合インデックスのカラム順序

複合インデックスにおけるカラム順序はパフォーマンスに決定的な影響を与えます：

-- インデックス: (user_id, created_at, status)

-- インデックス活用可能（左から順にマッチ）
WHERE user_id = 1
WHERE user_id = 1 AND created_at > '2026-01-01'
WHERE user_id = 1 AND created_at > '2026-01-01' AND status = 'active'

-- インデックス活用不可（途中のカラムをスキップ）
WHERE user_id = 1 AND status = 'active'  -- created_at をスキップ
WHERE created_at > '2026-01-01'          -- user_id がない
WHERE status = 'active'                  -- 先頭カラムがない

原則: 等号（=）条件のカラムを前に、範囲（より大きい、より小さい、BETWEEN）条件を後ろに配置します。

GIN インデックス（全文検索、JSONB、配列）

-- JSONB フィールドインデックス
CREATE INDEX idx_products_metadata
ON products USING GIN(metadata);

-- JSONB 検索が高速に
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';

-- 配列インデックス
CREATE INDEX idx_posts_tags
ON posts USING GIN(tags);

SELECT * FROM posts WHERE tags @> ARRAY['postgresql'];

インデックスが使用されないケース

-- 関数適用時にインデックスが無視される
SELECT * FROM users WHERE LOWER(email) = 'test@example.com';
-- 関数インデックスを作成
CREATE INDEX idx_users_email_lower ON users(LOWER(email));

-- 型の不一致
SELECT * FROM users WHERE id = '123';  -- id は integer なのに文字列比較
-- 正しい型
SELECT * FROM users WHERE id = 123;

-- LIKE の先頭ワイルドカード
SELECT * FROM users WHERE name LIKE '%kim%';  -- Full Scan
-- 先頭固定
SELECT * FROM users WHERE name LIKE 'kim%';  -- Index 使用可能

-- OR 条件
SELECT * FROM users WHERE email = 'a@b.com' OR name = 'Kim';
-- UNION で分離
SELECT * FROM users WHERE email = 'a@b.com'
UNION ALL
SELECT * FROM users WHERE name = 'Kim';

インデックス使用状況のモニタリング

-- 使用されていないインデックスを見つける
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;

-- テーブル別インデックスサイズ
SELECT tablename,
       pg_size_pretty(pg_total_relation_size(tablename::regclass)) as total_size,
       pg_size_pretty(pg_indexes_size(tablename::regclass)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(tablename::regclass) DESC;

3. クエリ最適化パターン

N+1 クエリの排除

-- N+1 パターン（ORM でよく発生）
-- 1回目: SELECT * FROM users LIMIT 100;
-- 100回: SELECT * FROM orders WHERE user_id = ?;

-- JOIN で一度に取得
SELECT u.name, o.total, o.created_at
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2026-01-01'
LIMIT 100;

-- またはサブクエリ + IN
SELECT * FROM orders
WHERE user_id IN (
    SELECT id FROM users WHERE created_at > '2026-01-01'
);

ページネーション最適化

-- OFFSET 方式（ページが深くなるほど遅くなる）
SELECT * FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20;

-- Keyset Pagination（カーソルベース）
SELECT * FROM orders
WHERE id < 900000  -- 前のページの最後の id
ORDER BY id DESC
LIMIT 20;

-- カバリングインデックス + サブクエリ
SELECT o.* FROM orders o
JOIN (
    SELECT id FROM orders ORDER BY id DESC OFFSET 100000 LIMIT 20
) sub ON o.id = sub.id;

EXISTS vs IN

-- 大容量では EXISTS がより効率的（相関サブクエリ）
-- EXISTS
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o
    WHERE o.user_id = u.id AND o.total > 10000
);

-- IN（サブクエリ結果が大きい場合）
SELECT * FROM users
WHERE id IN (
    SELECT user_id FROM orders WHERE total > 10000
);

COUNT 最適化

-- 正確な COUNT（Full Scan が発生）
SELECT COUNT(*) FROM orders;

-- 近似値で十分な場合
SELECT reltuples::bigint AS estimate
FROM pg_class WHERE relname = 'orders';

-- 条件付き COUNT 最適化
-- インデックスがあれば Index Only Scan 可能
SELECT COUNT(*) FROM orders WHERE status = 'completed';
-- 必要: CREATE INDEX idx_orders_status ON orders(status);

CTE vs サブクエリ

-- PostgreSQL 12+ では CTE はデフォルトでインライン化される
-- MATERIALIZED ヒントで強制物理化が可能

-- 自動インライン（オプティマイザが最適化）
WITH active_users AS (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users WHERE name LIKE 'K%';

-- 強制物理化（重複実行を防止）
WITH active_users AS MATERIALIZED (
    SELECT id, name FROM users WHERE status = 'active'
)
SELECT * FROM active_users au
JOIN orders o ON au.id = o.user_id;

4. 設定チューニング — postgresql.conf

メモリ関連

# shared_buffers: 全体 RAM の 25%（最も重要！）
# 16GB RAM 基準
shared_buffers = 4GB

# effective_cache_size: OS キャッシュを含む予想メモリ（RAM の 75%）
effective_cache_size = 12GB

# work_mem: ソート/ハッシュ操作メモリ（セッション × クエリごと）
# 注意: コネクション数 × work_mem 分のメモリを使用する可能性あり
work_mem = 256MB

# maintenance_work_mem: VACUUM, CREATE INDEX 時に使用
maintenance_work_mem = 1GB

WAL およびチェックポイント

# WAL サイズ（書き込みの多いワークロード）
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB

# チェックポイント間隔
checkpoint_completion_target = 0.9

クエリプランナー

# 並列クエリの有効化
max_parallel_workers_per_gather = 4
max_parallel_workers = 8

# ランダム I/O コスト（SSD は低く）
random_page_cost = 1.1  # HDD: 4.0, SSD: 1.1

# 統計収集の精度
default_statistics_target = 200  # デフォルト 100

5. VACUUM と統計管理

VACUUM が必要な理由

PostgreSQL は MVCC（Multi-Version Concurrency Control）を使用します。UPDATE/DELETE 時に既存の行を即座に削除せず、「dead tuple」として残します。VACUUM がこれを整理します。

-- テーブル別 dead tuple 確認
SELECT schemaname, relname,
       n_live_tup, n_dead_tup,
       ROUND(n_dead_tup::numeric / NULLIF(n_live_tup, 0) * 100, 2) as dead_ratio
FROM pg_stat_user_tables
WHERE n_dead_tup > 1000
ORDER BY n_dead_tup DESC;

-- 手動 VACUUM（通常は autovacuum が処理）
VACUUM ANALYZE orders;

-- VACUUM FULL（テーブルロック注意！ — ディスク領域回収）
VACUUM FULL orders;  -- 本番環境での使用禁止！

Autovacuum チューニング

# autovacuum デフォルト設定
autovacuum = on
autovacuum_max_workers = 5

# 大容量テーブル用のテーブル別設定
ALTER TABLE orders SET (
    autovacuum_vacuum_threshold = 1000,
    autovacuum_vacuum_scale_factor = 0.01,  -- 1%が dead tuple なら実行
    autovacuum_analyze_threshold = 500,
    autovacuum_analyze_scale_factor = 0.005
);

統計の更新

-- EXPLAIN の予想 rows が実際と大きく異なる場合
ANALYZE orders;

-- DB 全体の統計更新
ANALYZE;

-- 特定カラムの統計精度を上げる
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 500;
ANALYZE orders;

6. 実践トラブルシューティング — 遅いクエリの発見

pg_stat_statements でスロークエリを発見

-- 拡張の有効化
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- 最も遅いクエリ TOP 10
SELECT query,
       calls,
       ROUND(total_exec_time::numeric, 2) as total_time_ms,
       ROUND(mean_exec_time::numeric, 2) as avg_time_ms,
       rows
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;

-- 最も頻繁に呼び出されるクエリ（総時間基準）
SELECT query, calls,
       ROUND(total_exec_time::numeric, 2) as total_ms
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

現在実行中のクエリの確認

-- 長時間実行中のクエリ
SELECT pid, now() - pg_stat_activity.query_start AS duration,
       query, state
FROM pg_stat_activity
WHERE state != 'idle'
  AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY duration DESC;

-- ロック待ちのクエリ
SELECT blocked.pid AS blocked_pid,
       blocked.query AS blocked_query,
       blocking.pid AS blocking_pid,
       blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_locks bl ON bl.pid = blocked.pid
JOIN pg_locks kl ON kl.locktype = bl.locktype
  AND kl.relation = bl.relation
  AND kl.pid != bl.pid
JOIN pg_stat_activity blocking ON blocking.pid = kl.pid
WHERE NOT bl.granted;

7. パフォーマンスチェックリスト

本番運用前に必ず確認すべき項目：

EXPLAIN ANALYZE で主要クエリの実行計画を確認
Seq Scan が大容量テーブルで発生していないか確認
複合インデックスのカラム順序がクエリパターンに合っているか確認
使用されていないインデックスの整理（書き込みパフォーマンス低下を防止）
pg_stat_statements でスロークエリモニタリングを設定
Autovacuum が正常に動作しているか確認
shared_buffers、work_mem などのメモリ設定を検討
コネクションプーリング（PgBouncer 等）の適用を確認
定期的な ANALYZE 実行で統計を最新化

クイズ

Q1: EXPLAIN と EXPLAIN ANALYZE の違いは？

EXPLAIN は予想実行計画のみを表示し、EXPLAIN ANALYZE は実際にクエリを実行して実測時間と行数を一緒に表示します。ANALYZE は実際に実行されるため、INSERT/UPDATE/DELETE には注意が必要です。

Q2: 複合インデックス (a, b, c) で WHERE a = 1 AND c = 3 のクエリがインデックスを完全に活用できない理由は？

B-Tree 複合インデックスは左から順にマッチします。途中のカラム（b）をスキップすると、c カラムのインデックスを活用できません。a カラムのみがインデックスを使用します。

Q3: OFFSET ベースのページネーションが深いページで遅い理由は？

OFFSET N は N 行を読み込んで破棄する方式です。OFFSET 100000 なら 100,000 行を読んだ後に破棄し、その次の行から返すため、ページが深くなるほど読む行が増えます。

Q4: random_page_cost を SSD で 1.1 に下げる理由は？

SSD はランダム I/O とシーケンシャル I/O のパフォーマンス差がほとんどありません。デフォルト値 4.0 は HDD 基準なので、SSD では 1.1 に下げてプランナーがインデックススキャンをより積極的に選択するように誘導します。

Q5: PostgreSQL で dead tuple が発生する理由は？

MVCC アーキテクチャのためです。UPDATE/DELETE 時に既存の行を即座に削除せず、他のトランザクションが参照できるように「dead tuple」として残します。VACUUM がこれを整理します。

Q6: WHERE LOWER(email) = 'test@example.com' がインデックスを使えない場合の解決法は？

関数インデックスを作成します: CREATE INDEX idx_users_email_lower ON users(LOWER(email));

Q7: shared_buffers の推奨設定値は？

システム全体の RAM の約 25% です。例: 16GB RAM → shared_buffers = 4GB。

Q8: EXPLAIN 出力で「Rows Removed by Filter」の数値が非常に大きい場合の意味は？

テーブルから多くの行を読み込んだ後、フィルタで大部分を破棄していることを意味します。適切なインデックスを追加すれば、不要な行の読み込みを減らすことができます。

Q9: VACUUM FULL を本番環境で使うべきでない理由は？

VACUUM FULL はテーブルに対して ACCESS EXCLUSIVE ロックをかけるため、作業が完了するまでそのテーブルへのすべての読み書きがブロックされます。

Q10: EXISTS と IN のうち、大容量サブクエリでより効率的なのは？

一般的に EXISTS がより効率的です。EXISTS は最初のマッチング行を見つけると即座に中断しますが、 IN はサブクエリの全結果を先に生成する必要がある場合があります。