【テクニカル・上級編】 B-treeインデックスの最適化 – PostgreSQL

B-treeの深淵：PostgreSQLインデックスを「性能」で語るために

PostgreSQLを触り始めてしばらく経つと、誰もが一度は `CREATE INDEX` を叩くはずです。でも、実務で数億レコードのテーブルを相手にしていると、インデックスは単なる「検索を速くする魔法の杖」ではなく、維持コストと読み取り効率のバランスを競う「繊細なチューニング対象」であることが分かってきます。

今日は、PostgreSQLの屋台骨であるB-treeインデックスについて、マニュアルの行間にある「現場のリアリティ」を少し掘り下げてみましょう。

—

1. B-treeの「深さ」とIOコストの切実な関係

B-treeの構造について語るとき、多くの人は「平衡木である」ことだけを意識しがちです。しかし、データベースエンジニアが真に恐れるべきは、インデックスの「高さ（Level）」です。

PostgreSQLのB-treeは、ルートページからリーフページまで、木が深くなるほどIO回数が増えます。通常、3階層程度であれば問題になりませんが、巨大なテーブルでインデックスが肥大化し、階層が深まると、たった1行を探すためだけに複数のランダムIOが発生します。

パフォーマンストラブルの予兆: `pg_stat_user_indexes` でインデックスのサイズを確認する癖をつけましょう。
インデックスの断片化: `VACUUM` が適切に走っていない環境では、インデックスページ内に空き領域（デッドタプル）が蓄積され、木が不必要に肥大化します。これは「論理的な深さ」以上に、物理的なIO負荷を増大させる犯人です。

2. 複合インデックスの「列順序」は、ただのソートではない

複合インデックス（Multi-column Index）を設計する際、「左から右へ」という基本ルールは誰もが知っています。しかし、現場で重要なのは「カーディナリティ（選択性）」と「フィルタリング」のバランスです。

よくある失敗は、単に「よく使う列」を並べること。私が設計で意識しているのは以下の原則です。

等価比較（`=`）を先頭に: 等価条件で絞り込める列を先に置くことで、スキャン対象を劇的に絞り込めます。
範囲検索（`<`, `>`, `BETWEEN`）は最後尾に: 範囲検索が入った列以降のインデックスは、実質的に絞り込み能力が大幅に低下します。
ソート順の考慮: もし `ORDER BY` 句が固定されているなら、インデックスの順序と合わせることで、クエリ実行時の `Sort` ノード（メモリ消費の激しい処理）を完全に排除できる可能性があります。

3. インデックス・オンリー・スキャン（IOS）という究極の選択

性能を突き詰めると、最終的にたどり着くのは「テーブル本体を読ませない」という設計思想です。これが Index Only Scan です。

PostgreSQLはMVCC（多版同時実行制御）を採用しているため、インデックスからデータを取り出す際にも、その行が「可視かどうか」をヒープ（テーブル本体）まで見に行く必要があります。これが有名な `Heap Fetches` です。

HOT (Heap Only Tuple) との兼ね合い: インデックスが更新されないような設計や、fillfactorの調整を行うことで、インデックスの性能を最大化できます。
Covering Index: `INCLUDE` 句を使って、検索条件には含まれないが結果として必要な列をインデックスの末尾（リーフページ）に持たせる手法は、IOSを誘発させるための強力な武器です。

—

現場で役立つチューニングの心得

最後に、私が日々の運用で大切にしている視点を共有します。

1. 「とりあえず貼る」は最大の悪手: インデックスは書き込み性能を確実に削ります。`pg_stat_user_indexes` の `idx_scan` を見て、全く使われていないインデックスは迷わず削除する勇気が必要です。
2. EXPLAIN ANALYZE を疑え: 実行計画は「予測」であり、絶対ではありません。特に巨大なテーブルでは、統計情報が古いせいで全スキャンが選ばれるケースが多々あります。`ANALYZE` のタイミングと、統計情報の精度のバランスを常に意識してください。
3. インデックスの「広さ」と「深さ」を直感する: 1列のインデックスと、5列の複合インデックス。後者はページサイズを圧迫し、バッファキャッシュの効率を下げます。常に「何のためにこのインデックスがあるのか」を一行で説明できるようにしておくこと。

インデックスのチューニングは、パズルに近い面白さがあります。カーネルの挙動を想像し、クエリプランナーの思考をなぞる。そうして絞り出した数ミリ秒の短縮こそが、大規模システムを支えるエンジニアの矜持ではないでしょうか。

皆さんのデータベースに、幸多からんことを。