【実務・中級編】関数インデックス (Expression Index) – 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はインデックスを辿って一瞬でレコードを見つけてくれるようになるんだ。

—

教科書的な話はここまでにして、現場で僕がよく使うケースをシェアするね。

さっきのメールアドレスの例がまさにこれ。ユーザーが入力したメールアドレスの揺れを吸収したいとき、わざわざカラムを小文字で正規化して保存しなくても、関数インデックスさえあれば綺麗なデータ構造を維持したまま爆速検索ができる。

最近のプロダクトだと、JSONB型で柔軟にデータを持ちたい場面も多いよね。例えば、`metadata`というJSONBカラムの中に`{ “status”: “active” }`みたいなデータが入っている場合：

— これだとインデックスが効かないことが多い
SELECT FROM orders WHERE metadata->>’status’ = ‘active’;

— こうやってインデックスを貼る！
CREATE INDEX idx_orders_status ON orders ((metadata->>’status’));

※注意：JSONBの演算は複雑だから、`->>`なのか`->`なのか、型キャストが必要かを確認するのがコツだよ。

「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`句を覗いてみて。そこに計算式が潜んでいたら、関数インデックスの出番だ。

もし何か詰まったら、いつでも聞きに来てよ。現場からは以上です！