「とりあえずインデックス」で済ませてない？PostgreSQLの「関数ベースインデックス」でクエリを爆速にする話
現場でエンジニアをやっていると、たまにこんなクエリに出くわすよね。
SELECT FROM users WHERE lower(email) = ‘test@example.com’;
で、これに対して「よし、`email`列にインデックスを貼ろう！」といって `CREATE INDEX ON users(email);` を実行する。……でも、パフォーマンスは一向に改善しない。なぜか？
答えは簡単。PostgreSQLのインデックスは「列の値そのもの」を保存しているからです。`lower(email)` という「計算結果」に対して検索をかけているのに、インデックスが持っているのは加工前の値。これじゃあ、データベースは結局フルスキャン（全件検索）するしかないんだ。
今日は、そんな「惜しい」インデックス設計を卒業して、PostgreSQLの隠れた（でも最強の）武器である「関数ベースインデックス（Expression Index）」について深掘りしようと思う。
—
関数ベースインデックスとは何か？
一言で言えば、「計算結果をあらかじめ計算してインデックスに保存しておく」仕組み。
通常、インデックスは `(列の値, ポインタ)` という形で保存されるけど、関数ベースインデックスなら `(関数の計算結果, ポインタ)` という形で保存できる。これを使えば、クエリ実行時に毎回 `lower()` や `jsonb` のパス抽出を走らせる必要がなくなるんだ。
実践例1：大文字・小文字を無視した検索
さっきの `lower()` の例で見てみよう。
CREATE INDEX idx_users_lower_email ON users (lower(email));
これを作るだけで、PostgreSQLは賢いから `WHERE lower(email) = ‘…’` というクエリを見ると、自動的にこのインデックスを使ってくれるようになる。
ここでのポイントは、「クエリ側も同じ関数を使わないといけない」ということ。もしクエリを `WHERE email = ‘…’` と書いてしまったら、このインデックスは無視される。インデックス設計とクエリはセットで考える、これが鉄則だね。
実践例2：JSONBの深い階層を叩くとき
最近はPostgreSQLをドキュメントストアとして使うことも増えたよね。例えば、`metadata` という `jsonb` カラムの中に `{“user_id”: 123}` みたいに情報が入っている場合。
— 毎回こんな検索をしていない？
SELECT FROM logs WHERE metadata->>’user_id’ = ‘123’;
これも `metadata` 全体にインデックスを貼っても、あまり効果的じゃないことが多い。こんな時はこうする。
CREATE INDEX idx_logs_user_id ON logs ((metadata->>’user_id’));
カッコが二重になっているのは、PostgreSQLの構文上のルール。これだけで、数百万件あるログの中から特定のIDを瞬時に引けるようになる。JSONBをバリバリ使う現場なら、このテクニックを知っているだけで神扱いされるはずだぜ。
—
注意点：使いすぎは「諸刃の剣」
ここまで読んだ君なら「じゃあ全部関数ベースインデックスにすればいいじゃん！」と思うかもしれない。でも、ちょっと待って。
1. 書き込みコストは増える: インデックスが増えれば、`INSERT` や `UPDATE` のたびに計算が走る。当然、書き込み処理は重くなる。
2. ディスク容量: 計算結果を保存する場所が必要だから、ストレージを消費する。
3. 計算負荷: あまりにも複雑な関数をインデックスにすると、インデックス自体の更新がボトルネックになる。
基本的には、「頻繁に検索されるけど、その計算処理が重いもの」に絞って使うのが、ベテランのやり方だよ。
最後に：EXPLAIN ANALYZE を信じろ
最後のアドバイス。インデックスを作ったら、必ず `EXPLAIN ANALYZE` を叩いて確認してくれ。
EXPLAIN ANALYZE SELECT FROM users WHERE lower(email) = ‘…’;
ここに `Index Scan` と表示されていれば、君の勝ちだ。もし `Seq Scan`（全件検索）のままなら、インデックスが正しく貼れていないか、クエリの書き方がインデックスの定義と微妙にズレている証拠だ。
データベースは、エンジニアの「意図」をどれだけコードに反映できるか、その腕の見せ所だ。インデックスは単なる高速化の道具じゃなくて、DBに対する「こうやって検索してくれ！」という君からのメッセージなんだよ。
さあ、今のプロジェクトの重いクエリ、一つずつ紐解いていこうか。また何か詰まったら相談してくれよな。
コメント