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

なぜ、とりあえず「B-tree」を貼るのか？――PostgreSQLインデックス設計の勘所

こんにちは。現場でPostgreSQLと格闘していると、一度は「クエリが遅い」という壁にぶつかりますよね。そんな時、とりあえずインデックスを貼るわけですが、そのデフォルトである「B-tree」について、深掘りしたことはありますか？

「とりあえずB-tree」は、実は理にかなった素晴らしい選択です。でも、その仕組みを理解して使うのと、なんとなく使うのとでは、数年後のシステムのパフォーマンスに大きな差が出ます。今日は、現場の視点からB-treeの「本当の強み」を解説します。

—

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

B-tree（Balanced Tree）は、その名の通り「バランスの取れた木構造」です。データがどんなに増えても、ルートからリーフまで到達する階層（高さ）がほぼ一定になるように設計されています。

PostgreSQLでB-treeが重宝される理由は、「データの順序を保持している」という点にあります。これがあるおかげで、データベースは「点（等価比較）」だけでなく「線（範囲検索）」を爆速で処理できるんです。

等価比較: `WHERE id = 100`
範囲検索: `WHERE created_at > ‘2023-01-01’`
ソート: `ORDER BY created_at`

これらが全部カバーできるのは、B-treeが「整列された状態」を保っているからなんですね。

—

実践！複合インデックスを貼る時の「黄金ルール」

実務でよくあるのが、複数のカラムを組み合わせた複合インデックスです。ここで一つ、僕が後輩によく教える「左側の原則」というコツがあります。

例えば、ユーザーの注文テーブルに対してこんなクエリをよく投げるとしましょう。

SELECT FROM orders
WHERE user_id = 123 AND status = ‘shipped’;

この場合、インデックスは以下のように作るのが定石です。

CREATE INDEX idx_orders_user_status ON orders (user_id, status);

なぜ `(user_id, status)` なのか？

B-treeはインデックスの定義順に「整列」されます。まず `user_id` で大きな塊を作り、その中で `status` が並ぶイメージです。
もしクエリが `WHERE status = ‘shipped’` だけだったらどうなるか？インデックスの先頭が `user_id` なので、PostgreSQLはインデックスを上手く使えません（フルスキャンになりがちです）。

教訓: 「よく使う検索条件」を左側に寄せ、絞り込みの強い（カーディナリティが高い）カラムを意識して配置する。これがインデックス設計の第一歩です。

—

「ソート」を味方につける魔法

意外と知られていないのが、インデックスを貼るだけで「ORDER BY」が消滅するという現象です。

— このクエリが頻発する場合
SELECT FROM logs ORDER BY created_at DESC LIMIT 10;

— こういうインデックスを貼る
CREATE INDEX idx_logs_created_at ON logs (created_at DESC);

B-treeは最初から並んでいるので、データベースはわざわざメモリを使って並び替える必要がありません。インデックスの先頭から10件読むだけで終わる。これが、高負荷な環境で生き残るための「チューニングの秘訣」です。

—

注意点：インデックスの「貼りすぎ」は毒になる

最後に、これだけは伝えておきたい。「インデックスは無料じゃない」ということです。

インデックスを貼れば、当然ですが `INSERT` や `UPDATE` のたびに、B-treeの木構造をメンテナンスするコストが発生します。インデックスが10個も20個もついたテーブルに大量のデータを流し込めば、書き込みはみるみる遅くなります。

使われていないインデックスはないか？ (`pg_stat_user_indexes` で確認できます)
そのクエリ、本当にそのインデックスが必要か？

これらを定期的にチェックするのが、優秀なDBエンジニアの仕事です。

—

まとめ：B-treeと長く付き合うために

B-treeは非常に堅牢で、PostgreSQLの屋台骨です。まずは「順序が保たれている」という特性を意識して設計してみてください。

1. 範囲検索やソートが必要なカラムにはB-treeを検討する。
2. 複合インデックスは、検索条件の「左側」を意識する。
3. インデックスは書き込みコストとのトレードオフだと忘れない。

インデックス設計はパズルに似ています。最初は難しく感じるかもしれませんが、EXPLAINコマンドで実行計画を眺めながら、「どうすればDBが迷わずデータを見つけられるか」を想像する楽しさが分かってくると、きっと仕事がもっと面白くなりますよ。

また現場で迷うことがあったら、いつでも聞いてくださいね。一緒に最適解を探しましょう！