【PostgreSQL】式インデックスで関数を使った検索を高速化する

関数を使った検索はインデックスが効かない

email列にインデックスがあっても、lower(email)のように関数を適用した式で検索すると、そのインデックスは使われない。

CREATE INDEX idx_users_email ON users (email);

EXPLAIN SELECT * FROM users WHERE lower(email) = 'user500@example.com';

 Seq Scan on users  (cost=0.00..2235.00 rows=500 width=25)
   Filter: (lower(email) = 'user500@example.com'::text)

email列のインデックスはemailそのものの値に対して構築されているため、lower(email)という別の値には使えずSeq Scanになる。

式インデックスの作成

CREATE INDEXでは、列名の代わりに式を指定できる。この式インデックスを使うと、式の計算結果に対してインデックスを構築できる。

CREATE INDEX idx_users_email_lower ON users (lower(email));

EXPLAIN ANALYZE SELECT * FROM users WHERE lower(email) = 'user500@example.com';

 Bitmap Heap Scan on users  (cost=16.29..719.43 rows=500 width=25) (actual time=0.027..0.028 rows=1 loops=1)
   Recheck Cond: (lower(email) = 'user500@example.com'::text)
   Heap Blocks: exact=1
   ->  Bitmap Index Scan on idx_users_email_lower  (cost=0.00..16.17 rows=500 width=0) (actual time=0.025..0.025 rows=1 loops=1)
         Index Cond: (lower(email) = 'user500@example.com'::text)

lower(email)に対するインデックスが使われ、Bitmap Index Scanに変わった。メールアドレスの大文字・小文字を区別しない検索を高頻度に行うようなテーブルでは、この式インデックスが有効である。

注意点

式インデックスが使われるのは、クエリの条件がインデックス作成時と同じ式である場合に限られる。lower(email)で作成したインデックスはupper(email)の検索には使われない。式の書き方をアプリケーション側でも統一しておく必要がある。