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

複合インデックスの「順序」という名の魔術――PostgreSQLの深淵を覗く

PostgreSQLのチューニングにおいて、インデックス設計はまさに「職人芸」です。特に複合インデックス（B-tree）の列順序をどう定義するかは、パフォーマンスを劇的に左右する境界線であり、ここを理解しているかどうかで、クエリの実行計画（EXPLAIN ANALYZE）から読み取れる情報の解像度がまるで変わってきます。

今日は、教科書的な「左端の列が重要だよね」という話を少し掘り下げて、PostgreSQLの内部構造から、なぜその順序がクエリプランナーを支配するのかを紐解いてみましょう。

1. 「等価」と「範囲」の境界線を見極める

複合インデックスの設計で最も基本的な原則は、「等価比較（=）を左側に、範囲比較（>、<、BETWEEN）を右側に寄せる」というものです。なぜこれが重要か？それはB-treeの構造に起因します。PostgreSQLのB-treeは、インデックスの各ノードがソートされた状態で維持されています。左端の列から順に比較を行い、条件が確定した時点で探索範囲を絞り込みますが、範囲条件にぶつかった瞬間、そこから先の列によるインデックスの絞り込み能力は著しく低下します。

例えば、`(status, created_at)` という複合インデックスがある場合：

`WHERE status = ‘active’ AND created_at > ‘2023-01-01’` は、インデックスの連続する領域を効率的にスキャンできます。
しかし、`WHERE created_at > ‘2023-01-01’ AND status = ‘active’` の場合、プランナーはインデックス内の広範囲を走査せざるを得ません。

ここで重要なのは、「インデックスの選択性（Cardinality）」です。よりユニークな値を持つ列を左に配置することで、探索の枝刈り（Pruning）が強力に働きます。これは直感的ですが、インデックスのサイズ（ページ数）を最小化し、メモリ上のヒット率を最大化するための鉄則です。

2. ソート順序の「タダ乗り」を狙う

インデックスを貼る目的はフィルタリングだけではありません。実は、`ORDER BY` 句の最適化において、複合インデックスの順序は決定的な役割を果たします。

もしクエリが `WHERE col1 = ? ORDER BY col2` を実行するなら、`(col1, col2)` というインデックスがあれば、PostgreSQLはソート処理（Sortノード）を完全にスキップできます。これは `Top-N` クエリにおいて劇的な差を生みます。

ここで注意すべきは、`ORDER BY` の方向です。PostgreSQL 8.0以降では `DESC` インデックスもサポートされていますが、もしインデックスが昇順（ASC）で作成されている場合、`ORDER BY col1 DESC, col2 ASC` のような混在したソート順にはインデックスをフル活用できません。

実務レベルでのトラブルシュートとして、「なぜかインデックスを使わずFilesortが発生している」と悩んだときは、インデックスの定義とORDER BYの方向性が一致しているか、一度確認してみてください。

3. 「インデックスオンリースキャン」の罠

時折、「必要な列をすべて複合インデックスに入れてしまえば爆速になるのでは？」と考えて、列を詰め込みすぎるケースを見かけます。しかし、それは「インデックスの肥大化」と「更新負荷（Write Amplification）」という別の地獄を招きます。

PostgreSQLのインデックスオンリースキャン（IOS）は強力ですが、可視性マップ（Visibility Map）を確認し、データが実際にHEAPにアクセスせずに取得できるかどうかが鍵です。インデックスを広くしすぎると、インデックスのページ数が増え、バッファキャッシュの浪費につながります。

高頻度で更新される列を複合インデックスの左側に置かない：これはインデックスのフラグメンテーションを加速させ、`VACUUM` の負荷を無駄に高めます。
列の順序を最適化し、クエリの実行計画を「Index Scan」に固定させる：これがチューニングの終着点です。

最後に：エンジニアの直感と計測のバランス

結局のところ、インデックス設計に「唯一の正解」はありません。アプリケーションのクエリパターンは進化し続けます。

私が現場で大切にしているのは、「プランナーがどのような意図でそのインデックスを選んだか（あるいは選ばなかったか）」を読み解くことです。`EXPLAIN (ANALYZE, BUFFERS)` を実行し、どのノードでバッファが消費されているかを見てください。

技術は常に魔法ではなく、論理の積み重ねです。インデックスの列順序一つで、サーバーのCPU使用率が10％下がることもある。その瞬間の快感を知っている皆さんと、これからも深い技術の森を歩んでいけたらと思います。

それでは、また次回の記事で。Happy Querying!