【実務・中級編】 B-treeインデックス – PostgreSQL

PostgreSQLのB-treeインデックス：なんとなく使っているそのインデックス、本当に最適？

やあ。データベースのパフォーマンスチューニングに頭を悩ませる日々、お疲れ様。
今日はPostgreSQLのデフォルトであり、かつ最も奥が深い「B-treeインデックス」について少し話をしようと思うんだ。

「とりあえずインデックスを貼っておけば速くなるだろう」と、カラムに片っ端からインデックスを貼って満足していないか？気持ちはわかる。でも、そのインデックスが裏でどう動いているかを理解しているのといないのとでは、大規模データに直面した時の「余裕」が全く違ってくるんだ。

今日は、教科書的な説明は最小限にして、現場で僕たちがどう意識すべきかにフォーカスしていくよ。

—

B-treeが「最強のデフォルト」である理由

PostgreSQLでインデックスを指定せずに作成すると、デフォルトでB-treeが選ばれるよね。これにはちゃんと理由がある。

B-treeは「バランスの取れた木構造」だ。データの挿入・削除が起きても、常に木の高さを維持して、目的のデータに辿り着くまでのコストを対数時間（O(log n)）に抑えてくれる。

特筆すべきは、「等価比較（=）」だけでなく、「範囲検索（<, >, BETWEEN）」にも適していること。これが他のインデックス（ハッシュインデックスなど）にはない最大の強みだね。

現場でよく見る「残念なインデックス」の使いどころ

よくあるのが、インデックスを貼っているのに効いていないケース。これ、実は設計の段階で防げるんだ。

例えば、こんなクエリを考えてみてほしい。

— ユーザーテーブルのメールアドレスで検索
SELECT FROM users WHERE email LIKE ‘%@gmail.com’;

これ、`email` カラムにB-treeインデックスがあっても、ほとんどの場合フルスキャンが走る。なぜなら、先頭がワイルドカード（`%`）で始まっているから、B-treeの「左から順番に比較していく」という特性が殺されてしまうんだ。

先輩からのアドバイス：
LIKE検索でインデックスを効かせたいなら、前方一致（`LIKE ‘gmail.com%’`）にするか、どうしても後ろを検索したいなら `pg_trgm` 拡張を使った「トライグラムインデックス」を検討すべきだ。B-treeは魔法の杖じゃない。特性を理解して、それに合わせた検索条件を書くのがプロの仕事だよ。

複合インデックスの「順序」が勝負を決める

複数のカラムでインデックスを貼る時、順番を適当に決めていないかな？
B-treeの複合インデックスは、左側のカラムから順にソートされていることを忘れてはいけない。

— よくある設計
CREATE INDEX idx_users_status_created_at ON users (status, created_at);

このインデックスが効くのは：
1. `WHERE status = ‘active’`（先頭カラムが使われている）
2. `WHERE status = ‘active’ AND created_at > ‘2023-01-01’`（先頭から順番に使われている）

逆に、`WHERE created_at > ‘2023-01-01’` だけの検索だと、このインデックスは無視されることが多い。もし `created_at` 単体での検索頻度が高いなら、別のインデックスを検討するか、インデックスの設計自体を見直す必要がある。

インデックスは「貼れば貼るほど遅くなる」という事実

ここ、意外と忘れがちなんだけど、インデックスはあくまで「データの検索を助ける地図」だ。地図をコピーしてたくさん持ち歩けば、移動（データの挿入・更新）のたびに、その全ての地図を書き換えないといけない。

書き込みが多いテーブル：インデックスは最小限に。
読み取り中心の分析テーブル：インデックスを多めに。

このバランスを見極めるのが、データベースエンジニアとしての腕の見せ所だね。`EXPLAIN ANALYZE` を叩いて、実際にインデックスが使われているか、無駄なインデックスがないかを確認する習慣は、今日からでもつけてほしい。

まとめ：インデックスは「武器」だ

B-treeインデックスは、正しく使えば数千万件のデータからミリ秒単位で結果を返してくれる頼もしい武器になる。でも、ただ貼るだけでは重りにもなる。

1. 先頭から検索条件が始まるか？（前方一致、複合インデックスの順序）
2. そのインデックスは本当に使われているか？（EXPLAINで見極める）
3. 書き込み負荷と検索速度のトレードオフは適正か？

まずは今のプロジェクトのクエリを、`EXPLAIN` を通して眺めてみるところから始めてみよう。何か気づきがあったら、またいつでも相談してくれよ。

現場からは以上だ。また書くよ。