仕組みの復習
10万行を相手にする
ここまでの章では、行数が数件のテーブルを扱ってきました。数件なら、どんな書き方をしても一瞬で終わります。設計の良し悪しが速度に出るのは、行数が増えてからです。
この章では10万行の貸出テーブルを相手にします。実務では珍しくない規模です。
索引が無いときの仕事量
索引が無い状態で「会員 777 番の貸出」を探すと、データベースは先頭から最後まで全部の行を見て、条件に合うものを拾います。10万行あれば10万行見ます。これを全表走査、実行計画では Seq Scan と呼びます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
EXPLAIN SELECT * FROM loans WHERE member_id = 777;Seq Scan on loans
Filter: (member_id = 777)Filter は「全部の行を見てから、条件で捨てている」という意味です。5件を取り出すために99995件を捨てています。
B-tree が何をしているか
索引を作ると、その列の値が並んだ状態で別に保持されます。使われるのは B-tree という木構造です。
辞書を引くのと同じで、並んでいれば「探す範囲を半分に絞る」を繰り返せます。10万件から目的の値にたどり着くまで、おおよそ十数回の比較で済みます。10万回と十数回、これが索引の効き目です。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE INDEX loans_member_id_idx ON loans (member_id);作り直したあとの計画は変わります。
Bitmap Heap Scan on loans
Recheck Cond: (member_id = 777)
-> Bitmap Index Scan on loans_member_id_idx
Index Cond: (member_id = 777)注目するのは Index Cond です。条件が索引に渡っている、つまり索引の側で絞り込めているという印です。さきほどの Filter とは意味がまったく違います。
統計情報を更新する
どの索引を使うかは、データベースが統計情報を見て自分で決めます。統計が古いと判断を誤るので、大量にデータを入れたあとや索引を作ったあとには ANALYZE を打ちます。
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
ANALYZE loans;本番では自動で走る仕掛けがありますが、作った直後に測るときは自分で打つのが確実です。
索引一覧の確認を読む
pg_indexes は索引の一覧です。tablename は表名、indexname は索引名です。
SQL クエリ
SELECT indexname FROM pg_indexes
WHERE schemaname = 'public' AND tablename = 'borrowings'
ORDER BY indexname;主キーの索引もここに出ます。演習の一覧と索引作成前の EXPLAIN は用意済みで、作成後の EXPLAIN は自分で補います。
手を動かす
索引の無い状態の計画を見てから索引を作り、計画が変わることを確かめます。
10万行の orders が用意してあります。索引は主キーのものだけです。
EXPLAIN SELECT * FROM orders WHERE customer_id = 777;を打って、索引が無いときの計画を見るcustomer_idに索引orders_customer_id_idxを作る。CREATE INDEX <索引名> ON <表> (<列>);の形ANALYZE orders;で統計情報を更新する- もう一度同じ
EXPLAINを打って、Index Condが出ることを確かめる
仕上げに、pg_indexes から tablename が 'orders' の indexname を、indexname の順に取り出してください。
索引作成前の計画と一覧の確認は用意済みです。索引を作り、統計情報を更新し、作成後の計画を補います。 ひな形の「ここに学ぶ部分を追記する」を 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;
ANALYZE orders;
期待される出力
| indexname |
|---|
| orders_customer_id_idx |
| orders_pkey |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い