性能検証
クーポンにも索引設計をする
構造と制約が固まったので、最後に性能です。第6章と同じく、テーブルではなく流れるクエリから決めます。
クーポン機能で流れるクエリは次の3本です。
| # | クエリ | 絞り込み |
|---|---|---|
| 1 | この注文にクーポンが使われたか | coupon_usages.order_id |
| 2 | この顧客が使ったクーポンの一覧 | coupon_usages.customer_id |
| 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 は右側なので、この索引では絞り込めません。
したがって customer_id 単独の索引が別に要ります。
一意制約が索引を兼ねるかどうかは、列の順番で決まる。
これが設計表を作らないと見落とす部分です。制約の一覧を見ただけでは、どのクエリが救われてどれが救われないかは分かりません。
有効期限は範囲で絞る
クエリ3は WHERE expires_on >= CURRENT_DATE のような範囲条件です。範囲でも索引は効くので、expires_on に索引を作ります。
ここで第6章の落とし穴を思い出します。WHERE date_trunc('month', expires_on) = ... のように関数をかけて書くと索引が使えません。範囲で書けるものは範囲で書く、を守ります。
手を動かす
足りない索引だけを作り、3本のクエリの計画を確かめます。
テーブル構造
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 |