効かないパターン
張ったのに速くならない
索引を作ったのに速度が変わらない、という相談はとても多いものです。原因のほとんどは、クエリの書き方が索引を使えない形になっていることです。
索引は、列の値がそのまま並んだ表です。だから列の値そのものと突き合わせられる条件でしか絞り込めません。
列に関数をかけると使えない
SQL クエリ
SELECT * FROM orders WHERE upper(status) = 'PAID';索引に並んでいるのは paid であって PAID ではありません。データベースは全部の行について upper() を計算してみないと、条件に合うか判断できません。
計画にはこう出ます。
Seq Scan on orders
Filter: (upper(status) = 'PAID'::text)索引があるのに使われず、表を全部読んで Filter で絞っています。索引の列に関数をかけると、絞り込みには使えません。 計画に索引の名前が出ても Index Cond が無ければ、同じ状態です。
索引の名前が出ているかではなく、
Index Condがあるかを見る。
これが実行計画を読むときの一番大事な癖です。
直し方は2つ
条件の側を直す。 WHERE status = 'paid' と書けるなら、それが一番です。
式に索引を張る。大文字と小文字を区別しない検索が業務上どうしても必要なら、式そのものに索引を作れます。
SQL クエリ
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 は使えない
SQL クエリ
SELECT * FROM orders WHERE status LIKE '%aid%';索引は先頭から並んでいるので、先頭が決まっていないと範囲を絞れません。電話帳で「途中に『やま』が入る名字」を探すのと同じで、全部めくることになります。
| 書き方 | 索引 |
|---|---|
LIKE 'ship%' (前方一致) | 使える |
LIKE '%aid%' (中間一致) | 使えない |
LIKE '%ped' (後方一致) | 使えない |
これは普通の B-tree 索引の話です。中間一致が業務要件なら、LIKE ではなく pg_trgm の索引や全文検索用の索引を使う、というのが正しい対処です。索引の形と検索の形を合わせるという一点に尽きます。
手を動かす
索引が使えない書き方を試し、関数インデックスで解消します。
索引が効かない書き方を確かめて、直します。
EXPLAIN SELECT count(*) FROM orders WHERE upper(status) = 'PAID';を打つ。orders_status_idxの名前は出るがIndex Condが無いことを見るEXPLAIN SELECT count(*) FROM orders WHERE status LIKE '%aid%';を打つ。全表走査になることを見るupper(status)に関数インデックスorders_status_upper_idxを作る。CREATE INDEX <名前> ON orders (upper(status));の形ANALYZE orders;を打つ- 手順1と同じ
EXPLAINをもう一度打ち、Index Condが出ることを確かめる
仕上げに、pg_indexes から tablename が 'orders' の indexname を、indexname の順に取り出してください。
テーブル構造
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 |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い