インデックスの代償
読みが速くなる裏で何が起きているか
索引は魔法ではありません。読みを速くする代わりに、別のところで確実に損をしています。損の中身を知らないと、索引を増やす判断ができません。
書き込みが重くなる
索引は、テーブルとは別に持っている並んだ表です。テーブルの行が変われば、索引の側も同じだけ書き換えなければなりません。
| 操作 | 索引が5本あるときに起きること |
|---|---|
| INSERT | 表に1行 + 索引5本すべてに追加 |
| UPDATE | 索引の列を変えると、全部の索引に新しい項目を足す (変えなければ足さずに済むことがある) |
| DELETE | 表の1行を消す印を付ける (行と索引の項目は後で VACUUM が片付ける) |
索引を1本増やすということは、すべての書き込みに仕事を1つ足すということです。読みが1本速くなる代わりに、書き込みが全部わずかに遅くなります。
書き込みが多いテーブルほど、この代償が重くなります。
容量を食う
索引はデータの複製なので、当然その分の容量を使います。列の多い複合インデックスを何本も持つと、索引の合計がテーブル本体より大きくなることも珍しくありません。
容量はディスクだけの問題ではありません。よく使う部分はメモリに載せて使うので、索引が膨らむと本体のためのメモリが減ります。
使われていない索引は害になる
ここから導かれる結論は単純です。
使われていない索引は、書き込みを遅くし、容量を食うだけの存在。
ありがちなのは、過去に「念のため」で作られた索引、複合インデックスに吸収されて不要になった単独の索引、そして仕様変更で消えたクエリのための索引です。
本番の PostgreSQL では pg_stat_user_indexes の idx_scan を見ると、その索引が何回使われたかが分かります。長期間0のものは落とす候補です。
落とすときも慎重に
ただし落とす前に確認します。月次バッチでしか使われない索引は、普段の数字では0に見えます。見ている期間が業務の周期を覆っているかを確かめてから落とします。
検索にも一意性にも使われていない索引は DROP INDEX <名前>; で落とせます (PRIMARY KEY と UNIQUE 制約の索引は落とせません)。作り直しはできるので、削除そのものは取り返しがつく操作です。
用意済みの一括投入を読む
generate_series は連続した整数を行として作ります。% は割り算の余りです。
SQL クエリ
INSERT INTO reading_logs (id, library_id)
SELECT 500 + n, 1 + (n % 2)
FROM generate_series(1, 3) AS n;この例は id が501から503の3行を作り、library_id を2、1、2にします。DATE に整数を足すとその日数だけ先の日付になり、ARRAY で作った配列は [1] から要素を選べます。演習ではこの投入処理と件数を並べる副問い合わせは用意済みです。
手を動かす
使われていない索引を落としてから、まとめて書き込みを行います。
orders には索引が4本あります。設計を見直したところ、実際に流れるクエリで使われているのは customer_id と ordered_on の2本だけでした。
- 使われていない索引
orders_store_id_idxとorders_status_idxを落とす。DROP INDEX <名前>;の形 - 1000件の注文をまとめて追加する。次の文をそのまま使ってよい
SQL クエリ
INSERT INTO orders
SELECT 100000 + 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, 1000) AS g;仕上げに、orders に残っている索引の本数 index_count と、orders の行数 row_count を1行2列で取り出してください。
大量投入と最終集計は用意済みです。利用されていない2本を選んで削除し、customer_id = 777 の検索を EXPLAIN して残した索引を観察します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 3 行を補います。確認用の SQL は用意してあります。
テーブル構造
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_customer_id_idx ON orders (customer_id);
CREATE INDEX orders_ordered_on_idx ON orders (ordered_on);
CREATE INDEX orders_store_id_idx ON orders (store_id);
CREATE INDEX orders_status_idx ON orders (status);
ANALYZE orders;
期待される出力
| index_count | row_count |
|---|---|
| 3 | 101000 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い