🔍
【PostgreSQL】前方一致検索でindexが効かない理由と対策

とぴ
2025/12/17に公開
PostgreSQL
tech
こちらの記事はPREVENTアドベントカレンダーの記事です
https://adventar.org/calendars/12152

 はじめにこんにちは！PREVENTで働いているバックエンドエンジニアのとぴ（@topi_log）と申します！
少し前に「失敗から学ぶRDBの正しい歩き方 (Software Design plus)」を読みました。
https://amzn.asia/d/dYhBm2T
書籍の中で下記の文章があります。
RDBMSの検索にLIKE検索があります。（中略）このうち、標準でINDEXを利用するケースは前方一致のみです。
完全一致はindexを利用するのは知っていましたが、この文章を読み前方一致も使われるとわかりました。実際に検証してみたところ、前方一致でindexが効かなかったのです。

当記事ではその理由と対策をまとめました。

 調査内容まずは実際どのくらい速くなるのか確認してみました。

PostgreSQLではB-Treeインデックスをベースとしており、公式ドキュメントには下記のような内容があります。
パターンマッチ演算子LIKE、~を含む問い合わせでも、そのパターンが定数であり、先頭文字列を指定しているのであればB-treeインデックスを使用することができます。 例えば、col LIKE 'foo%'またはcol ~ '^foo'では使用されますが、col LIKE '%bar'では使用されません。
PostgreSQL 17文書 - インデックスの種類
完全一致と前方一致であればindexが使われるというものです。

1000万レコードを追加したデータを用いて検索速度を測定しました。

 環境Docker
PostgreSQL
ローカル
データ注入のためRuby on Railsを利用
ダミーデータでFakerを利用


 データ準備データは下記を準備しました。
indexを一切貼っていないテーブル
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR NOT NULL,
  email VARCHAR NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);
indexを貼ったテーブル
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  last_name VARCHAR NOT NULL,
  first_name VARCHAR NOT NULL,
  email VARCHAR NOT NULL,
  created_at TIMESTAMP NOT NULL,
  updated_at TIMESTAMP NOT NULL
);
CREATE INDEX idx_users_last_name ON users(last_name);
CREATE INDEX idx_users_first_name ON users(first_name);
CREATE INDEX idx_users_email ON users(email);

 測定した実行計画以下のクエリで実行計画を測定しました。
完全一致
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';
前方一致
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'test%';
後方一致
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%example.com';
部分一致
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE '%test%';

 実行速度の結果まずは実行速度の違いを見てみました。


検索タイプ
indexなし
indexあり


完全一致
763.633 ms
1.220 ms

前方一致
1167.174 ms
760.479 ms

後方一致
510.195 ms
435.000 ms

部分一致
578.106 ms
406.983 ms

完全一致はindexが貼ってあると圧倒的に早いですね！

しかし公式ドキュメントでは「先頭文字列を指定しているのであればB-treeインデックスを使用することができます」とあり、前方一致でもindexが使えるはずです。

完全一致と前方一致の実行計画をちゃんと見てみましょう。

 実行計画の詳細
 indexなし・完全一致EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

 Gather  (cost=1000.00..173849.34 rows=1 width=65) (actual time=6.583..761.794 rows=1 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..172849.24 rows=1 width=65) (actual time=481.264..729.659 rows=0 loops=3)
         Filter: ((email)::text = 'test@example.com'::text)
         Rows Removed by Filter: 3333333
 Planning Time: 0.050 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.046 ms (Deform 0.234 ms), Inlining 0.000 ms, Optimization 1.191 ms, Emission 6.026 ms, Total 9.263 ms
 Execution Time: 763.633 ms
(12 rows)
Parallel Seq Scan = フルスキャンしています。

 indexあり・完全一致EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

 Index Scan using index_users_on_email on users  (cost=0.56..8.58 rows=1 width=65) (actual time=1.046..1.047 rows=1 loops=1)
   Index Cond: ((email)::text = 'test@example.com'::text)
 Planning Time: 0.250 ms
 Execution Time: 1.220 ms
(4 rows)
Index Scan using index_users_on_email on users = indexを使ってスキャンしているのがわかります。

 indexなし・前方一致EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'test%';

 Gather  (cost=1000.00..173949.24 rows=1000 width=65) (actual time=11.656..1165.853 rows=1277 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..172849.24 rows=417 width=65) (actual time=14.400..1085.189 rows=426 loops=3)
         Filter: ((email)::text ~~ 'test%'::text)
         Rows Removed by Filter: 3332908
 Planning Time: 0.208 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.254 ms (Deform 0.441 ms), Inlining 0.000 ms, Optimization 1.238 ms, Emission 10.338 ms, Total 12.830 ms
 Execution Time: 1167.174 ms
(12 rows)
こちらもフルスキャンです。

 indexあり・前方一致EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'test%';

 Gather  (cost=1000.00..173950.31 rows=1000 width=65) (actual time=19.544..759.474 rows=1277 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on users  (cost=0.00..172850.31 rows=417 width=65) (actual time=15.009..677.442 rows=426 loops=3)
         Filter: ((email)::text ~~ 'test%'::text)
         Rows Removed by Filter: 3332908
 Planning Time: 0.364 ms
 JIT:
   Functions: 6
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 2.979 ms (Deform 1.813 ms), Inlining 0.000 ms, Optimization 3.912 ms, Emission 21.438 ms, Total 28.329 ms
 Execution Time: 760.479 ms
(12 rows)
Parallel Seq Scanとあり、indexが使われていないことがわかります。

 前方一致でindexが使われない理由公式ドキュメントを改めて読んでみると、重要な記載がありました。
しかし、データベースがCロケールを使用していない場合、パターンマッチ問い合わせのインデックス付けをサポートする特別な演算子クラスでインデックスを作成しなければなりません。
DBがCロケールを使っているかどうかでB-Treeの前方一致が使えるかどうかが変わるようです。

 DBのロケール設定を確認DBのロケール設定を確認してみました。
SELECT name, setting, context FROM pg_settings WHERE name LIKE 'lc%';
この結果が下記でした。


name
setting
context


lc_collate
en_US.UTF-8
internal

lc_ctype
en_US.UTF-8
internal

ロケール設定がen_US.UTF-8が使われており、Cロケールではありませんでした。

このため、B-Treeインデックスにおける前方一致検索でインデックスを使った検索ができていなかったのです。

 どうすれば使えるのか公式によると以下のような記載があります。
text_pattern_ops、varchar_pattern_ops、bpchar_pattern_ops演算子クラスは、それぞれ、text、varchar、char型上のB-treeインデックスをサポートします。 デフォルトの演算子クラスとの違いは、ロケール特有の照合規則に従わずに、文字同士を厳密に比較する点です。 これらの演算子クラスを、標準「C」ロケールを使用しないデータベースにおける、パターンマッチ式（LIKEやPOSIX正規表現）を含む問い合わせでの使用に適したものにします。
PostgreSQL 17文書 - 演算子クラスと演算子族
今回のケースでは、text_pattern_opsを使うとよいようです。

 試してみた結果emailに前方一致のtext_pattern_opsを付けて実行計画を確認してみました。
EXPLAIN ANALYZE SELECT * FROM users WHERE email LIKE 'test%';

 Bitmap Heap Scan on users  (cost=4.18..11.29 rows=3 width=120) (actual time=0.028..0.029 rows=0 loops=1)
   Filter: ((email)::text ~~ 'test%'::text)
   ->  Bitmap Index Scan on index_users_on_email  (cost=0.00..4.18 rows=3 width=0) (actual time=0.016..0.016 rows=0 loops=1)
         Index Cond: (((email)::text ~>=~ 'test'::text) AND ((email)::text ~<~ 'tesu'::text))
 Planning Time: 2.156 ms
 Execution Time: 0.210 ms
(6 rows)


検索タイプ
indexなし
indexあり
text_pattern_ops


前方一致
1167.174 ms
760.479 ms
0.210 ms

indexが効いていますね！

前方一致でも高速な検索ができるようになりました。

 結論公式に書いてあるままといえばそうですが、PostgreSQLにおいてデフォルトのindexを有効活用するには、ロケール設定が適切であるかを確認の上、Cロケールでない場合は前方一致用のindexを貼る必要があることがわかりました。

前方一致検索を行うカラムについては、それ用のindex（今回だとtext_pattern_ops）を指定すると効果的に使えることがわかります。

 終わりにロケール設定はDB作成時にのみ設定ができ、作成後の変更はできません。ロケールの種類までは深掘りしていませんが、DB作成時にそこも考慮できると良さそうですね。

indexを貼る時はそのカラムがどのような検索を行うかを考えて適切に貼ることで、検索速度を高速化できるとわかったので、indexの貼り方は今まで以上に意識していきたいと思います。

 参考https://www.postgresql.jp/document/17/
https://amzn.asia/d/5YLmFDV
検索タイプ	indexなし	indexあり
完全一致	763.633 ms	1.220 ms
前方一致	1167.174 ms	760.479 ms
後方一致	510.195 ms	435.000 ms
部分一致	578.106 ms	406.983 ms
name	setting	context
lc_collate	en_US.UTF-8	internal
lc_ctype	en_US.UTF-8	internal
検索タイプ	indexなし	indexあり	text_pattern_ops
前方一致	1167.174 ms	760.479 ms	0.210 ms
株式会社PREVENT開発部Publication
はじめに

調査内容

環境

データ準備

測定した実行計画

実行速度の結果

実行計画の詳細

indexなし・完全一致

indexあり・完全一致

indexなし・前方一致

indexあり・前方一致

前方一致でindexが使われない理由

DBのロケール設定を確認

どうすれば使えるのか

試してみた結果

結論

終わりに

参考

Discussion
