開発・技術選定

効かないインデックスを作らない — PostgreSQL のインデックス設計

· by IT.Skill

「遅いからインデックスを付けた。でも速くならない」。
よくある状況です。インデックスが使われる条件を押さえると解決します。

まず EXPLAIN で確認する

推測せず、実行計画を見ます。

EXPLAIN ANALYZE
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC LIMIT 20;

見るのは1行目です。

Seq Scan on orders  (cost=... rows=120000 ...)   ← 全件走査（遅い）
Index Scan using idx_orders_status ...           ← インデックス利用（速い）

Seq Scan が出ていたら、インデックスが使われていません。

使われない典型的な原因

1. 列を加工している

WHERE DATE(created_at) = '2021-04-01'     -- 使われない
WHERE created_at >= '2021-04-01'
  AND created_at <  '2021-04-02'          -- 使われる

列に関数をかけると、インデックスは効きません。範囲で書き直します。

2. 前方一致以外の LIKE

WHERE name LIKE '%山田%'    -- 使われない
WHERE name LIKE '山田%'     -- 使われる

中間一致が必要なら、全文検索用の仕組み（GIN + pg_trgm）が要ります。

3. 複合インデックスの順序が違う

CREATE INDEX idx ON orders (status, created_at);

このインデックスは次で使えます。

WHERE status = ?                          ✓
WHERE status = ? ORDER BY created_at      ✓
WHERE created_at > ?                      ✗（先頭列が無い）

左端から順に使うのが原則です。
絞り込みに使う列を先、並び替えに使う列を後にします。

4. 選択率が悪い

is_active = true が全体の95%を占めるような列は、
インデックスを使うより全件読んだほうが速いと判断されます。これは正しい挙動です。

こういう場合は部分インデックスが効きます。

CREATE INDEX idx_inactive ON users (id) WHERE is_active = false;

Django での書き方

class Meta:
    indexes = [
        models.Index(fields=["status", "-created_at"]),
        models.Index(fields=["email"], name="idx_email_active",
                     condition=Q(is_active=True)),
    ]

付けすぎない

インデックスは書き込みを遅くします。
INSERT/UPDATE のたびに全インデックスの更新が発生するためです。

使われていないものは定期的に落とします。

SELECT indexrelname, idx_scan FROM pg_stat_user_indexes
WHERE idx_scan = 0 ORDER BY relname;

idx_scan = 0 は一度も使われていないインデックスです。

まとめ

  • まず EXPLAIN ANALYZE。Seq Scan かどうかを見る
  • 列を加工しない、中間一致 LIKE を避ける
  • 複合インデックスは「絞り込み列 → 並び替え列」の順
  • 偏った列には部分インデックス
  • 使われていないインデックスは削除する

関連記事