【PostgreSQL】 WHERE 句に関数をかけるとインデックスが使われないことを確認してみた
インデックスを貼っても、クエリ実行時に使用されないことがあります。
今回は WHERE 句に関数をかけた場合にインデックスが使われない理由を検証してみました。
一例として以下のようなパターンが挙げられます。
-- users テーブルの email カラムにインデックス作成
blog_sample_db=# CREATE INDEX idx_users_email ON users(email);
CREATE INDEX
-- 以下検索クエリを実行しても、インデックスが使用されず全件スキャンのまま
SELECT * FROM users WHERE UPPER(email) = 'USER_5000@EXAMPLE.COM'
以下で検証していきます。
実行環境
実行環境は EC2（AL2023）にインストールした PostgreSQL を使用しました。
EC2 に PostgreSQL をインストールする手順としては下記をご参照ください。
PostgreSQL 17.7 を利用し、検証していきます。
[ec2-user@ip-10-0-13-178 ~]$ sudo -u postgres psql
psql (17.7)
Type "help" for help.
postgres=# select * from version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 17.7 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
サンプルデータの作成
検証用にデータベースとテーブルを作成します。
-- 新規データベース作成
postgres=# CREATE DATABASE blog_sample_db;
CREATE DATABASE
-- 作成したデータベースへ接続
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- テーブル作成
blog_sample_db=# CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT,
age INT,
email TEXT
);
CREATE TABLE
-- 10万件データ投入
blog_sample_db=# INSERT INTO users (name, age, email)
SELECT
'user_' || i,
(random() * 100)::INT,
'user_' || i || '@example.com'
FROM generate_series(1, 100000) i;
INSERT 0 100000
-- 件数確認
blog_sample_db=# SELECT COUNT(*) FROM users;
count
--------
100000
(1 row)
作成したデータは以下のようなものです。
-- 最初の 5 行
blog_sample_db=# SELECT * FROM users LIMIT 5;
id | name | age | email
----+--------+-----+--------------------
1 | user_1 | 0 | user_1@example.com
2 | user_2 | 76 | user_2@example.com
3 | user_3 | 96 | user_3@example.com
4 | user_4 | 21 | user_4@example.com
5 | user_5 | 30 | user_5@example.com
(5 rows)
-- 最後の 5 行
blog_sample_db=# SELECT * FROM users ORDER BY id DESC LIMIT 5;
id | name | age | email
--------+-------------+-----+-------------------------
100000 | user_100000 | 22 | user_100000@example.com
99999 | user_99999 | 2 | user_99999@example.com
99998 | user_99998 | 32 | user_99998@example.com
99997 | user_99997 | 45 | user_99997@example.com
99996 | user_99996 | 3 | user_99996@example.com
(5 rows)
作成したテーブルの各カラムの説明を以下に示します。
| カラム名 | 型 | 説明 |
|----------|---------|-------------------------------------|
| id | INTEGER | 1 〜 100,000 までの連番 |
| name | TEXT | user_1 〜 user_100000 までの文字列 |
| age | INTEGER | 0 〜 100 のランダムな整数 |
| email | TEXT | user_1@example.com 〜 |
| | | user_100000@example.com までの文字列|
インデックスの作成
作成した users テーブルの email カラムにインデックスを作成します。
-- インデックス作成
blog_sample_db=# CREATE INDEX idx_users_email ON users(email);
CREATE INDEX
-- インデックス確認
blog_sample_db=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | text | | |
age | integer | | |
email | text | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"idx_users_email" btree (email)
検証してみた
まず、WHERE 句に関数条件を付けずに普通に検索してみます。
以下結果より、インデックススキャンが行われ、クエリ実行時間は 0.035 ms であることがわかります。
-- パターン1: インデックスあり・普通に検索
blog_sample_db=# EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user_5000@example.com';
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
-------------
Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=40) (actual time=0.020..0.021 row
s=1 loops=1)
Index Cond: (email = 'user_5000@example.com'::text)
Planning Time: 0.070 ms
Execution Time: 0.035 ms
(4 rows)
Index Scan using idx_users_email on users
Index Cond: (email = 'user_5000@example.com'::text)
-> インデックススキャン
-> インデックスを使って一発で見つけている
続いて、WHERE 句の条件に関数 UPPER() をかけて検索してみます。
-- パターン2: WHERE 句にて UPPER() をかけて検索
blog_sample_db=# EXPLAIN ANALYZE SELECT * FROM users WHERE UPPER(email) = 'USER_5000@EXAMPLE.COM';
QUERY PLAN
------------------------------------------------------------------------------------------------------
Seq Scan on users (cost=0.00..2435.00 rows=500 width=40) (actual time=2.123..41.237 rows=1 loops=1)
Filter: (upper(email) = 'USER_5000@EXAMPLE.COM'::text)
Rows Removed by Filter: 99999
Planning Time: 0.050 ms
Execution Time: 41.254 ms
(5 rows)
Seq Scan on users
Filter: (upper(email) = 'USER_5000@EXAMPLE.COM'::text)
Rows Removed by Filter: 99999
-> フルスキャン(10万件全件スキャン)した上で 99,999 件を破棄
-> インデックスが使用されず、完全に無視されている
ちなみに他の関数でやってみてもインデックスは使用されず、全て Seq Scan (全件スキャン) になります。
-- LOWER()：小文字に変換
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM users WHERE LOWER(email) = 'user_5000@example.com';
QUERY PLAN
------------------------------------------------------------------------------------------
------------
Seq Scan on users (cost=0.00..2435.00 rows=500 width=40) (actual time=6.434..58.350 rows
=1 loops=1)
Filter: (lower(email) = 'user_5000@example.com'::text)
Rows Removed by Filter: 99999
Planning Time: 12.792 ms
Execution Time: 59.883 ms
(5 rows)
-- LENGTH()：文字列の長さを取得
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM users WHERE LENGTH(email) = 20;
QUERY PLAN
------------------------------------------------------------------------------------------
--------------
Seq Scan on users (cost=0.00..2435.00 rows=500 width=40) (actual time=0.021..13.231 rows
=900 loops=1)
Filter: (length(email) = 20)
Rows Removed by Filter: 99100
Planning Time: 0.069 ms
Execution Time: 13.289 ms
(5 rows)
インデックスが使われない理由
上記結果のようにインデックスが使用されない理由は PostgreSQL がインデックスを使えない状況になってしまっているからです。
インデックスは作成時のカラムの値をもとに構築されています。
今回の例では email カラムに対してインデックスを作成したため、以下のような値がインデックスに登録されています。
-- インデックスに登録されている値
user_1@example.com
user_2@example.com
...
user_5000@example.com ← 全て小文字
WHERE 句に関数をかけるとインデックスに登録されている値と異なるものを比較して検索することになります。
-- UPPER() をかけた場合
USER_5000@EXAMPLE.COM ← 大文字に変換
インデックスの値と一致しない
-- LENGTH() をかけた場合
20 ← 文字数を返している
インデックスには文字数は登録されていない
-- LOWER() をかけた場合
user_5000@example.com ← 一見同じに見えるが...
大文字データが混在している可能性がある
ここでもう一つ重要なのは PostgreSQL は email カラムの中身を事前に知らないことです。
LOWER() の例で考えてみます。
例えば以下のように大文字小文字が混在しているデータがある場合、
id: 5000 → user_5000@example.com ← 小文字
id: 100001 → USER_5000@EXAMPLE.COM ← 大文字
PostgreSQL では大文字小文字は別のデータとして扱われるため、この2件は異なるデータとして登録されます。
この状態で LOWER() をかけて検索するとどちらも同じ値に変換されてしまいます。
LOWER('user_5000@example.com') → user_5000@example.com
LOWER('USER_5000@EXAMPLE.COM') → user_5000@example.com
つまりインデックスには user_5000@example.com と USER_5000@EXAMPLE.COM が別々に登録されているにも関わらず LOWER() をかけた結果は同じになるため、インデックスで正しく区別できなくなります。
人間の視点だと email カラムの中身が全て小文字だということを知っているので、
「どうせ email カラムには小文字のデータしかないから LOWER() をかけても同じでしょ」と思いますが、PostgreSQL はカラムの中身を事前に知りません。そのため、大文字のデータが混在している可能性も考慮して LOWER() をかけた場合でも全件スキャンを選択しているということです。
インデックスは「検索する値とインデックスの値がそのまま比較できる場合」にのみ使われます。
検索条件に LOWER() などの関数をかけると、インデックス作成時のカラムの値と変わってしまう可能性があるため
PostgreSQL はインデックスを使わず「念の為」全件スキャンするしかないということになります。
対策
対策として、WHERE 句のカラムに関数をかけてもインデックスを使う方法は2つあります。
- 関数を値の側にかける
以下のようにかけたい関数を値の方に適用することで、インデックススキャンにできます。
# EXPLAIN ANALYZE SELECT * FROM users WHERE email = LOWER('USER_5000@EXAMPLE.COM');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
-------------
Index Scan using idx_users_email on users (cost=0.42..8.44 rows=1 width=40) (actual time=0.023..0.024 row
s=1 loops=1)
Index Cond: (email = 'user_5000@example.com'::text)
Planning Time: 0.087 ms
Execution Time: 0.037 ms
(4 rows)
- 関数インデックスを使う
対策 2 つ目は、インデックス作成時に関数をかけた結果をあらかじめ登録しておく方法です。
以下、UPPER() および LENGTH() の例を示します。
結果より、どちらもインデックスが使われていることがわかります。
-- 関数インデックスを作成
blog_sample_db=# CREATE INDEX idx_users_email_upper ON users(UPPER(email));
CREATE INDEX
-- インデックスが作成されたか確認
blog_sample_db=# \d users
Table "public.users"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('users_id_seq'::regclass)
name | text | | |
age | integer | | |
email | text | | |
Indexes:
"users_pkey" PRIMARY KEY, btree (id)
"idx_users_email" btree (email)
"idx_users_email_upper" btree (upper(email))
-- WHERE 句に関数をかけたままクエリ実行
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM users WHERE UPPER(email) = 'USER_5000@EXAMPLE.COM';
QUERY PLAN
------------------------------------------------------------------------------------------------------------
---------------------
Bitmap Heap Scan on users (cost=16.29..833.58 rows=500 width=40) (actual time=0.025..0.025 rows=1 loops=1)
Recheck Cond: (upper(email) = 'USER_5000@EXAMPLE.COM'::text)
Heap Blocks: exact=1
-> Bitmap Index Scan on idx_users_email_upper (cost=0.00..16.17 rows=500 width=0) (actual time=0.020..0
.020 rows=1 loops=1)
Index Cond: (upper(email) = 'USER_5000@EXAMPLE.COM'::text)
Planning Time: 0.166 ms
Execution Time: 0.041 ms
(7 rows)
-- LENGTH() をかけた関数インデックスを作成
blog_sample_db=# CREATE INDEX idx_users_email_length
ON users(LENGTH(email));
CREATE INDEX
-- LENGTH(email) を条件にして検索
-- 結果より、インデックススキャンになっている
blog_sample_db=# EXPLAIN ANALYZE
SELECT * FROM users WHERE LENGTH(email) = 20;
QUERY PLAN
------------------------------------------------------------------------------------------------------------
-----------------------
Bitmap Heap Scan on users (cost=8.17..825.45 rows=500 width=40) (actual time=0.085..0.179 rows=900 loops=1
)
Recheck Cond: (length(email) = 20)
Heap Blocks: exact=10
-> Bitmap Index Scan on idx_users_email_length (cost=0.00..8.04 rows=500 width=0) (actual time=0.074..0
.075 rows=900 loops=1)
Index Cond: (length(email) = 20)
Planning Time: 0.892 ms
Execution Time: 0.235 ms
(7 rows)
関数インデックスの詳細については、以下公式ドキュメントもご参照ください。
PostgreSQL 17.6文書 - 11.7. 式に対するインデックス
https://www.postgresql.jp/document/17/html/indexes-expressional.html
使い分けとしては、まず既存のインデックスをそのまま活用できる方法 1 を検討します。
一方で、今回の LENGTH() など方法 1 で対応できない場合やアプリ側のコードを変更できない場合などは、方法 2 の関数インデックス作成を検討しましょう。
終わりに
今回は、クエリでインデックスが使われないパターン(WHERE 句の関数条件)を確認してみました。検証を通じて、関数インデックスの必要性などを再認識できたので、とても有意義な検証でした。
細かい配慮でパフォーマンスは変化するので、今後も実際に触っていきながら PostgreSQL を知っていきたいです。
本ブログがどなたかのお役に立てば幸いです。
参考情報
