複合インデックスの「順序」という名の魔物：PostgreSQLのB-treeと左端一致の深淵
PostgreSQLのパフォーマンスチューニングにおいて、インデックス設計はまさに職人芸です。「とりあえずこの列とこの列にインデックスを貼っておけばいいだろう」という安易なアプローチが、数ヶ月後に数百万行のテーブルで悲鳴を上げ始める。そんな光景を、私は何度見てきたことか。
今日は、複合インデックス（Composite Index）の設計における最大の落とし穴、「列の順序」と「左端一致（Leftmost Prefix）」の原則について、少し深掘りしてみたいと思います。
なぜ「順序」がすべてを決定づけるのか
まず前提として、PostgreSQLの標準的なインデックスであるB-tree構造を思い出してください。B-treeは、インデックスの定義順に従ってソートされた「辞書のようなもの」です。
例えば、`(last_name, first_name)` という複合インデックスを張った場合、データはまず `last_name` で整列され、その中で `first_name` が並びます。この構造があるからこそ、私たちは「`last_name = ‘Sato’` かつ `first_name = ‘Taro’`」という検索で高速なレスポンスを得られるわけです。
ここで重要なのは、「左端が含まれていないインデックスは、実質的に無力である」という冷徹な事実です。
左端一致の原則を無視した代償
もしクエリが `WHERE first_name = ‘Taro’` だけを条件にしていたらどうなるか。B-treeは `last_name` の先頭から順に走査を始めなければなりません。`first_name` は `last_name` の中でバラバラに散らばっているため、インデックス全体をスキャンする `Index Full Scan` が走り、最悪の場合は `Sequential Scan` と変わらないコストを支払うことになります。
「条件に含めている列があるから大丈夫」という思い込みが、実行計画を歪める最大の原因です。
Cardinality（カーディナリティ）の幻想を捨てる
よく「カーディナリティが高い列を左に置くべきだ」という定説を聞きます。確かに、絞り込み効率を考えれば論理的ですが、それは半分正解で半分は罠です。
真に意識すべきは、「その列が等価条件（`=`）で使われるのか、範囲条件（`>` や `<`）で使われるのか」という点です。
- 等価条件の列を左へ: `WHERE status = ‘active’ AND created_at > ‘2023-01-01’` のようなクエリであれば、`(status, created_at)` と定義するのが定石です。`status` でバッサリと範囲を絞り込み、その中で `created_at` のツリーを辿れるからです。
- 範囲条件の列を右へ: 逆に、範囲条件を左に置いてしまうと、その後の列はインデックスとしての検索効率を失います。PostgreSQLのB-treeは、最初の範囲条件にヒットした部分以降を走査する必要があるため、それ以降の列は「ソート順を維持するためだけ」の存在に成り下がってしまうのです。
パフォーマンストラブルシューティングの現場から
私が現場でよく行う「インデックスの棚卸し」では、`pg_stat_user_indexes` を必ず確認します。
SELECT relname, indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
WHERE schemaname = ‘public’;
ここで `idx_scan` は多いのに `idx_tup_fetch` が極端に少ないインデックスを見つけたら、それは「インデックスが機能していない証拠」です。多くの場合、複合インデックスの順序がクエリのWHERE句の構成と噛み合っていないか、あるいは左端一致の原則から外れたクエリが投げられています。
最後に：完璧なインデックスは存在しない
結局のところ、インデックス設計とは「トレードオフの芸術」です。
特定の検索クエリのためにインデックスを最適化すれば、書き込み（UPDATE/INSERT）時のオーバーヘッドは増大します。私が若手エンジニアによく言うのは、「インデックスは、テーブルの持ち物ではなく、クエリのしもべである」ということです。
アプリケーションがどのような検索パターンを必要としているのか。そのクエリの実行頻度は？ レスポンスタイムの許容範囲は？ これらを見極めた上で、`CREATE INDEX` を打つ。その一回一回の判断が、数年後のシステムの健全性を左右します。
皆さんのデータベースは、今日も静かに、かつ効率的に動いていますか？ もし `EXPLAIN ANALYZE` の出力結果が芳しくないなら、一度インデックスの「順序」を見直してみてください。そこに、劇的な改善のヒントが隠されているはずです。
コメント