どこに張るか
索引は「全部に張る」ものではない
索引は速くする仕掛けですが、増やせば増やすほど良いものではありません。次のレッスンで見るとおり、書き込みが重くなり、容量も食います。
そこでどこに張るかを決める根拠が要ります。根拠になるのは、テーブルの形ではなく実際に流れるクエリです。
索引は、テーブルを見て決めるのではなく、クエリを見て決める。
候補になる3か所
流れるクエリを並べて、次の3か所に出てくる列を候補にします。
| 場所 | 効き方 | 例 |
|---|---|---|
WHERE の絞り込み | 見る行を減らす | WHERE customer_id = 777 |
JOIN の結合条件 | 相手の行を探す回数を減らす | ON o.store_id = s.id |
ORDER BY の並び替え | 並べ替える作業を省ける | ORDER BY ordered_on DESC |
ORDER BY が候補になるのは意外に思えるかもしれません。索引はもともと並んだ状態を持っているので、並べ替えそのものを省略できるからです。
絞り込めない列には張らない
もう1つの基準が、その列でどれだけ行が減るかです。
性別のように値が2種類しかない列に索引を張っても、半分にしか減りません。半分残るなら、索引を経由するより最初から全部読んだほうが速い、という判断になることさえあります。
| 列の性質 | 索引の価値 |
|---|---|
| 値の種類が多い(顧客ID、メールアドレス) | 高い |
| 値の種類が少ない(状態フラグ、性別) | 低い |
値の種類が多いことをカーディナリティが高いと言います。カーディナリティが高い列ほど索引が効きます。
外部キーには張る
外部キー制約を作っても、参照する側の列には索引が自動では作られません。主キー側にしか索引が無い状態です。
ところが実務では、外部キーの列は JOIN と WHERE の両方で毎回使われます。orders.store_id で店舗を絞る、店舗と結合する、どちらもよくあります。
外部キーの列は索引の第一候補、と覚えてしまってよい部分です。
手を動かす
実際に流れる3本のクエリを見て、索引を張る列を決めます。
テーブル構造
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;
ANALYZE orders;
期待される出力
| indexname |
|---|
| orders_customer_id_idx |
| orders_ordered_on_idx |
| orders_pkey |
| orders_store_id_idx |