【実務・中級編】複合インデックスの設計 – PostgreSQL

PostgreSQLの「複合インデックス」でハマらないための鉄則：順序がすべてを決める

やあ。最近、コードレビューをしていて「あ、ここはインデックスの張り方を少し変えるだけで、クエリの実行速度が数倍から数十倍変わるのにな」と思うことがよくあるんだ。

PostgreSQLを触っていると、インデックスは「とりあえず貼れば速くなる」と思われがちだけど、実は魔法じゃない。特に「複合インデックス（複数のカラムを組み合わせたインデックス）」は、設計次第で救世主にもなれば、ただのストレージの無駄遣いにもなる。

今日は、現場で後輩によく話している「複合インデックスを設計する時のコツ」を、少し噛み砕いて解説するよ。

—

1. なぜ「カラムの順序」が命なのか？

まず大前提として、PostgreSQLのB-treeインデックスは、「左端一致（Leftmost Prefix）」というルールで動いている。

例えば、`users`テーブルに対して `(status, created_at)` という複合インデックスを貼ったとするよね。このとき、PostgreSQLはインデックスを「まず`status`でソートし、その中で`created_at`をソートする」という順序で保持しているんだ。

これがどういう意味を持つかというと：

効くクエリ: `WHERE status = ‘active’ AND created_at > ‘2023-01-01’`
`status`で絞り込んでから`created_at`を見るから、爆速。
効くクエリ: `WHERE status = ‘active’`
左端の`status`が使われているから、これでもインデックスは効く。
効かない（効きにくい）クエリ: `WHERE created_at > ‘2023-01-01’`
ここが重要！ 左端の`status`がないと、インデックスの「最初の入り口」が見つからないから、フルスキャン（全件走査）に近い動きになってしまうんだ。

つまり、「頻繁に検索条件として使うカラム」を、インデックスの左側に持ってくるのが鉄則。これが逆転していると、インデックスの恩恵をほとんど受けられないこともあるんだ。

—

2. 実践的な設計例：どっちを左にすべき？

よくある「会員一覧画面」のクエリを例に考えてみよう。

SELECT FROM users
WHERE status = ‘active’
AND category_id = 5
ORDER BY created_at DESC;

さて、ここで `(status, category_id, created_at)` という複合インデックスを貼るべきか、それとも `(category_id, status, created_at)` がいいか。判断基準は「カーディナリティ（値の多様性）」と「等価比較か範囲比較か」だ。

迷ったらこの基準で選べ

1. 等価比較（＝）を左へ: `status = ‘active’` のように「＝」で絞り込めるカラムを左に置く。範囲指定（`>`や`<`など）を左に置くと、それ以降の条件がインデックスで絞り込まれにくくなるからだ。 2. 選択性が高いものを左へ: 全体のレコードに対して、その値でどれくらい絞り込めるか。例えば「性別（男/女）」よりも「会員ID」の方が絞り込めるよね。絞り込みの強いカラムを左に置くと、インデックスの探索範囲がグッと小さくなる。

今回の例なら、`status`（値が少ない）よりも `category_id`（値が多い）の方が絞り込み効果が高いことが多い。だから、`(category_id, status, created_at)` とした方が、統計情報的にも効率が良いケースが多いんだ。

—

3. 「複合インデックス」vs「単一インデックス」の迷い

「単一インデックスを3つ貼るのと、複合インデックスを1つ貼るのと、どっちがいいの？」という質問もよく受ける。

結論から言うと、「検索条件を組み合わせて使うことが多いなら、間違いなく複合インデックス」だ。

単一インデックスを複数貼っても、PostgreSQLは「Bitmap Index Scan」を使って複数のインデックスをマージしようとするんだけど、これにはCPUコストがかかるし、結局データ本体へのアクセス（Heap Fetch）回数が増えて遅くなることが多い。

ただし、注意点もある：
複合インデックスは、それ単体でサイズが肥大化しやすい。更新頻度が高いテーブルに巨大な複合インデックスを貼りまくると、今度は`INSERT`や`UPDATE`のたびにインデックスの更新コストが発生して、書き込みが重くなる。

読み取り重視のテーブル: 複合インデックスを積極的に活用する。
書き込み重視のテーブル: インデックスは必要最小限に絞る。

このバランスを忘れないでほしい。

—

先輩からのアドバイス：まずはEXPLAINしてみよう

最後になるけど、理屈で考えるのも大事だけど、最後は必ず `EXPLAIN (ANALYZE, BUFFERS)` を見てほしい。

EXPLAIN (ANALYZE, BUFFERS)
SELECT FROM users WHERE status = ‘active’ AND category_id = 5;

ここで `Index Scan` が使われているか、それとも `Seq Scan` になっているか。`Buffers` の数字が大きすぎていないか。これらを確認する習慣をつけるだけで、君のSQLチューニングレベルは一気に上がるはずだよ。

インデックス設計に「これが絶対の正解」というものはない。データ量やアクセスの偏りによっても変わるからね。まずは手を動かして、PostgreSQLがどう動いているのかを覗いてみることから始めてみて。

また何か詰まったら、いつでも聞きに来てよ。エンジニア同士、一緒に成長していこう。