【テクニカル・上級編】関数インデックス (Expression Index) – PostgreSQL

「その検索、本当にフルスキャンでいいの？」――関数インデックスを武器にするということ

PostgreSQLを長く触っていると、必ずと言っていいほど「WHERE句で関数を通した瞬間にインデックスが効かなくなる」という壁にぶつかります。

`WHERE LOWER(email) = ‘user@example.com’` なんてクエリを書いて、`EXPLAIN ANALYZE` を叩いた時に、無慈悲な `Seq Scan` が表示された時の絶望感といったら……。

もちろん、アプリケーション側で正規化して保存するのが王道ですが、レガシーなDB設計を変えられない状況や、JSONBのような柔軟なデータ型を扱う現代では、そうも言っていられません。そこで登場するのが「関数インデックス（Expression Index）」です。

今日は、単なる「便利な機能」としてではなく、PostgreSQLの内部アーキテクチャの視点から、このインデックスとどう付き合うべきか、深掘りしてみましょう。

—

関数インデックスの本質は「仮想的な列」

PostgreSQLのインデックスは、基本的に「列の値」をB-treeなどの構造で保持します。しかし、関数インデックスは少し違います。

内部的には、インデックスが作成された時点で「式を評価した結果」を実データとしてB-treeに格納します。つまり、クエリ実行時に `LOWER(email)` を計算しているのではなく、インデックスという「補助的な表」の中に、あらかじめ計算済みの値が並んでいるというわけです。

ここで重要なのは、「クエリのWHERE句が、インデックス作成時に指定した式と完全に一致していなければならない」という制約です。

インデックス：`CREATE INDEX idx_lower_email ON users (LOWER(email));`
クエリ：`SELECT FROM users WHERE LOWER(email) = ‘…’`

このとき、PostgreSQLのオプティマイザは、クエリ内の式を解析し、登録されているインデックス定義と照合します。もし `LOWER` の代わりに `UPPER` を使ったり、引数に余計な関数を一つ噛ませただけで、オプティマイザは「あ、これ別物だ」と判断し、容赦なくインデックスを無視します。

パフォーマンスの「トレードオフ」を可視化する

関数インデックスは魔法ではありません。当然ながら、代償があります。

1. 書き込みコストの増大: インデックスは、挿入や更新のたびに式を再評価します。複雑な関数（特にユーザー定義関数）をインデックスに組み込むと、INSERTのたびにその計算が走るため、書き込み性能が露骨に低下します。
2. 空間計算量: インデックスのサイズは、格納されるデータの特性によって膨らみます。特にJSONB内の特定キーを抽出するような場合、元のデータよりインデックスの方が肥大化することさえあります。

ここでの教訓：
「とにかく全部インデックスにしておこう」という雑な設計は、数万件規模までは快適でも、数億件を超えた瞬間にディスクI/OとCPU負荷のボトルネックとして牙を剥きます。計算の複雑さと、検索頻度のバランスを常に天秤にかける感覚が重要です。

トラブルシューティング：なぜインデックスが選ばれないのか？

現場でよくあるのが、「インデックスは作ったのに、クエリがフルスキャンになる」というケースです。ここにはいくつかの落とし穴があります。

照合順序（Collation）の問題:

`LOWER(col)` でインデックスを作ったはずなのに効かない場合、データベースのロケール設定と照合順序が影響していることがあります。特に `COLLATE “C”` を明示的に指定していない場合、インデックスと実行時で評価結果が変わるリスクがあるため、オプティマイザが慎重になることがあります。

データ型の不一致:

`JSONB` のキー検索でよくあるミスです。例えば `(data->>’id’)::int` とキャストしてインデックスを張ったのに、クエリ側で `data->>’id’ = ‘123’` と文字列比較をしているケース。暗黙の型変換が働かない場合、インデックスは使われません。`EXPLAIN` を見て、型変換が起きている箇所がないか常にチェックしてください。

関数の不確定性（Immutability）:

ここが最も玄人向けのポイントです。PostgreSQLのインデックスには `IMMUTABLE`（同じ引数には必ず同じ結果を返す）な関数しか使えません。もし `STABLE` や `VOLATILE` な関数を無理やりインデックスに使おうとすると、そもそも作成自体が拒否されます。独自関数を組み込む際は、関数定義の `IMMUTABLE` 指定を忘れないように。

最後に：エンジニアの美学として

関数インデックスは、DBの構造的欠陥をカバーする「救済措置」でもありますが、適切に使えば複雑な要件をスマートに解決する「強力な武器」にもなります。

特にJSONBの普及で、スキーマレスなデータ構造をリレーショナルDBにねじ込むことが増えた今、この技術なしではパフォーマンスを維持するのは困難です。

「なぜインデックスが効かないのか」を突き詰めることは、PostgreSQLというエンジンの思考回路を理解することと同義です。パフォーマンスチューニングは、単なる作業ではなく、データベースと対話する知的活動だと私は思っています。

次回のチューニング時、`EXPLAIN` の出力に `Index Scan` が現れたときの小さな喜びを、ぜひ皆さんも味わってください。それでは、また。