【テクニカル・上級編】複合インデックスの設計 – PostgreSQL

複合インデックスの「順序」が運命を分ける理由：PostgreSQLのB-tree構造から紐解く最適化戦略

データベースのパフォーマンスチューニングにおいて、インデックス設計はまさに「職人芸」の領域です。特に複合インデックス（Composite Index）の設計は、そのわずかな順序の違いがクエリの実行計画を劇的に変えてしまう。

今日は、PostgreSQLのB-treeインデックスの内部構造を踏まえつつ、なぜ「左端一致の原則」が絶対的なのか、そして、単一インデックスを乱用する前に考えるべき「インデックスの合成」について、現場の知見を共有したいと思います。

—

なぜ「左端一致」は覆せないのか

PostgreSQLの標準であるB-treeインデックスは、データをソートされた状態で保持します。複合インデックスが作成されるとき、PostgreSQLは指定されたカラムの順序に従って「辞書順」にソートされたツリーを構築します。

例えば、`(last_name, first_name)` という複合インデックスがある場合、データはまず `last_name` でソートされ、その範囲内で `first_name` がソートされます。

ここで重要なのは、「左側のカラムが決まらない限り、右側のカラムの順序は保証されない」という物理的な制約です。

`WHERE last_name = ‘Sato’` ：左端が特定されているため、インデックスは効きます。
`WHERE first_name = ‘Taro’` ：左端が不明なため、インデックスのツリーを辿ることができず、Index ScanではなくIndex Scan全体を走査する羽目になります。

内部アーキテクチャの観点で言えば、B-treeのルートノードからリーフノードまで潜る際、左端のキーがないと、検索範囲を絞り込むための「枝分かれ」が判断できないのです。この物理的な制約を無視したインデックス設計は、どんなに強力なサーバーを使っても、いずれクエリの遅延という形で跳ね返ってきます。

—

カラム順序を決める「3つの鉄則」

複合インデックスのカラム順序を決定する際、私は常に以下の優先順位で検討します。

1. 等価性条件（Equality）を左に寄せる

`WHERE status = ‘active’ AND created_at > ‘2023-01-01’` のようなクエリであれば、迷わず `(status, created_at)` とします。等価比較はツリーをピンポイントで絞り込めますが、範囲比較（`>`, `<`など）はその後の絞り込み効率を落とすためです。

2. 選択性（Selectivity）の高いカラムを優先する

もし複数のカラムで等価条件を使うなら、よりユニークな値（選択性が高いもの）を左側に配置してください。例えば `(user_id, status)` と `(status, user_id)` では、`user_id` が特定できるなら前者のほうが圧倒的にツリーの走査範囲が狭まり、メモリ効率も向上します。

3. ソート順（ORDER BY）との整合性

もし `ORDER BY` で頻繁に使われるカラムがあるなら、インデックスの順序をそれに合わせることで、「ソート済みとしてインデックスを読み込む」ことが可能になります。これは `EXPLAIN` 結果から `Sort` ノードを消し去るための最強の武器です。

—

複合か、単一か？「インデックスの過剰」という罠

「とりあえずカラムごとに単一インデックスを貼っておけば、オプティマイザが賢く選んでくれるだろう」――これは、中級者によくある誤解です。

PostgreSQLには Bitmap Index Scan という機能があり、複数の単一インデックスを組み合わせて検索することは可能です。しかし、これはあくまで「複合インデックスが最適でない場合」のバックアップに過ぎません。

単一インデックスの乱用： 書き込み（INSERT/UPDATE）のたびに、インデックスの数だけB-treeの更新コストが発生します。Write-Heavyなワークロードでは、これだけでトランザクションのボトルネックになります。
複合インデックスの賢さ： 適切に設計された複合インデックスは、単一インデックスの役割も兼ねることができます。`(a, b, c)` を持っていれば、`(a)` や `(a, b)` に対するクエリもカバーできるからです（左端一致の法則により）。

私は、「クエリのWHERE句がインデックスのプレフィックスと一致するか」を常に意識します。もし複数のクエリパターンがあるなら、インデックスを増やすのではなく、最も頻度が高く、かつコストのかかるクエリに合わせて複合インデックスを「重畳」させるように設計します。

—

最後に：計測こそが唯一の真実

理論は重要ですが、PostgreSQLのオプティマイザは統計情報を元に動く生き物です。データの分布（カーディナリティ）が変われば、最適なインデックスも変わります。

インデックスを設計したら、必ず `EXPLAIN (ANALYZE, BUFFERS)` を実行してください。特に `Buffers: shared hit` の数値に注目してください。インデックスを最適化することで、ディスクI/Oがどれだけ減ったか。その数字こそが、エンジニアとしての確かな仕事の証です。

インデックス設計に銀の弾丸はありません。あるのは、データとクエリのパターンに対する、地道で情熱的な対話だけです。皆さんのデータベースが、今日も軽快に応答してくれることを願っています。