つくる 効果検証
索引設計は表にする
新しいことは出てきません。第6章で学んだ判断基準を、多店舗ECの主要クエリに当てはめます。
索引設計を頭の中だけでやると、必ず抜けと重なりが出ます。クエリを1行にした表を作るのが確実です。
| # | クエリ | 絞り込み | 並べ替え | 張る索引 |
|---|---|---|---|---|
| 1 | 顧客の注文履歴 | customer_id | ordered_on | orders (customer_id, ordered_on) |
| 2 | 店舗の期間売上 | store_id, ordered_on の範囲 | 無し | orders (store_id, ordered_on) |
| 3 | 商品ごとの販売数 | product_id | 無し | order_items (product_id) |
表にすると、判断の根拠がそのまま残ります。半年後に「この索引は何のためか」と聞かれたときに答えられる状態が、良い索引設計です。
重なりを消す
表ができたら、重なりを探します。クエリ1と2はどちらも orders を絞りますが、左端の列が違うので1本にまとめることはできません。左端優先の性質から、(customer_id, ordered_on) は store_id の絞り込みには使えないからです。
一方、store_id 単独の索引は不要です。クエリ2の複合インデックスが左端で兼ねてくれます。兼ねられるものは兼ねさせ、兼ねられないものは分ける、という判断になります。
並べ替えを索引に任せる
クエリ1は ordered_on で並べ替えます。(customer_id, ordered_on) の索引があると、絞り込んだ結果がすでに ordered_on の順に並んでいるので、並べ替えの作業そのものが要らなくなります。
複合インデックスの2列目に並べ替えの列を置く、というのは覚えておく価値のある型です。
外部キーの列は忘れない
クエリ3の order_items.product_id は外部キーの列です。外部キー制約を作っても索引は自動では作られないので、明示的に張る必要があります。ここが抜けている設計は本当によく見ます。
手を動かす
設計表のとおりに3本の索引を作り、意図どおり使われることを確かめます。
テーブル構造
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
);
CREATE TABLE order_items (
order_id integer NOT NULL,
product_id integer NOT NULL,
quantity integer NOT NULL,
unit_price integer NOT NULL,
PRIMARY KEY (order_id, product_id)
);
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;
INSERT INTO order_items
SELECT g, 1 + (g % 200), 1 + (g % 3), 1000 + (g % 30000)
FROM generate_series(1, 100000) AS g;
ANALYZE orders;
ANALYZE order_items;
期待される出力
| tablename | indexname |
|---|---|
| order_items | order_items_pkey |
| order_items | order_items_product_id_idx |
| orders | orders_customer_ordered_idx |
| orders | orders_pkey |
| orders | orders_store_ordered_idx |