KDOC 580: 複合インデックスは等価条件、範囲条件の順にすると速い
この文書のステータス
- 作成
- 2026-06-06 貴島
- レビュー
- 2026-06-21 貴島
概要
- 日付範囲、外部キーの2つのフィールドで検索する
- その場合、複合インデックスにつけた順番がインデックス使用および速度に影響をもたらすか調べる
CREATE TABLE customers (
id INT PRIMARY KEY,
name TEXT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id),
ordered_at DATE NOT NULL,
amount INT NOT NULL
);
CREATE INDEX idx_cust_date ON orders (customer_id, ordered_at);
-- 顧客を1000人作成
INSERT INTO customers (id, name)
SELECT i, 'customer_' || i
FROM generate_series(1, 1000) AS i;
-- 注文を100万行作成（顧客1〜1000、日付は2024-01-01〜2026-05-11の範囲）
INSERT INTO orders (customer_id, ordered_at, amount)
SELECT
(random() * 999 + 1)::int,
'2024-01-01'::date + (random() * 860)::int,
(random() * 10000 + 100)::int
FROM generate_series(1, 1000000);
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 3
AND ordered_at BETWEEN '2026-01-01' AND '2026-03-31';
| | | |
|-------------------------------------------------------------------------------------------------------------------------+---+---|
| > CREATE TABLE customers ( | | |
| > id INT PRIMARY KEY, | | |
| > name TEXT | | |
| > ); | | |
| CREATE TABLE | | |
| | | |
| > CREATE TABLE orders ( | | |
| > id SERIAL PRIMARY KEY, | | |
| > customer_id INT NOT NULL REFERENCES customers(id), | | |
| > ordered_at DATE NOT NULL, | | |
| > amount INT NOT NULL | | |
| > ); | | |
| CREATE TABLE | | |
| | | |
| > CREATE INDEX idx_cust_date ON orders (customer_id, ordered_at); | | |
| CREATE INDEX | | |
| | | |
| > INSERT INTO customers (id, name) | | |
| > SELECT i, 'customer_' | | i |
| > FROM generate_series(1, 1000) AS i; | | |
| INSERT 0 1000 | | |
| | | |
| > INSERT INTO orders (customer_id, ordered_at, amount) | | |
| > SELECT | | |
| > (random() * 999 + 1)::int, | | |
| > '2024-01-01'::date + (random() * 860)::int, | | |
| > (random() * 10000 + 100)::int | | |
| > FROM generate_series(1, 1000000); | | |
| INSERT 0 1000000 | | |
| | | |
| > EXPLAIN ANALYZE | | |
| > SELECT * FROM orders | | |
| > WHERE customer_id = 3 | | |
| > AND ordered_at BETWEEN '2026-01-01' AND '2026-03-31'; | | |
| QUERY PLAN | | |
| Bitmap Heap Scan on orders (cost=4.74..100.08 rows=25 width=16) (actual time=0.019..0.064 rows=86.00 loops=1) | | |
| Recheck Cond: ((customer_id = 3) AND (ordered_at >= '2026-01-01'::date) AND (ordered_at <= '2026-03-31'::date)) | | |
| Heap Blocks: exact=86 | | |
| Buffers: shared hit=89 | | |
| -> Bitmap Index Scan on idx_cust_date (cost=0.00..4.74 rows=25 width=0) (actual time=0.009..0.009 rows=86.00 loops=1) | | |
| Index Cond: ((customer_id = 3) AND (ordered_at >= '2026-01-01'::date) AND (ordered_at <= '2026-03-31'::date)) | | |
| Index Searches: 1 | | |
| Buffers: shared hit=3 | | |
| Planning: | | |
| Buffers: shared hit=8 | | |
| Planning Time: 0.055 ms | | |
| Execution Time: 0.070 ms | | |
CREATE TABLE customers (
id INT PRIMARY KEY,
name TEXT
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL REFERENCES customers(id),
ordered_at DATE NOT NULL,
amount INT NOT NULL
);
CREATE INDEX idx_date_cust ON orders (ordered_at, customer_id);
-- 顧客を1000人作成
INSERT INTO customers (id, name)
SELECT i, 'customer_' || i
FROM generate_series(1, 1000) AS i;
-- 注文を100万行作成（顧客1〜1000、日付は2024-01-01〜2026-05-11の範囲）
INSERT INTO orders (customer_id, ordered_at, amount)
SELECT
(random() * 999 + 1)::int,
'2024-01-01'::date + (random() * 860)::int,
(random() * 10000 + 100)::int
FROM generate_series(1, 1000000);
EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 3
AND ordered_at BETWEEN '2026-01-01' AND '2026-03-31';
| | | |
|----------------------------------------------------------------------------------------------------------------------------+---+---|
| > CREATE TABLE customers ( | | |
| > id INT PRIMARY KEY, | | |
| > name TEXT | | |
| > ); | | |
| CREATE TABLE | | |
| | | |
| > CREATE TABLE orders ( | | |
| > id SERIAL PRIMARY KEY, | | |
| > customer_id INT NOT NULL REFERENCES customers(id), | | |
| > ordered_at DATE NOT NULL, | | |
| > amount INT NOT NULL | | |
| > ); | | |
| CREATE TABLE | | |
| | | |
| > CREATE INDEX idx_date_cust ON orders (ordered_at, customer_id); | | |
| CREATE INDEX | | |
| | | |
| > INSERT INTO customers (id, name) | | |
| > SELECT i, 'customer_' | | i |
| > FROM generate_series(1, 1000) AS i; | | |
| INSERT 0 1000 | | |
| | | |
| > INSERT INTO orders (customer_id, ordered_at, amount) | | |
| > SELECT | | |
| > (random() * 999 + 1)::int, | | |
| > '2024-01-01'::date + (random() * 860)::int, | | |
| > (random() * 10000 + 100)::int | | |
| > FROM generate_series(1, 1000000); | | |
| INSERT 0 1000000 | | |
| | | |
| > EXPLAIN ANALYZE | | |
| > SELECT * FROM orders | | |
| > WHERE customer_id = 3 | | |
| > AND ordered_at BETWEEN '2026-01-01' AND '2026-03-31'; | | |
| QUERY PLAN | | |
| Bitmap Heap Scan on orders (cost=118.94..214.28 rows=25 width=16) (actual time=0.198..0.274 rows=120.00 loops=1) | | |
| Recheck Cond: ((ordered_at >= '2026-01-01'::date) AND (ordered_at <= '2026-03-31'::date) AND (customer_id = 3)) | | |
| Heap Blocks: exact=118 | | |
| Buffers: shared hit=397 | | |
| -> Bitmap Index Scan on idx_date_cust (cost=0.00..118.94 rows=25 width=0) (actual time=0.186..0.186 rows=120.00 loops=1) | | |
| Index Cond: ((ordered_at >= '2026-01-01'::date) AND (ordered_at <= '2026-03-31'::date) AND (customer_id = 3)) | | |
| Index Searches: 90 | | |
| Buffers: shared hit=279 | | |
| Planning: | | |
| Buffers: shared hit=8 | | |
| Planning Time: 0.055 ms | | |
| Execution Time: 0.281 ms | | |
- 等価条件、範囲条件の順に複合インデックスを設定したほうが高速である
- 顧客レコードの数が少なくして1顧客あたりの返却レコード数が多い場合、インデックスの効率差よりもヒープアクセスのほうが圧倒的に大きいので実行時間があまり変わらなかった
関連
抽象。
- 複数の鍵で並べるとき、一致で絞る鍵を先に置くと、範囲で絞る対象が1か所にまとまる
- 支配的なコストが別にあると、一部を改善しても全体の時間は変わらない
関連。
- 補足: KDOC 496: 検索も結果もインデックスで完結するとIndex Only Scanになる。本体を見に行くコスト
- 前提: KDOC 497: 複合インデックスは2番めの要素だけの条件では使われない。複合インデックスの条件でスキャン方法が変わること
- 類推: KDOC 576: 『ザ・ゴール』。全体の速さは支配的な部分で決まり、ほかを改善しても変わらない例