Postgres: lower(email) で検索するなら式インデックスを張る

WHERE lower(email) = lower($1) は email 列の普通のインデックスを使えない。CREATE INDEX ON users (lower(email)) のように式そのものにインデックスを張る。

何が起きるか

B-tree インデックスは「列の値」で並んでいるので、関数を通した値 lower(email) での検索には使えない。大文字小文字を無視したログイン検索を書くと、ログインのたびに users の全件走査（Seq Scan）になる。行数が少ないうちは気づかず、ユーザーが増えてから遅くなる。

やり方

sql

-- 式インデックス。UNIQUE にすると大文字小文字だけ違う重複登録も防げる
CREATE UNIQUE INDEX CONCURRENTLY users_email_lower_key
  ON users (lower(email));

-- クエリはインデックスと「同じ式」で書く
SELECT * FROM users WHERE lower(email) = lower($1);

EXPLAIN で Index Scan using users_email_lower_key になっていれば効いている。

注意点

クエリの式がインデックスの式と一字一句同じでないと使われない。lower(email) で張って upper(email) や email ILIKE $1 で検索しても効かない。
既存データに大文字小文字違いの重複があると UNIQUE インデックスの作成が失敗する。先に SELECT lower(email), count(*) FROM users GROUP BY 1 HAVING count(*) > 1 で確かめる。
本番の大きなテーブルでは CONCURRENTLY を付ける。失敗すると INVALID なインデックスが残るので PostgreSQL: 失敗した CREATE INDEX CONCURRENTLY は INVALID なインデックスを残すの手順で確認する。
別案として、拡張の citext 型を使えば列そのものが大文字小文字を区別しなくなり、普通の = とインデックスで済む。保存時に小文字へそろえておく方法もある。

出典

@amitsinha__07 · 2026-10-28