インデックスの代償
読みが速くなる裏で何が起きているか
索引は魔法ではありません。読みを速くする代わりに、別のところで確実に損をしています。損の中身を知らないと、索引を増やす判断ができません。
書き込みが重くなる
索引は、テーブルとは別に持っている並んだ表です。テーブルの行が変われば、索引の側も同じだけ書き換えなければなりません。
| 操作 | 索引が5本あるときに起きること |
|---|---|
| INSERT | 表に1行 + 索引5本すべてに追加 |
| UPDATE | 変更した列に関わる索引を更新 |
| DELETE | 表から1行 + 索引5本すべてから削除 |
索引を1本増やすということは、すべての書き込みに仕事を1つ足すということです。読みが1本速くなる代わりに、書き込みが全部わずかに遅くなります。
書き込みが多いテーブルほど、この代償が重くなります。
容量を食う
索引はデータの複製なので、当然その分の容量を使います。列の多い複合インデックスを何本も持つと、索引の合計がテーブル本体より大きくなることも珍しくありません。
容量はディスクだけの問題ではありません。よく使う部分はメモリに載せて使うので、索引が膨らむと本体のためのメモリが減ります。
使われていない索引は害でしかない
ここから導かれる結論は単純です。
使われていない索引は、書き込みを遅くし、容量を食うだけの存在。
ありがちなのは、過去に「念のため」で作られた索引、複合インデックスに吸収されて不要になった単独の索引、そして仕様変更で消えたクエリのための索引です。
本番の PostgreSQL では pg_stat_user_indexes の idx_scan を見ると、その索引が何回使われたかが分かります。長期間0のものは落とす候補です。
落とすときも慎重に
ただし落とす前に確認します。月次バッチでしか使われない索引は、普段の数字では0に見えます。見ている期間が業務の周期を覆っているかを確かめてから落とします。
索引は DROP INDEX <名前>; で落とせます。作り直しはいつでもできるので、削除そのものは取り返しがつく操作です。
手を動かす
使われていない索引を落としてから、まとめて書き込みを行います。
テーブル構造
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 |