仕組みの復習
10万行を相手にする
ここまでの章では、行数が数件のテーブルを扱ってきました。数件なら、どんな書き方をしても一瞬で終わります。設計の良し悪しが速度に出るのは、行数が増えてからです。
この章では10万行の注文テーブルを相手にします。実務では珍しくない規模です。
索引が無いときの仕事量
索引が無い状態で「顧客 777 番の注文」を探すと、データベースは先頭から最後まで全部の行を見て、条件に合うものを拾います。10万行あれば10万行見ます。これを全表走査、実行計画では Seq Scan と呼びます。
EXPLAIN SELECT * FROM orders WHERE customer_id = 777;Seq Scan on orders
Filter: (customer_id = 777)Filter は「全部の行を見てから、条件で捨てている」という意味です。5件を取り出すために99995件を捨てています。
B-tree が何をしているか
索引を作ると、その列の値が並んだ状態で別に保持されます。使われるのは B-tree という木構造です。
辞書を引くのと同じで、並んでいれば「探す範囲を半分に絞る」を繰り返せます。10万件から目的の値にたどり着くまで、おおよそ十数回の比較で済みます。10万回と十数回、これが索引の効き目です。
CREATE INDEX orders_customer_id_idx ON orders (customer_id);作り直したあとの計画は変わります。
Bitmap Heap Scan on orders
Recheck Cond: (customer_id = 777)
-> Bitmap Index Scan on orders_customer_id_idx
Index Cond: (customer_id = 777)注目するのは Index Cond です。条件が索引に渡っている、つまり索引の側で絞り込めているという印です。さきほどの Filter とは意味がまったく違います。
統計情報を更新する
どの索引を使うかは、データベースが統計情報を見て自分で決めます。統計が古いと判断を誤るので、大量にデータを入れたあとや索引を作ったあとには ANALYZE を打ちます。
ANALYZE orders;本番では自動で走る仕掛けがありますが、作った直後に測るときは自分で打つのが確実です。
手を動かす
索引の無い状態の計画を見てから索引を作り、計画が変わることを確かめます。
テーブル構造
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_pkey |