2章: where句
9. LIKEフィルタに対するインデックス
1. ワイルドカード位置がすべてを決める
-
LIKE 'abc%'（前方一致）
➡ B-treeでINDEX RANGE SCAN可能（インデックス有効） -
LIKE '%abc'（後方一致）
➡ B-treeでは開始位置が分からず 通常はインデックス不可 -
LIKE '%abc%'（部分一致／「全文検索的」）
➡ B-treeでは基本的に インデックス不可／フルスキャン
2. アクセス述語 vs フィルタ述語（重要）
- アクセス述語： インデックス走査の開始・終了点を決める（スキャン範囲を狭める）
- フィルタ述語： リーフノード走査後に評価される（スキャン範囲は狭めない）
➡ LIKE 式は「最初のワイルドカードの前まで」がアクセス述語、それ以降はフィルタ述語になる。
3. バインドパラメータとの関係
-
WHERE col LIKE :pのとき、最適化段階で:pの中身が不明なため多くのDBは 「先頭ワイルドカードは無い（前方一致）」と仮定する
- 実際に
:p = '%foo%'が渡されると仮定(foo%)と実態(%foo%)がズレ、期待どおりにインデックスが使われない／実行計画が合わない問題が発生する
- 直書きリテラルだとDBは正しく判断するが、毎回別SQL扱いになり最適化コストとSQLインジェクションリスクが増す
4. 対策（性能・安全性を両立する選択肢）
-
最も簡単で確実に高速化：
検索要件が前方一致にできるならabc%を使う（アプリ側で入力を整形）
-
全文／部分一致が必要な場合：
専用の全文検索インデックスを使う（Oracle Text、Postgres GIN/pg_trgm、外部全文検索エンジン）
-
後方一致（%TERM）を高速化したい場合：
文字列を反転して関数インデックスを作る
（reverse(col)に関数インデックスを張り、reverse(col) LIKE 'MRET%'にする）
-
SQL設計の工夫：
検索意図ごとにSQLを分けて（前方一致用・部分一致用など）明示的に使い分けることで、バインドを保ちつつ最適化ミスを避ける
-
避けるべき：
漠然とLIKE :pを渡して「DBが何でも最適化してくれる」と期待すること
5. DB固有の注意点
PostgreSQL は、バインドパラメータ付き LIKE の場合
“前方一致である”とは仮定せず、”部分一致である”と仮定するため、B-tree インデックスが使用されない。