RDBのインデックスとは？WHERE条件の高速化を具体例で確認してみた
はじめに
DBの勉強を始めると「インデックス」という言葉をよく聞くと思います。私自身もなんとなく「検索が早くなるもの」くらいの知識しかなかったのですが
- 実際どれくらい速くなるのか？
- どんなクエリに効くのか？
-
EXPLAIN ANALYZEで確かめるとどのくらいの差があるのか?
この辺の違いを手を動かして試したことがありませんでした。そこで、この記事では PostgreSQL 上にテスト用テーブルを作り、20万件のデータを入れて実測しながら、
- インデックスのざっくりした仕組み
-
WHERE条件に対してどれくらい効くのか - 単一インデックスと複合インデックスの違い
を 実行計画と実行時間 を通して確認していきます。
今回のゴール
この記事のゴールは次の3つです。
-
インデックスのイメージが持てること
→ B-Treeなどのアルゴリズムの詳細は省略します -
インデックスあり／なしで何が変わるかを体感すること
→EXPLAIN ANALYZEと実行時間の違いを見る -
「どこにインデックスを貼ると効くか」の直感をつかむこと
→ 等価条件、日付の範囲、複合条件などの感覚
実験環境
ローカルの Docker + PostgreSQL で実験しました。
PostgreSQL コンテナの起動
docker run --name pg-index-lab -e POSTGRES_PASSWORD=pass -e POSTGRES_USER=postgres -e POSTGRES_DB=index_lab -p 5432:5432 -d postgres:16
psql で接続
docker exec -it pg-index-lab psql -U postgres -d index_lab
psql に入ったら、計測しやすいように \timing を ON にしておきます。
\timing on
テーブル定義とダミーデータ
今回は、EC サイトの注文データをイメージした orders テーブルを使います。
テーブル定義
ここでは、次のようなカラム構成の orders テーブルを作成しました。
-
id- 型：BIGSERIAL
- 用途：主キー（自動採番）
-
customer_id- 型：INTEGER
- 用途：顧客 ID（どのユーザーの注文か）
-
status- 型：TEXT
- 用途：注文ステータス
- 例：
pending/paid/shipped/cancelledなど
-
total_amount- 型：INTEGER
- 用途：注文金額（今回はシンプルに整数にしています）
-
ordered_at- 型：TIMESTAMP
- 用途：注文日時（このカラムで期間指定や並び替えを行う）
実際のテーブル定義は次のような SQL です。
CREATE TABLE orders (
id BIGSERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL,
total_amount INTEGER NOT NULL,
ordered_at TIMESTAMP NOT NULL
);
ダミーデータ 20 万件投入
インデックスの効果を分かりやすくするために、テーブルにはダミーデータを 20 万件 入れています。
- 行数：200,000 行
- 顧客数：最大 10,000 人（
customer_idは 1〜10,000 のランダム） -
status：4 種類のステータスからランダムに付与 -
total_amount：1,000〜10,000 円くらいのランダムな整数 -
ordered_at：過去 1 年以内 のランダムな日時
データ投入には、PostgreSQL の generate_series と random() を使っています。
INSERT INTO orders (customer_id, status, total_amount, ordered_at)
SELECT
(random() * 9999 + 1)::int AS customer_id,
(ARRAY['pending','paid','shipped','cancelled'])[floor(random() * 4 + 1)] AS status,
(random() * 9000 + 1000)::int AS total_amount,
NOW() - (random() * 365 || ' days')::interval
FROM generate_series(1, 200000) AS g;
この記事中の SQL はそのままコピーして試せるようにしてあるので、
ぜひ自分の環境でも再現してみてください。
そもそもインデックスとは？
本の「索引」と同じ発想
よく使われる例えではありますが、インデックスは大量の行の中から「欲しい行」を素早く見つけるための索引用データ構造です。
- テーブル本体
→ データの「本文」 - インデックス
→ 特定の列（例:customer_id,ordered_at）だけを
ソートされた形で持っておく「索引」
本を読むときに
- 1 ページ目から最後まで全部読む（= 全件走査）
- 巻末の索引から該当ページを一気に探す（= インデックススキャン）
という違いがあるのと同じイメージです。
多くの RDB では B-Tree で実装されている
PostgreSQL を含む多くの RDB では、インデックスの中身は
B-Treeと呼ばれる「ソート済みの木構造」で管理されています。
-
customer_idにインデックスを貼る
→ 「customer_idの値」と「その行へのポインタ」が
ソートされた木構造に保存される -
WHERE customer_id = 1234
→ テーブル全件ではなく、木構造をたどって対象行を一気に絞り込める
上記したように本記事でアルゴリズムの詳細については省略します。
実験 1：customer_id 等価検索にインデックスを貼ってみる
まずはシンプルに、特定顧客の注文履歴を取得するクエリで実験してみます。
例：customer_id = 1234 の注文をすべて取得するクエリです。
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1234;
インデックスなし
インデックスを貼っていない状態での EXPLAIN ANALYZE の結果です。
<summary>EXPLAIN ANALYZE 結果（インデックスなし）</summary>
Seq Scan on orders (cost=0.00..3371.85 rows=739 width=56) (actual time=0.721..77.466 rows=21 loops=1)
Filter: (customer_id = 1234)
Rows Removed by Filter: 199979
Planning Time: 7.866 ms
Execution Time: 77.877 ms
Time: 99.823 ms
-
実行計画のポイント
Seq Scan on ordersRows Removed by Filter: 199979-
rows=21（ヒットした行は 21 行）
-
実行時間
- Execution Time: 約 77.9 ms
20 万行のうち21行しか欲しくないのに、20万行すべてを読みながらcustomer_id = 1234 だけを拾っている状態です。
customer_id にインデックスを作成して再計測
customer_id にインデックスを作成します。
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
同じクエリを再度 EXPLAIN ANALYZE します。
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1234;
<summary>EXPLAIN ANALYZE 結果（`customer_id` インデックスあり）</summary>
Bitmap Heap Scan on orders (cost=12.04..1448.65 rows=1000 width=56) (actual time=0.314..0.642 rows=21 loops=1)
Recheck Cond: (customer_id = 1234)
Heap Blocks: exact=21
-> Bitmap Index Scan on idx_orders_customer_id (cost=0.00..11.79 rows=1000 width=0) (actual time=0.272..0.272 rows=21 loops=1)
Index Cond: (customer_id = 1234)
Planning Time: 2.044 ms
Execution Time: 0.901 ms
Time: 5.821 ms
-
実行計画のポイント
-
Bitmap Index Scan→Bitmap Heap Scan - インデックスから
customer_id = 1234の行がどのブロックにあるかを調べて、
そのブロックだけテーブル本体を読む
-
-
実行時間
- Execution Time: 約 0.9 ms
まとめると、次のような差になりました。
- インデックスなし: 約 77.9 ms
- インデックスあり: 約 0.9 ms
およそ 80〜90 倍 の高速化です。
「特定ユーザーの検索」と customer_id インデックスの相性の良さがよく分かります。
実験 2：ordered_at で期間＋ ORDER BY したときの効果
次は、「直近 180 日分の注文を新しい順に 100 件だけ表示する」パターンです。
例：直近 180 日の注文を新しい順に 100 件取得するクエリです。
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE ordered_at >= NOW() - INTERVAL '180 days'
ORDER BY ordered_at DESC
LIMIT 100;
インデックスなし
インデックスを貼っていない状態での結果です。
<summary>EXPLAIN ANALYZE 結果（`ordered_at` インデックスなし）</summary>
Limit (cost=6820.73..6832.23 rows=100 width=31) (actual time=43.070..47.228 rows=100 loops=1)
-> Gather Merge (cost=6820.73..13554.44 rows=58554 width=31) (actual time=43.069..47.223 rows=100 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=5820.72..5967.10 rows=58554 width=31) (actual time=30.473..30.476 rows=84 loops=2)
Sort Key: ordered_at DESC
Sort Method: top-N heapsort Memory: 36kB
Worker 0: Sort Method: top-N heapsort Memory: 37kB
-> Parallel Seq Scan on orders (cost=0.00..3582.82 rows=58554 width=31) (actual time=0.085..27.523 rows=49514 loops=2)
Filter: (ordered_at >= (now() - '180 days'::interval))
Rows Removed by Filter: 50486
Planning Time: 4.540 ms
Execution Time: 47.791 ms
Time: 70.841 ms
-
実行計画のポイント
-
Parallel Seq Scan on ordersでテーブル全体を並列にフルスキャン -
Filter: ordered_at >= ...で 180 日以内に絞り込み - その後
Sort（ordered_at DESC）→Gather Merge→Limit 100
-
-
実行時間
- Execution Time: 約 47.8 ms
ordered_at にインデックスを作成して再計測
ordered_at にインデックスを作成します。
CREATE INDEX idx_orders_ordered_at
ON orders(ordered_at);
同じクエリを再実行すると、psql の計測では次のようになりました。
<summary>EXPLAIN ANALYZE 実行時の計測時間（`ordered_at` インデックスあり）</summary>
Time: 5.437 ms
- インデックスなし: 約 47.8 ms
- インデックスあり: 約 5.4 ms
おおよそ 10 倍前後 の高速化になっています。
ここから言えること
-
「直近◯日」「直近◯ヶ月」のように、日付で絞り込んで新しい順に並べたいクエリは
ordered_atインデックスと相性が良い -
ダッシュボードや一覧画面でよく使う
WHERE ordered_at >= ? ORDER BY ordered_at DESC LIMIT 100;のようなクエリは、最初からインデックスを意識しておくと効きやすいです。
実験 3：customer_id × ordered_at の複合インデックス
最後に、複合条件＋ソートのパターンを見てみます。
特定顧客の直近 1 年分の注文を、新しい順に 100 件だけ取得する
というクエリを題材にします。
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE customer_id = 1234
AND ordered_at >= NOW() - INTERVAL '365 days'
ORDER BY ordered_at DESC
LIMIT 100;
このクエリに対して、インデックス構成を変えながら
EXPLAIN ANALYZE の結果を比べてみます。
パターン A：インデックスなし
まず、すべてのインデックスを削除します。
DROP INDEX IF EXISTS idx_orders_customer_id;
DROP INDEX IF EXISTS idx_orders_ordered_at;
DROP INDEX IF EXISTS idx_orders_customer_ordered_at;
その状態で EXPLAIN ANALYZE を実行した結果がこちらです。
<summary>EXPLAIN ANALYZE 結果（インデックスなし）</summary>
Limit (cost=4877.17..4878.55 rows=12 width=31) (actual time=45.984..49.871 rows=21 loops=1)
-> Gather Merge (cost=4877.17..4878.55 rows=12 width=31) (actual time=45.983..49.867 rows=21 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=3877.16..3877.19 rows=12 width=31) (actual time=27.860..27.861 rows=10 loops=2)
Sort Key: ordered_at DESC
Sort Method: quicksort Memory: 26kB
Worker 0: Sort Method: quicksort Memory: 25kB
-> Parallel Seq Scan on orders (cost=0.00..3876.94 rows=12 width=31) (actual time=1.375..27.508 rows=10 loops=2)
Filter: ((customer_id = 1234) AND (ordered_at >= (now() - '365 days'::interval)))
Rows Removed by Filter: 99990
Planning Time: 0.867 ms
Execution Time: 49.963 ms
Time: 54.358 ms
-
実行計画のポイント
-
Parallel Seq Scan on ordersで 20 万行すべてを走査 Filter: (customer_id = 1234 AND ordered_at >= ...)- その後
Sort（ordered_at DESC）→Gather Merge→Limit
-
-
実行時間
- Execution Time: 約 49.9 ms
パターン B：customer_id 単体インデックスあり
次に、customer_id にだけインデックスを貼ります。
CREATE INDEX idx_orders_customer_id
ON orders(customer_id);
同じクエリを EXPLAIN ANALYZE した結果です。
<summary>EXPLAIN ANALYZE 結果（`customer_id` 単体インデックスあり）</summary>
Limit (cost=78.41..78.46 rows=20 width=31) (actual time=0.801..0.808 rows=21 loops=1)
-> Sort (cost=78.41..78.46 rows=20 width=31) (actual time=0.800..0.801 rows=21 loops=1)
Sort Key: ordered_at DESC
Sort Method: quicksort Memory: 26kB
-> Bitmap Heap Scan on orders (cost=4.45..77.98 rows=20 width=31) (actual time=0.134..0.476 rows=21 loops=1)
Recheck Cond: (customer_id = 1234)
Filter: (ordered_at >= (now() - '365 days'::interval))
Heap Blocks: exact=21
-> Bitmap Index Scan on idx_orders_customer_id (cost=0.00..4.45 rows=20 width=0) (actual time=0.097..0.097 rows=21 loops=1)
Index Cond: (customer_id = 1234)
Planning Time: 1.190 ms
Execution Time: 0.883 ms
Time: 4.782 ms
-
実行計画のポイント
-
Bitmap Index Scanでcustomer_id = 1234の行をインデックスから取得 -
Bitmap Heap Scanで対象ブロックだけテーブル本体を読む - その後
ordered_atでフィルタし、Sortで並べ替え
-
-
実行時間
- インデックスなし: 約 49.9 ms
-
customer_idインデックスあり: 約 0.88 ms
およそ 50 倍以上 の高速化です。
まず customer_id で対象行をかなり絞り込めているのが効いています。
パターン C：複合インデックス (customer_id, ordered_at) あり
さらに、customer_id の単体インデックスを削除し、代わりに複合インデックスを貼ります。
DROP INDEX IF EXISTS idx_orders_customer_id;
CREATE INDEX idx_orders_customer_ordered_at
ON orders(customer_id, ordered_at);
同じクエリを再度 EXPLAIN ANALYZE した際の psql の計測時間は次の通りです。
<summary>EXPLAIN ANALYZE 実行時の計測時間（複合インデックスあり）</summary>
Time: 4.615 ms
今回のデータ量と条件では、customer_id 単体インデックスとの差はそこまで大きくありませんでした。これについて少し考察してみます。
なぜ複合インデックスであまり差が出なかったのか？ 今回のデータでは、customer_id で絞り込んだ時点で対象が 21 件しかありませんでした。21 件程度のデータであれば、メモリ上でのソートやフィルタは一瞬で終わるため、複合インデックスによる「ソート処理のスキップ」などの恩恵が数値として表れにくかったと考えられます。 もし、1人の顧客が数万件の注文データを持っているようなケースであれば、複合インデックスの効果はより顕著になったはずです。
とはいえ、複合インデックスについて押さえておきたいポイントがいくつかあります。
複合インデックスで押さえておきたいポイント
-
複合インデックスは 先頭から順に効く 性質がある
-
(customer_id, ordered_at)の場合WHERE customer_id = ?WHERE customer_id = ? AND ordered_at >= ?
のようなクエリとは相性が良い
-
一方で、
WHERE ordered_at >= ?のみのクエリでは効きにくいことがある（先頭キーが違うため）
-
-
クエリの
WHEREやORDER BYを眺めて、
「どのカラムをどの順番で複合インデックスに含めるか」を決めるのが重要
今回の例だと、
- 条件:
WHERE customer_id = ? AND ordered_at >= ? - 並び順:
ORDER BY ordered_at DESC
なので、(customer_id, ordered_at) のような複合インデックスは理論的には相性が良いパターンです。
インデックスのデメリット・注意点
ここまで見てくると「じゃあ全部のカラムにインデックス貼れば最強では？」と思いがちですが、もちろんデメリットもあります。
1. 書き込みコストが増える
-
INSERT/UPDATE/DELETEのたびに- テーブル本体
- 関連するすべてのインデックス
- を更新する必要があります。
インデックスが増えるほど、書き込み系のクエリは重くなる 傾向があります。
2. ディスク容量を食う
インデックスも「データ構造」なので、テーブルとは別にディスク容量を使います。なので、大量のインデックスを貼りすぎると、ストレージを圧迫します。
3. カーディナリティが低い列には効きにくい
カーディナリティ = 「値の種類の多さ」です。
例えば status が ['pending', 'paid', 'shipped', 'cancelled'] の 4 種類しかない場合、WHERE status = 'paid' のようなクエリではテーブルの多くの行がヒットしてしまい、結局「ほぼ全部読む」ことになるので、インデックスの恩恵が少なくなってしまいます。
そのため、クエリプランナーがカーディナリティについても考慮するべきでしょう。
インデックス設計の進め方
最後に、「じゃあ実際にインデックス設計をするときはどう考えればいいの？」という視点でざっくり整理します。
1. まずはクエリから逆算する
- よく使う
WHERE条件 - よく使う
JOIN条件 - よく使う
ORDER BY
を洗い出し、それに合わせてインデックスを設計します。
例えば、
WHERE customer_id = ?WHERE customer_id = ? AND ordered_at BETWEEN ? AND ?ORDER BY ordered_at DESC
のようなクエリが多いなら、今回のように
-
customer_id単体インデックス -
ordered_at単体インデックス -
(customer_id, ordered_at)複合インデックス
などを候補として検討していきます。
2. “よく使う画面” に絞る
すべてのクエリを最適化する必要はありません。
次のような「ユーザーが頻繁に開く画面」に絞ると、コスパが良いです。
- ログイン直後のダッシュボード
- 注文履歴ページ
- 検索結果画面
- 一覧ページでのフィルタリング・ソート
まずはここで使われるクエリに対して、インデックスを優先的に貼っていくのが現実的です。
3. スロークエリを計測してから対策する
あとは計測と改善です。
- ログ
- 監視ツール
- PostgreSQL なら
pg_stat_statements
などを使って、
- 実際に時間がかかっている（回数が多い or 1 回が重い）クエリを見つける
- そのクエリについて
EXPLAIN ANALYZEしてみる - 必要に応じてインデックス追加やクエリ改善を行う
というサイクルを回すのが王道でしょう。
まとめ
この記事では、PostgreSQL 上に 20 万件の orders テーブルを用意し、
- インデックスあり / なし
- 単一インデックス / 複合インデックス
の違いを、実行計画と実測値で確認しました。
ざっくり振り返ると:
-
実験 1:
customer_id等価検索- インデックスなし: 約 77.9 ms
- インデックスあり: 約 0.9 ms
→ 特定顧客の検索で 80 倍前後の高速化
-
実験 2:
ordered_atの範囲 +ORDER BY+LIMIT- インデックスなし: 約 47.8 ms
- インデックスあり: 約 5.4 ms
→ 日付ベースの「最近のデータ一覧」系クエリで 10 倍前後の高速化
-
実験 3:
customer_id × ordered_atの複合条件- インデックスなし: 約 50 ms
-
customer_idインデックスあり: 約 0.88 ms -
(customer_id, ordered_at)複合インデックスあり: 数 ms 台（今回の条件では差は小さめ）
→ 「どのカラムをどの順番で複合インデックスに含めるか」という感覚を掴める
インデックスは、
- クエリパターン
- データ量とカーディナリティ
- 読み取り／書き込みのバランス
を見ながら設計していく必要がありますが、
実際に EXPLAIN ANALYZE を回してみると一気に理解が進みます。
この記事で使ったテーブル構成やダミーデータ生成、クエリは、
そのまま（あるいは少し調整して）自分の環境でも再現できるはずなので、
ぜひローカルで遊びながら、自分なりのインデックス設計の感覚を掴んでみてください。
Discussion