効かないパターン
張ったのに速くならない
索引を作ったのに速度が変わらない、という相談はとても多いものです。原因のほとんどは、クエリの書き方が索引を使えない形になっていることです。
索引は、列の値がそのまま並んだ表です。だから列の値そのものと突き合わせられる条件でしか絞り込めません。
列に関数をかけると使えない
SELECT * FROM orders WHERE upper(status) = 'PAID';索引に並んでいるのは paid であって PAID ではありません。データベースは全部の行について upper() を計算してみないと、条件に合うか判断できません。
計画にはこう出ます。
Bitmap Index Scan on orders_status_idx索引の名前が出ているので効いていそうに見えます。しかしよく見ると Index Cond がなく、上の階層に Filter があります。索引を読んではいるが、絞り込みには使えていない状態です。
索引の名前が出ているかではなく、
Index Condがあるかを見る。
これが実行計画を読むときの一番大事な癖です。
直し方は2つ
条件の側を直す。 WHERE status = 'paid' と書けるなら、それが一番です。
式に索引を張る。 大文字と小文字を区別しない検索が業務上どうしても必要なら、式そのものに索引を作れます。
CREATE INDEX orders_status_upper_idx ON orders (upper(status));これで upper(status) = 'PAID' に Index Cond が付きます。関数インデックスと呼びます。
同じ話が日付にも当てはまります。WHERE date_trunc('month', ordered_on) = ... は索引が使えません。WHERE ordered_on >= ... AND ordered_on < ... と範囲で書き直せば使えます。関数をかけるのではなく、範囲に直せないかを先に考えます。
中間一致の LIKE は使えない
SELECT * FROM orders WHERE status LIKE '%aid%';索引は先頭から並んでいるので、先頭が決まっていないと範囲を絞れません。電話帳で「途中に『やま』が入る名字」を探すのと同じで、全部めくることになります。
| 書き方 | 索引 |
|---|---|
LIKE 'ship%' (前方一致) | 使える |
LIKE '%aid%' (中間一致) | 使えない |
LIKE '%ped' (後方一致) | 使えない |
全文検索が業務要件なら、LIKE ではなく全文検索用の索引を使う、というのが正しい対処です。索引の形と検索の形を合わせるという一点に尽きます。
手を動かす
索引が使えない書き方を試し、関数インデックスで解消します。
テーブル構造
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL,
customer_id integer NOT NULL,
ordered_on date NOT NULL,
status text NOT NULL,
total integer NOT NULL
);
INSERT INTO orders
SELECT g,
1 + (g % 5),
1 + (g % 20000),
DATE '2026-01-01' + (g % 366),
(ARRAY['paid', 'shipped', 'cancelled'])[1 + (g % 3)],
1000 + (g % 50000)
FROM generate_series(1, 100000) AS g;
CREATE INDEX orders_status_idx ON orders (status);
ANALYZE orders;
期待される出力
| indexname |
|---|
| orders_pkey |
| orders_status_idx |
| orders_status_upper_idx |