PostgreSQLのインデックス戦略：遅いクエリを撃退する

インデックスの種類

B-tree（デフォルト）

最も一般的なインデックス。等値検索・範囲検索・ソートに使えます。

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

-- 範囲検索にも有効
SELECT * FROM orders
WHERE created_at BETWEEN '2026-01-01' AND '2026-03-31';

GIN — 配列・JSON・全文検索

-- JSON カラムの検索
CREATE INDEX idx_products_tags ON products USING GIN(tags);

-- クエリ
SELECT * FROM products WHERE tags @> '["typescript"]';

-- 全文検索
CREATE INDEX idx_articles_fts ON articles
  USING GIN(to_tsvector('japanese', body));

SELECT * FROM articles
WHERE to_tsvector('japanese', body) @@ plainto_tsquery('japanese', 'TypeScript 型');

GiST — 地理情報・範囲型

-- PostGIS と組み合わせた位置検索
CREATE INDEX idx_stores_location ON stores USING GIST(location);

SELECT * FROM stores
WHERE ST_DWithin(location, ST_MakePoint(139.7, 35.6), 1000); -- 1km 圏内

BRIN — タイムスタンプの大量データ

-- ログテーブルなど時系列で追記されるテーブルに向く（サイズが小さい）
CREATE INDEX idx_logs_created ON logs USING BRIN(created_at);

EXPLAIN ANALYZE の読み方

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

Seq Scan on orders  (cost=0.00..8432.00 rows=12 width=89)
                    (actual time=0.041..45.231 rows=12 loops=1)
  Filter: ((user_id = 100) AND (status = 'pending'))
  Rows Removed by Filter: 99988

ポイント:

Seq Scan = テーブル全体をスキャン → インデックスが効いていない
Rows Removed by Filter が多い → インデックスを追加するサイン

インデックスを追加後：

Index Scan using idx_orders_user_status on orders
  (cost=0.43..8.47 rows=12 width=89)
  (actual time=0.018..0.031 rows=12 loops=1)

Index Scan に変わり、実行時間が劇的に改善。

部分インデックス — 条件付きインデックス

-- アクティブユーザーのみインデックス
CREATE INDEX idx_users_active_email
  ON users(email)
  WHERE is_active = true;

-- 未処理の注文のみ（大半がcompleted なら効果大）
CREATE INDEX idx_orders_pending
  ON orders(created_at)
  WHERE status = 'pending';

インデックスサイズを小さく保ちつつ、対象クエリを高速化できます。

複合インデックスの列順

-- ❌ 誤った順序（status の絞り込みが先でも user_id が活用されない）
CREATE INDEX idx_wrong ON orders(status, user_id);

-- ✅ 正しい順序（等値条件 → 範囲条件の順）
CREATE INDEX idx_correct ON orders(user_id, status, created_at);

-- このクエリに最適
SELECT * FROM orders
WHERE user_id = 100        -- 等値
  AND status = 'pending'   -- 等値
  AND created_at > '2026-01-01'; -- 範囲

N+1 問題の検出

-- pg_stat_statements で頻度の高いクエリを見つける
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;

N+1 の典型パターン：

-- 同じクエリが users 数だけ実行される
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 2;
SELECT * FROM orders WHERE user_id = 3;
-- ...

-- 対策: JOIN または IN で一括取得
SELECT o.* FROM orders o
WHERE o.user_id IN (1, 2, 3);

インデックスのメンテナンス

-- 肥大化したインデックスを再構築（ロックあり）
REINDEX INDEX idx_users_email;

-- ロックなしで再構築（推奨）
REINDEX INDEX CONCURRENTLY idx_users_email;

-- 未使用インデックスを確認
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY schemaname, tablename;

まとめ

まず EXPLAIN ANALYZE で Seq Scan を見つける
等値 + 範囲の順で複合インデックスを設計する
部分インデックスで特定条件のクエリを高速化
pg_stat_statements で頻繁に実行されるクエリを特定
定期的に REINDEX CONCURRENTLY でメンテナンス

参考: PostgreSQL 公式ドキュメント - インデックス

S	公式ソース確認済み
A	成功実績多数・失敗例少数
B	賛否両論
C	動作未確認・セキュリティリスク高
Z	個人所感