【実務・中級編】 B-treeインデックスの最適化と特性 – PostgreSQL

なぜそのインデックス、効かないのか？PostgreSQLのB-treeと長く付き合うための心得

「インデックスを貼ったはずなのに、クエリが遅い」。
現場でこんな相談を受けたとき、僕は決まって「`EXPLAIN ANALYZE`の結果を見せて」と伝えます。

PostgreSQLのデフォルトであるB-treeインデックス。これ、実はただ「貼れば速くなる魔法の杖」じゃないんです。仕組みを理解せずに闇雲に貼ると、かえってストレージを圧迫し、書き込み負荷を増大させるだけの「お荷物」になってしまうこともあります。

今日は、B-treeの「本当の性格」を知って、明日のチューニングに役立てるための話をしましょう。

—

B-treeは「辞書」だと思えばいい

B-treeの構造を難しく考える必要はありません。イメージとしては「辞書」や「電話帳」です。

等価比較 (`=`): 「田中さん」を探すとき、最初からページをめくる人はいないですよね。索引を使って目的の場所に直接飛ぶ。これがB-treeの真骨頂です。
範囲検索 (`<`, `>`, `BETWEEN`): 辞書で「た」から「つ」までを探すとき、一度場所を見つけたら、あとは連続したページをめくるだけで済みますよね。B-treeはデータがソートされた状態で保持されているので、この「範囲スキャン」がめちゃくちゃ速いんです。

一方で、`LIKE ‘%検索ワード’` のような前方一致しない検索や、関数を通した値 (`WHERE upper(name) = ‘TANAKA’`) に対しては、B-treeは無力です。先頭の文字が分からないと、辞書を最初から最後まで全部めくるしかないのと同じですね。

クエリ性能の分かれ道：Index Scan vs Index Only Scan

ここからが少し専門的な話。PostgreSQLの実行計画を見ていると、`Index Scan` と `Index Only Scan` という言葉が出てきますよね。この違いを意識できているかで、エンジニアとしての格が一段上がります。

1. Index Scan（インデックススキャン）

インデックスには「値」と「そのデータが物理的にどこにあるか（ポインタ）」しか書かれていません。
そのため、インデックスで目的の場所を見つけた後、わざわざテーブルの実体（ヒープ）までデータを取りに行く必要があります。これがコストの正体です。

2. Index Only Scan（インデックスオンリースキャン）

もし、クエリで要求されているデータが、インデックスの中にすべて含まれていたら？
わざわざテーブル本体を見に行く必要はありません。インデックスだけで答えが出る。これが最速のパターンです。

— 例えばこんなテーブルがあるとします
CREATE TABLE users (
id serial PRIMARY KEY,
username text,
email text
);

— usernameにインデックスを貼る
CREATE INDEX idx_users_username ON users(username);

— 1. Index Scanになる可能性が高い（emailも取得するため）
SELECT username, email FROM users WHERE username = ‘alice’;

— 2. Index Only Scanになる（インデックスの情報だけで完結！）
SELECT username FROM users WHERE username = ‘alice’;

もし、特定のカラムだけを頻繁にSELECTするなら、そのカラムをインデックスに含める「Covering Index」という手法がめちゃくちゃ効いてきます。

現場でよくある「落とし穴」

僕が後輩のコードレビューでよく指摘するのは、以下の2点です。

カーディナリティの低いカラムへのインデックス:

例えば「性別」や「フラグ」カラム。選択肢が少ないデータにインデックスを貼っても、PostgreSQLのオプティマイザは「全件検索した方が速いな」と判断してインデックスを無視することが多いです。無駄なインデックスは `UPDATE` や `INSERT` を遅くするだけなので、勇気を持って削除しましょう。

複合インデックスの順序:

`CREATE INDEX idx_test ON table(a, b);` としたとき、`WHERE a = 1 AND b = 2` は速いですが、`WHERE b = 2` だけではこのインデックスは使われません（B-treeの左側から順に判定するため）。「どの条件が絞り込みの第一段階か」を考えて順序を決めるのがコツです。

最後に

インデックスチューニングは、いわば「整理整頓」です。
あまりに多くのインデックスを貼ると、データ更新のたびに本棚の整理をし直さなければならず、システムは悲鳴を上げます。「このクエリのために、本当にインデックスが必要か？」「Index Only Scanでいけないか？」と一度立ち止まって考える習慣をつけてみてください。

データベースと会話できるようになると、エンジニアとしての仕事はもっと面白くなりますよ。さて、次はあなたのクエリ計画を見てみましょうか。何かわからないことがあったら、いつでも聞いてくださいね。