「とりあえずインデックス」で満足してない？PostgreSQLのB-treeを深掘りする
現場でコードを書いていて、「検索が遅いからインデックスを貼る」というのは、もはや呼吸をするのと同じくらい当たり前の作業だよね。でも、そのインデックスが「なぜ速いのか」、そして「どんな時に裏切られるのか」をちゃんと説明できるエンジニアは、実はそう多くない。
今日は、PostgreSQLの「標準装備」であり、最も頼りになる相棒、B-treeインデックスについて、現場の視点から少し突っ込んだ話をしようと思う。
—
B-treeって、結局どうなってるの？
よくある勘違いが、「インデックスは単なるソート済みのリストだ」と思っていること。あれは間違いじゃないけど、データ量が増えたときを想像してみて。数百万、数千万行あるテーブルで、端から順に見ていくのは効率が悪すぎるよね。
PostgreSQLのB-treeは、「平衡木（Balanced Tree）」という構造をしている。これが何をもたらすかというと、ルート（根）からリーフ（葉）まで、どのデータにアクセスしても「同じステップ数でたどり着ける」ということだ。
- ルート（Root）: 最初の入り口。
- ブランチ（Branch）: 分岐点。ここで「右に行くか左に行くか」を判断する。
- リーフ（Leaf）: 実際のデータへのポインタ（TID）が格納されている終着点。
このおかげで、検索対象がどれだけ膨大になっても、ログオーダー（対数時間）の計算量で目的のデータに到達できる。これが、DBが爆速で動く一番の理由だよ。
—
どんな時に「B-tree」を愛すべきか
B-treeは万能選手だ。以下の演算子を使うクエリなら、迷わずB-treeを信頼していい。
- `<` , `<=` , `=` , `>=` , `>`
- `BETWEEN`
- `IN`
- `IS NULL` / `IS NOT NULL`
逆に、「前方一致」以外のLIKE検索（`LIKE ‘%hoge’`など）や、特定の関数を通した検索（`WHERE lower(name) = ‘taro’`）なんかは、通常のB-treeインデックスでは歯が立たない。ここが、インデックスが効かないクエリの典型例だね。
—
実践：こんな時、どうする？
例えば、ユーザーの「登録日」と「ステータス」で頻繁に絞り込むようなテーブルを考えてみよう。
— よくあるクエリ
SELECT FROM users
WHERE created_at > ‘2023-01-01’
AND status = ‘active’;
ここで「`created_at`にインデックスを貼ればいいや」と安直に考えると、意外とパフォーマンスが伸びないことがある。PostgreSQLのオプティマイザは、複数のインデックスをマージ（Bitmap Index Scan）することもあるけど、基本は「複合インデックス」を貼るのが鉄則だ。
— 複合インデックスの作成
CREATE INDEX idx_users_status_created_at ON users (status, created_at);
ここで重要な「順番」の話
複合インデックスを作るときは、「等価比較（＝）」を先に、その後に「範囲比較（＞、＜）」を置くのがセオリーだ。
なぜかって？ B-treeの構造上、先に「statusがactiveなもの」をガサッと絞り込んでから、その中で「日付順」に並んでいるデータを探すほうが、探索効率が段違いにいいからだよ。
—
先輩から最後に一つだけアドバイス
インデックスは「魔法の杖」じゃない。貼れば貼るほど、`INSERT`や`UPDATE`のたびにインデックスの再構築コストが発生して、書き込み性能が落ちる。
1. まずは `EXPLAIN ANALYZE` を叩く癖をつけること。 実際にインデックスが使われているか、予想外のフルスキャンになっていないかを確認する。
2. 使われないインデックスは罪。 定期的に `pg_stat_user_indexes` を見て、スキャン回数が0に近いインデックスは思い切って削除する勇気も必要だよ。
PostgreSQLのアーキテクチャを理解すると、クエリを書くのが楽しくなるはずだ。「なんとなく」から「納得して」インデックスを貼れるようになると、君の書くコードの信頼性はグッと上がる。
何か具体的に「このクエリ、遅いんだけど…」っていう悩みがあったら、いつでも相談してくれ。一緒に実行計画を眺めようじゃないか。
コメント