性能検証
クーポンにも索引設計をする
構造と制約が固まったので、最後に性能です。第6章と同じく、テーブルではなく流れるクエリから決めます。
クーポン機能で流れるクエリは次の3本です。
| # | クエリ | 絞り込み |
|---|---|---|
| 1 | この注文にクーポンが使われたか | coupon_usages |
| 2 | この顧客が使ったクーポンの一覧 | coupon_usages |
| 3 | まだ有効なクーポンの一覧 | coupons.expires_on の範囲 |
制約が索引を連れてくる
ここで第6章の重なりの話が効いてきます。一意制約を作ると、その裏で索引が自動的に作られます。重複を検査するには並んだ状態が必要だからです。
前のレッスンで作った制約を並べると、次のようになります。
| 制約 | 生まれる索引 | どのクエリに効くか |
|---|---|---|
coupon_usages_order_uk (order_id) | order_id の索引 | クエリ1 |
coupon_usages_customer_uk (coupon_id, customer_id) | 複合索引 | 左端が決まらないので、読む範囲を狭めにくい |
クエリ1のための索引は、すでに存在します。改めて作る必要はありません。
左端優先がここでも効く
クエリ2は customer_id だけで絞ります。coupon_usages_customer_uk は (coupon_id, customer_id) の順なので、左端が coupon_id です。customer_id は右側なので、この索引では読む範囲を狭めにくくなります。単独の索引が要るかは、EXPLAIN で確かめてから作ります。
したがって customer_id 単独の索引が別に要ります。
一意制約が索引を兼ねるかどうかは、列の順番で決まる。
これが設計表を作らないと見落とす部分です。制約の一覧を見ただけでは、どのクエリが救われてどれが救われないかは分かりません。
有効期限は範囲で絞る
クエリ3は WHERE expires_on >= CURRENT_DATE のような範囲条件です。範囲でも索引は効くので、expires_on に索引を作ります。
ここで第6章の落とし穴を思い出します。WHERE date_trunc('month', expires_on) = ... のように関数をかけて書くと索引が使えません。範囲で書けるものは範囲で書く、を守ります。
手を動かす
足りない索引だけを作り、3本のクエリの計画を確かめます。
クーポンの索引を仕上げます。すでにある一意制約の索引で足りるものは、作りません。
coupon_usagesのcustomer_idに索引coupon_usages_customer_id_idxを作るcouponsのexpires_onに索引coupons_expires_on_idxを作るANALYZE coupon_usages;とANALYZE coupons;を打つ- 次の3本の
EXPLAINを打ち、いずれもIndex Condが出ることを確かめる
SQL クエリ
EXPLAIN SELECT * FROM coupon_usages WHERE order_id = 5000;
EXPLAIN SELECT * FROM coupon_usages WHERE customer_id = 777;
EXPLAIN SELECT * FROM coupons WHERE expires_on >= DATE '2026-06-01';order_id の索引は coupon_usages_order_uk がすでに持っているので、新しくは作りません。
仕上げに、pg_indexes から tablename が 'coupon_usages' の indexname を、indexname の順に取り出してください。
テーブル構造
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
customer_id integer NOT NULL REFERENCES customers (id),
total integer NOT NULL
);
CREATE TABLE coupons (
id integer PRIMARY KEY,
code text NOT NULL,
discount_amount integer NOT NULL,
expires_on date NOT NULL,
CONSTRAINT coupons_code_uk UNIQUE (code),
CONSTRAINT coupons_discount_ck CHECK (discount_amount > 0)
);
CREATE TABLE coupon_usages (
id integer PRIMARY KEY,
coupon_id integer NOT NULL REFERENCES coupons (id),
order_id integer NOT NULL REFERENCES orders (id),
customer_id integer NOT NULL REFERENCES customers (id),
discounted_amount integer NOT NULL,
CONSTRAINT coupon_usages_order_uk UNIQUE (order_id),
CONSTRAINT coupon_usages_customer_uk UNIQUE (coupon_id, customer_id)
);
INSERT INTO customers
SELECT g, '顧客' || g FROM generate_series(1, 20000) AS g;
INSERT INTO orders
SELECT g, 1 + (g % 20000), 1000 + (g % 50000)
FROM generate_series(1, 100000) AS g;
INSERT INTO coupons
SELECT g, 'CODE' || g, 500 + (g % 30) * 100, DATE '2026-01-01' + (g % 366)
FROM generate_series(1, 300) AS g;
INSERT INTO coupon_usages
SELECT g, 1 + (g % 300), g, 1 + (g % 20000), 500
FROM generate_series(1, 10000) AS g;
ANALYZE customers;
ANALYZE orders;
ANALYZE coupons;
ANALYZE coupon_usages;
期待される出力
| indexname |
|---|
| coupon_usages_customer_id_idx |
| coupon_usages_customer_uk |
| coupon_usages_order_uk |
| coupon_usages_pkey |
ヒント
まだ開いていません
付箋は 3 枚。下の段ほど答えに近づきます
ヒント 2
ヒント 3答えに近い