【テクニカル・上級編】関数ベースのインデックス – PostgreSQL

「WHERE句で関数を叩く」という誘惑と、PostgreSQLの賢い逃げ道：関数ベースインデックスの深淵

データベース設計をしていると、どうしても避けられない瞬間がある。「ユーザーが入力した文字列を、大文字小文字を区別せずに検索させたい」といった要件だ。

多くの初学者は、安易に `WHERE LOWER(email) = LOWER(?)` と書いてしまう。これを見た瞬間、熟練のエンジニアは眉をひそめる。なぜなら、通常のB-treeインデックスはカラムの「生のデータ」に構築されるものであって、「関数の計算結果」に対しては無力だからだ。

しかし、PostgreSQLにはこれに対する非常にエレガントな解決策がある。それが「関数ベースインデックス（Expression Indexes）」だ。今日は、この一見シンプルに見える機能の裏側にある、プロフェッショナルが知っておくべき挙動と落とし穴について話をしよう。

—

なぜ関数ベースインデックスは「魔法」なのか

PostgreSQLのインデックスは、単なる「列の値のコピー」ではない。実際には、インデックスの定義内に記述された「式」を評価した結果を保存する、一種の「計算済みキャッシュ」のようなものだ。

例えば、以下のように定義したとする。

CREATE INDEX idx_users_lower_email ON users (LOWER(email));

このインデックスが作成されると、テーブルの行が挿入・更新されるたびに、PostgreSQLは `LOWER(email)` を計算し、その結果をB-tree構造に格納する。クエリ実行時にオプティマイザは、検索条件の `LOWER(email)` とインデックスの定義が一致しているかを判定し、一致すれば迷わずインデックススキャンを選択する。

ここまでは教科書通りだ。だが、現場でトラブルシューティングを行う際、我々が注目すべきは「いかにクエリがインデックスを利用できるか」という最適化の機微にある。

パフォーマンストラブルの種：式の一致という「厳格なルール」

関数ベースインデックスが機能しなくなる原因の第1位は、「わずかな式の不一致」だ。

— インデックス定義
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

— 検索クエリ
SELECT FROM users WHERE LOWER(email) = ‘example@gmail.com’; — 利用される
SELECT FROM users WHERE LOWER(email) = LOWER(‘Example@Gmail.com’); — 利用される
SELECT FROM users WHERE email = ‘example@gmail.com’; — 利用されない（フルスキャン！）

特に見落としがちなのが、`COLLATE`（照合順序）の影響だ。もしデータベースが `C` ロケールで動いているのに、式の中で特定のロケールを指定して比較したりすると、オプティマイザは「これは同じ式ではない」と判断し、インデックスを無視することがある。

`EXPLAIN ANALYZE` を叩いて、`Index Scan` が発生していないときは、まず検索条件の式とインデックス定義の式を、スペースの一個単位まで見比べてみてほしい。

内部アーキテクチャから見た「代償」

関数ベースインデックスは万能ではない。エンジニアとして忘れてはならないのは、「計算コストの転嫁」だ。

1. 書き込み負荷（Write Amplification）:
インデックスの数だけ `LOWER()` 関数の計算が走る。大量の行を一括更新するバッチ処理がある場合、この計算コストがCPUを圧迫し、書き込みスループットを劇的に低下させる可能性がある。

2. 関数の安定性（IMMUTABLEの呪縛）:
PostgreSQLは、インデックス内に「不変（IMMUTABLE）」な結果しか保存できない。もし使用する関数が `VOLATILE`（現在時刻に依存するなど、実行するたびに結果が変わるもの）であれば、インデックス作成自体が拒否される。もし独自の関数をインデックスで使いたいなら、それが正しく `IMMUTABLE` として宣言されているか確認が必要だ。

現場で役立つ「高度な戦術」

もし、巨大なテーブルで「特定のカラムに対して複数の関数で検索したい」という要件があるなら、インデックスを量産する前に一度立ち止まろう。

最近のPostgreSQL（v12以降）では、`generated columns`（生成列）とインデックスを組み合わせる手法が非常に有効だ。

ALTER TABLE users ADD COLUMN email_lower text GENERATED ALWAYS AS (LOWER(email)) STORED;
CREATE INDEX idx_users_email_lower ON users (email_lower);

このように物理的に列として保持することで、検索クエリがシンプルになるだけでなく、クエリの可読性が格段に向上する。さらに、検索の意図が明確になるため、将来的なメンテナンス時にも「なぜこのインデックスがあるのか」という悩みが減る。

最後に

関数ベースインデックスは、データベースの表現力を広げる強力な武器だ。だが、その裏側にある「計算コスト」と「式の一致という制約」を理解していないと、いざという時に足元をすくわれる。

パフォーマンスチューニングとは、突き詰めれば「データベースに何を計算させ、何を省かせるか」という引き算の美学だ。ぜひ次の設計では、このインデックスをただの解決策としてではなく、システム全体の計算コストを最適化する戦略の一部として組み込んでみてほしい。

さて、次はどのインデックスの深淵を覗こうか。また次回、コードの向こう側でお会いしよう。