「それ、全件スキャンしてない？」PostgreSQLの関数インデックスで検索を劇的に速くする魔法
やあ。最近、コードレビューで「検索が遅い」という相談を受けることが増えたんだけど、その原因の多くが「インデックスを貼っているのに、それが使われていない」という悲しい状況なんだよね。
特に多いのが、`WHERE`句で列に対して関数をかませちゃっているパターン。「これ、インデックス貼ってあるじゃん！」って言いたくなる気持ちもわかるんだけど、DBはそんなに甘くない。
今日は、そんな悩みを一発で解決する「関数インデックス（Expression Index）」という、現場でマジで重宝するテクニックについて話そうと思う。これを知っているだけで、パフォーマンスチューニングの引き出しがグッと増えるはずだよ。
—
なぜ普通のインデックスが効かないのか？
まずは復習から。PostgreSQLで`CREATE INDEX`を作ると、テーブルの「列の値」そのものをB-Tree構造に並べるよね。
でも、こんなクエリを投げたらどうなると思う？
SELECT FROM users WHERE LOWER(email) = ‘hoge@example.com’;
このとき、DBは「`email`列の値」を知っているけど、「`LOWER(email)`の結果」がどうなっているかは知らないんだ。だから、結局テーブルを全件走査（シーケンシャルスキャン）する羽目になる。これが遅延の正体。
関数インデックスの登場
ここで登場するのが、計算結果そのものをインデックスにぶち込む「関数インデックス」だ。
CREATE INDEX idx_users_lower_email ON users (LOWER(email));
こう書くと、PostgreSQLは「`LOWER(email)`の計算結果」をインデックスとして保持してくれる。次に同じクエリを投げたとき、DBはインデックスを辿って一瞬でレコードを見つけてくれるようになるんだ。
—
実務で「これ使える！」と思った3つのユースケース
教科書的な話はここまでにして、現場で僕がよく使うケースをシェアするね。
1. 大文字・小文字を無視した検索
さっきのメールアドレスの例がまさにこれ。ユーザーが入力したメールアドレスの揺れを吸収したいとき、わざわざカラムを小文字で正規化して保存しなくても、関数インデックスさえあれば綺麗なデータ構造を維持したまま爆速検索ができる。
2. JSONB内の特定のキーを検索
最近のプロダクトだと、JSONB型で柔軟にデータを持ちたい場面も多いよね。例えば、`metadata`というJSONBカラムの中に`{ “status”: “active” }`みたいなデータが入っている場合：
— これだとインデックスが効かないことが多い
SELECT FROM orders WHERE metadata->>’status’ = ‘active’;
— こうやってインデックスを貼る！
CREATE INDEX idx_orders_status ON orders ((metadata->>’status’));
※注意：JSONBの演算は複雑だから、`->>`なのか`->`なのか、型キャストが必要かを確認するのがコツだよ。
3. 部分的な日付検索（年・月だけ抽出）
「2023年のデータだけ取りたい」なんて要件、よくあるよね。`created_at`（タイムスタンプ）から年だけを抽出する場合にも有効だ。
CREATE INDEX idx_orders_year ON orders (EXTRACT(YEAR FROM created_at));
—
使う前に知っておいてほしい「注意点」
ただ、便利な反面、気をつけてほしいこともある。
- 書き込み性能のトレードオフ:
当たり前だけど、インデックスが増えるということは、`INSERT`や`UPDATE`のたびに計算コストがかかるということ。闇雲に何でもインデックスを貼ればいいわけじゃない。
- クエリとインデックスの完全一致:
`LOWER(email)`でインデックスを作ったなら、クエリ側も必ず`LOWER(email)`と記述しないとダメだ。「`UPPER(email)`」とか「`LOWER(email) || ‘ ‘`」みたいに少しでも式が変わると、DBはインデックスを無視しちゃうから気をつけてね。
- ストレージ容量:
関数インデックスは実質的に「計算結果という名のカラム」を裏で管理しているようなもの。巨大なテーブルだとそれなりのサイズを食うから、`EXPLAIN ANALYZE`で本当に効いているか確認する癖をつけよう。
—
最後に
「インデックスは貼るもの」じゃなくて、「クエリに合わせて設計するもの」。
この感覚を掴むと、パフォーマンスチューニングがパズルみたいで楽しくなってくるはずだよ。もし「この検索、インデックス貼ってるのに遅いんだよな…」と悩んでいる後輩がいたら、まずはそのクエリの`WHERE`句を覗いてみて。そこに計算式が潜んでいたら、関数インデックスの出番だ。
もし何か詰まったら、いつでも聞きに来てよ。現場からは以上です！
コメント