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

インデックスの「裏側」を使いこなす：PostgreSQLの式インデックスでクエリを極限までチューニングする

PostgreSQLのチューニングにおいて、多くのエンジニアが「カラムに対するインデックス」の壁にぶつかります。`WHERE`句で関数を噛ませた瞬間にインデックスが効かなくなり、`EXPLAIN ANALYZE`の結果を見て溜息をつく……そんな経験、誰しも一度はありますよね。

「`lower(email)`で検索したいけど、`email`カラムにインデックスを貼っても無視される」

もしあなたがまだ、この問題に対して「アプリケーション側で正規化してから検索する」という原始的なアプローチをとっているなら、今日でそのやり方は卒業しましょう。PostgreSQLには、インデックスの概念を拡張する強力な武器、「式インデックス（Expression Indexes）」という選択肢があります。

—

式インデックスは「計算結果のキャッシュ」ではない

式インデックスを単なる「計算結果を保存しておく場所」だと誤解している人がいますが、それは少し違います。内部的な視点で見れば、PostgreSQLは式インデックスを「その式を算出するための、独立した仮想的なカラム」として扱います。

具体的にどういうことか。例えば `CREATE INDEX idx_lower_email ON users (LOWER(email));` と定義したとします。PostgreSQLのクエリプランナは、`WHERE LOWER(email) = ‘…’` という条件が現れたとき、クエリ内の式とインデックス定義の式が「意味的に一致するか」を厳密に照合します。

ここで重要なのは、インデックス自体はB-tree構造を維持しつつ、そのキー値がカラムの値そのものではなく、関数の実行結果であるという点です。つまり、インデックスの各ノードには `LOWER(‘alice@example.com’)` という計算コストを支払った後の値が格納されているわけです。

—

パフォーマンスの真の落とし穴：IMMUTABLEという制約

式インデックスを使いこなす上で、避けて通れないのが関数の「安定性（Volatility）」という概念です。

PostgreSQLは、式インデックスに使用する関数に `IMMUTABLE`（不変）であることを求めます。もし `STABLE` や `VOLATILE` な関数を使おうとすれば、PostgreSQLは即座にエラーを返します。なぜなら、同じ入力に対して異なる結果を返す可能性のある関数をインデックス化してしまえば、インデックスの整合性が崩壊し、検索結果が信用できなくなるからです。

もし、どうしてもインデックス化したい独自関数があるなら、以下の手順で設計を見直す必要があります。

1. 関数の再定義: その関数が本当に外部状態（時刻やテーブルデータなど）に依存していないか精査し、`IMMUTABLE` として定義し直す。
2. ロジックの単純化: 不変性を保つために、関数内で行っている計算をSQLの式としてインデックス定義内に直接展開する。

ここを疎かにして「とりあえず動くようにする」と、将来的にインデックスが壊れたり、データ移行の際に予期せぬ挙動を引き起こしたりするリスクを抱えることになります。

—

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

「式インデックスを作ったのに、プランナがフルスキャンを選択している」

これに直面したとき、まず疑うべきはデータ型の一致です。
例えば、`LOWER(email)` でインデックスを作ったのに、検索条件が `LOWER(email::text) = ‘…’` のようになっていないか確認してください。式がわずかでも異なれば、プランナはそれを「別物」として認識します。

また、意外な伏兵が 「照合順序（Collation）」 です。
データベースのデフォルトの照合順序と、インデックス作成時の照合順序が異なると、インデックスは使われません。特に多言語対応のシステムでは、`COLLATE “C”` を明示的に指定してインデックスを作成することで、比較のオーバーヘッドを抑えつつ意図した通りにインデックスを効かせることができます。

—

熟練エンジニアへのアドバイス：使いすぎは「諸刃の剣」

式インデックスは非常に強力ですが、乱用は禁物です。

書き込みコスト: 当たり前ですが、`INSERT` や `UPDATE` のたびにインデックス化された関数が実行されます。複雑な計算式をインデックスに詰め込むと、書き込みのスループットが目に見えて落ちます。
ストレージの肥大化: 関数によって生成された値が長い文字列や複雑なオブジェクトである場合、インデックスサイズは驚くほど膨らみます。

私が現場でよくやるのは、「本当に検索頻度が高いのか？」の再検証です。式インデックスを作る前に、まずは `pg_stat_user_indexes` を見て、そのクエリが本当にインデックスの恩恵を受けるべきボトルネックなのかを確認してください。

最後に

式インデックスは、PostgreSQLの「柔軟性」を象徴する機能です。クエリの構造に合わせてDB側を変幻自在に最適化できるこの仕組みは、エンジニアにとっての自由度そのものです。

「関数を呼び出すコスト」を「インデックス検索の速さ」で相殺する。このトレードオフを頭の中でシミュレーションできるようになったとき、あなたはデータベースエンジニアとして一つ上のステージに上がったと言えるでしょう。

さあ、次はどのクエリを最適化しますか？