は、目的の行を速く見つけるための索引です。本の巻末索引と同じで、全ページをめくる代わりに索引を引くことで速くなります。
ただし本の索引と違い、内容が変わるたびに索引も作り直されます。読み取りを速くする代わりに書き込みを遅くする、という交換が常に発生します。この回はその判断軸を扱います。
1. 何と交換しているのか
索引を1つ足すと、次のものを払います。貼る判断のたびに、この代償と釣り合うかを考える必要があります。
| 払うもの | 影響 |
|---|---|
| 書き込みの速度 | 挿入・更新・削除のたびに索引も更新する |
| 保存容量 | 索引はデータとは別に領域を消費する |
| 判断の複雑さ | 候補が増えるほど、DBが選択を誤る余地も増える |
| 保守の対象 | 設計変更のたびに、索引が妥当かを見直す必要がある |
1番目が最も直接的です。書き込みが多い表に索引を増やすと、書き込みが目に見えて遅くなります。読み取りが主体の表と、書き込みが主体の表では、貼ってよい本数の感覚が変わります。索引が増えるほど書き込み時の も起きやすくなるため、同時実行が多い環境では影響が二重になります。
初期データの投入やバッチでの一括登録では、索引を落としてから投入し、後で作り直すほうが速いことがあります。1行ごとに索引を更新するより、まとめて作るほうが効率が良いためです。
2. 何を基準に貼るか
貼る候補を決める基準は、実際に実行されているクエリです。設計書やテーブル定義を眺めて決めるものではありません。
- 1実行頻度が高いクエリを集める — 遅いクエリより、頻度の高いクエリを先に見る
- 2その絞り込み条件を確認する — WHERE と JOIN の条件が索引の候補になる
- 3絞り込みの効きを見る — その条件で全体の何割まで絞れるか
- 4並べ替えの条件も見る — ORDER BY を索引で賄えると並べ替えが不要になる
3番目が判断の核心です。その条件で件数が大きく減らないなら、索引の効果は薄い。極端な例として、値が2種類しかない列（有効/無効など）に索引を貼っても、半分にしか絞れません。索引を辿る手間のほうが上回ることがあります。
-- その条件で、全体の何割まで絞れるかを見るSELECT COUNT(*) AS 全件, COUNT(*) FILTER (WHERE status = 'paid') AS 該当, ROUND(100.0 * COUNT(*) FILTER (WHERE status = 'paid') / NULLIF(COUNT(*), 0), 1) AS 割合FROM orders;
-- 値の種類数を見る（少なすぎる列は索引の効果が薄い）SELECT COUNT(DISTINCT status) AS 種類数, COUNT(*) AS 全件FROM orders;1%まで絞れるなら索引は強く効きます。50%までしか絞れないなら、 と大差ない、あるいは遅くなる可能性すらあります。割合を確認せずに貼ると、効かない索引が増えます。
ただし、この割合はデータの偏りによって変わります。全体では 50% でも、特定の顧客に限れば 0.1% ということは珍しくありません。 の見積もりが実際と乖離する原因の多くはここにあり、平均的な分布を前提に判断すると外します。代表的なケースだけでなく、極端なケースでも確認する必要があります。
3. 複合索引の列順
複数列の索引は、先頭の列から順にしか使えません。電話帳が「姓→名」の順で並んでいるとき、姓だけでは引けても名だけでは引けないのと同じです。
| 索引 (a, b, c) | 使えるか |
|---|---|
| a で絞る | 使える |
| a と b で絞る | 使える |
| a と b と c で絞る | 使える |
| b だけで絞る | 使えない |
| b と c で絞る | 使えない |
| a と c で絞る | a の部分だけ使える |
順序の決め方には定石があります。等値で絞る列を先に、範囲で絞る列を後に置きます。範囲条件が入ると、それ以降の列は索引の並びとして使えなくなるためです。
-- よくある条件: status は等値、created_at は範囲SELECT * FROM ordersWHERE status = 'paid' AND created_at >= '2026-09-01';
-- 良い順序: 等値 → 範囲CREATE INDEX idx_orders_status_created ON orders (status, created_at);
-- 悪い順序: 範囲が先だと、status での絞り込みに索引を活かせない-- CREATE INDEX idx_orders_created_status ON orders (created_at, status);4. 索引だけで完結させる
もう一段の工夫として、必要な列をすべて索引に含めてしまうという手があります。索引を読むだけで答えが出るので、表本体を読みに行く必要がなくなります。
効果は大きいものの、索引が太るという代償があります。含める列が増えるほど容量を消費し、書き込みも重くなる。頻度が非常に高く、取得する列が少ないクエリに限って使うのが妥当な線です。
またこの手法は、列の追加に弱いという性質があります。表示項目が1つ増えただけで索引だけでは完結しなくなり、性能が急に落ちる。落ちたときに原因が分かりにくいのも難点です。使うなら、なぜその列を索引に含めているのかを必ず書き残してください。書いていないと、後任者が「不要な列だ」と判断して外します。
5. 貼ってはいけない場合
逆に、貼らないほうがよい場合を挙げます。索引を増やせば速くなるという思い込みが最も事故を生みます。
- 書き込みが圧倒的に多い表 — ログの記録先など。読み取りは別の場所へ複製する選択もある
- 値の種類が極端に少ない列 — 絞り込めないので効果が薄い
- 件数が少ない表 — 全件読んでも一瞬。索引の維持コストだけが残る
- 既存の索引で代替できる場合 — 先頭が一致する複合索引があれば、単独索引は不要なことが多い
- 将来使うかもしれない、という理由 — 使われない索引は負債にしかならない
4番目は見落とされがちです。`(a, b)` の索引があるなら、`a` 単独の索引はたいてい不要です。似た索引が並んでいる表は、整理の余地があります。
6. 使われていない索引を剥がす
索引は増える一方になりがちです。追加は簡単で、削除は勇気が要るからです。しかし使われていない索引は、書き込みを遅くするだけの存在です。
SELECT schemaname, relname AS テーブル, indexrelname AS 索引, idx_scan AS 使用回数, pg_size_pretty(pg_relation_size(indexrelid)) AS サイズFROM pg_stat_user_indexesWHERE idx_scan = 0ORDER BY pg_relation_size(indexrelid) DESC;使用回数がゼロのまま一定期間が過ぎた索引は削除候補です。ただし確認すべき例外があります。月次や年次でしか動かない重要な処理が使っている可能性、そして一意性の制約として機能している場合です。後者を削ると、性能ではなくデータの整合性が壊れます。
データベースによっては、索引を削除せず一時的に無効化できます。無効化して問題が出ないことを確認してから削除すれば、切り戻しが容易です。大きな表の索引は作り直しに時間がかかるため、この手順が効きます。
索引は読み取りの速さと書き込みの速さの交換。貼る基準は実際に実行されているクエリと、その条件での絞り込みの効き。複合索引は等値を先、範囲を後。似た索引は統合でき、使われていない索引は剥がす。次回は、アプリ側で最も頻出する構造的問題を扱います。
この記事の感想を教えてください
あなたの 1 クリックで、本当にこの記事は更新されます。「もっと詳しく」「続編希望」が一定数集まった記事は、 ふくふくが 実際に内容を拡充したり続編記事を公開 します。 送信したリアクションはお使いのブラウザに記録され、再カウントされません。