PostgreSQL インデックス設計ガイド
このドキュメントでは、PostgreSQLにおけるインデックスの仕組みと 効果的な設計方法について解説します。
目次
- インデックスの基本概念
- B-treeインデックス
- GINインデックス
- GiSTインデックス
- その他のインデックス
- 複合インデックス
- 部分インデック ス
- カバリングインデックス
- インデックスの運用
- 設計のベストプラクティス
1. インデックスの基本概念
1.1 インデックスとは
┌─────────────────────────────────────────────────────────────────┐
│ インデックスの役割 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【本の索引のようなもの】 │
│ │
│ 本で「PostgreSQL」という単語を探す場合: │
│ ❌ 全ページを順番に読む → 遅い │
│ ✅ 索引で「PostgreSQL → p.123」を見つける → 速い │
│ │
│ データベースでも同様: │
│ ❌ テーブル全体をスキャン（Seq Scan）→ 遅い │
│ ✅ インデックスで位置を特定（Index Scan）→ 速い │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Table Index (email) │ │
│ │ ┌────┬──────────────────┐ ┌────────────────────┐ │ │
│ │ │ id │ email │ │ alice@... → row 1 │ │ │
│ │ ├────┼──────────────────┤ │ bob@... → row 2 │ │ │
│ │ │ 1 │ alice@example... │ │ carol@... → row 3 │ │ │
│ │ │ 2 │ bob@example... │ │ ... │ │ │
│ │ │ 3 │ carol@example... │ └────────────────────┘ │ │
│ │ │ ...│ ... │ ↑ ソートされている │ │
│ │ └────┴──────────────────┘ │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────┘
1.2 インデックスの種類
┌─────────────────────────────────────────────────────────────────┐
│ インデックスの種類 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────────┬──────────────────────────────────────────┐ │
│ │ 種類 │ 用途 │ │
│ ├──────────────┼──────────────────────────────────────────┤ │
│ │ B-tree │ 等価・範囲検索（デフォルト、最も一般的）│ │
│ │ Hash │ 等価検索のみ（PostgreSQL 10+で改善） │ │
│ │ GIN │ 配列、JSONB、全文検索 │ │
│ │ GiST │ 地理情報、範囲型、全文検索 │ │
│ │ SP-GiST │ 不均一な分布のデータ │ │
│ │ BRIN │ 大きなテーブル、時系列データ │ │
│ └──────────────┴──────────────────────────────────────────┘ │
│ │
│ 【選択の目安】 │
│ ・通常の検索 → B-tree │
│ ・配列/JSONB → GIN │
│ ・地理情報/範囲 → GiST │
│ ・時系列の大きなテーブル → BRIN │
│ │
└─────────────────────────────────────────────────────────────────┘
1.3 インデックスのトレードオフ
┌─────────────────────────────────────────────────────────────────┐
│ インデックスのトレードオフ │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【メリット】 │
│ ✅ SELECT が高速化 │
│ ✅ ORDER BY が高速化 │
│ ✅ JOIN が高速化 │
│ │
│ 【デメリット】 │
│ ❌ INSERT が遅くなる（インデックス更新が必要） │
│ ❌ UPDATE が遅くなる（インデックス対象カラムの場合） │
│ ❌ DELETE が遅くなる（インデックス更新が必要） │
│ ❌ ディスク容量を消費 │
│ │
│ 【判断基準】 │
│ ・読み取りが多い → インデックス有効 │
│ ・書き込みが多い → インデックス最小限 │
│ ・カーディナリティが高い → インデックス有効 │
│ ・カーディナリティが低い → インデックス効果薄い │
│ │
└─────────────────────────────────────────────────────────────────┘
2. B-treeインデックス
2.1 B-treeの仕組み
┌─────────────────────────────────────────────────────────────────┐
│ B-tree 構造 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌───────────────┐ │
│ │ 50 | 100 │ ← ルートノード │
│ └───────┬───────┘ │
│ ┌──────────────┼──────────────┐ │
│ ▼ ▼ ▼ │
│ ┌───────────┐ ┌───────────┐ ┌───────────┐ │
│ │ 20 | 35 │ │ 70 | 85 │ │ 120| 150 │ ← 中間 │
│ └─────┬─────┘ └─────┬─────┘ └─────┬─────┘ │
│ │ │ │ │
│ ▼ ▼ ▼ │
│ ┌───────┐ ┌───────┐ ┌───────┐ │
│ │ Leaf │ │ Leaf │ │ Leaf │ ← リーフ │
│ │ nodes │ │ nodes │ │ nodes │ │
│ └───────┘ └───────┘ └───────┘ │
│ │
│ 【特徴】 │
│ ・ソート順を維持 │
│ ・等価検索: O(log n) │
│ ・範囲検索: O(log n + k) ※k = 結果件数 │
│ ・ORDER BY にも使える │
│ │
└─────────────────────────────────────────────────────────────────┘
2.2 B-treeが有効な操作
-- B-treeインデックスの作成
CREATE INDEX idx_users_email ON users (email);
-- 等価検索 ✅
SELECT * FROM users WHERE email = 'alice@example.com';
-- 範囲検索 ✅
SELECT * FROM users WHERE created_at >= '2024-01-01';
SELECT * FROM users WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';
-- 前方一致 ✅
SELECT * FROM users WHERE email LIKE 'alice%';
-- ソート ✅
SELECT * FROM users ORDER BY email;
-- 中間・後方一致 ❌（インデックス使用不可）
SELECT * FROM users WHERE email LIKE '%alice%';
SELECT * FROM users WHERE email LIKE '%@example.com';
-- 関数適用 ❌（通常はインデックス使用不可）
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- → 式インデックスで対応可能
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
2.3 NULLとB-tree
-- B-treeはNULLも格納できる
CREATE INDEX idx_users_deleted_at ON users (deleted_at);
-- IS NULL検索にも使える
SELECT * FROM users WHERE deleted_at IS NULL;
-- NULLの位置を制御
CREATE INDEX idx_users_deleted_at_nulls_first
ON users (deleted_at NULLS FIRST);
CREATE INDEX idx_users_deleted_at_nulls_last
ON users (deleted_at NULLS LAST);
2.4 内部構造と計算量の詳細
データ保存形式
PostgreSQL の B-tree は B+tree の派生で、データはデ ィスク上の 8KB page 単位で格納される。
┌──────────────────────────────────────────────────────────┐
│ ルートページ (8KB) │
│ ┌────────────────────────────────────────────────┐ │
│ │ Header │ Item₁ │ Item₂ │ Item₃ │ ... │ free │ │
│ └────────────────────────────────────────────────┘ │
│ 各 Item = (key値, 子ページへのポインタ) │
└──────────────────────────────────────────────────────────┘
│
┌─────────────────┼─────────────────┐
▼ ▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────┐
│ 内部ページ│ │ 内部ページ│ │ 内部ページ│
└──────────┘ └──────────┘ └──────────┘
│
┌─────────────────┼─────────────────┐
▼ ▼ ▼
┌─────────────┐ ┌─────────────┐ ┌─────────────┐
│ リーフページ │ │ リーフページ │ │ リーフページ │
│ (key + TID) │←→│ (key + TID) │←→│ (key + TID) │
└─────────────┘ └─────────────┘ └─────────────┘
↑ 葉同士は双方向リンクで連結（範囲スキャン高速化）
- エントリ単位: 1 行 = 1 エントリ。各エントリは
(キー値, TID = テーブル内タプル位置) - page サイズ: 8KB 固定。1 page に格納できるエントリ数は
8KB / (key サイズ + tid 6byte + メタ)- 例:
bigint(8 byte) のキーなら 1 page に約 400 エントリ - 例:
uuid(16 byte) のキーなら 1 page に約 250 エントリ
- 例:
- 高さの目安: ファンアウト ~250〜400 として、高さ
hで400^h行- 100 万行: 高さ 3
- 1 億行: 高さ 4
- 10 億行: 高さ 4〜5
- どんなに大きくても通常 4〜5 段で済むのが B-tree の強み
計算量の内訳
| 操作 | 計算量 | 内訳 |
|---|---|---|
等価検索 (WHERE id = ?) | O(log n) | ルート→葉まで高さぶん page を辿る |
範囲検索 (WHERE id BETWEEN ? AND ?) | O(log n + k) | 開始位置 O(log n) + 葉のリンクを辿って k 件取得 |
| ORDER BY ソート | O(k) | 葉がソート済みのため、リンクを辿るだけ |
| 挿入 | O(log n) | 適切な葉に書き込み、必要なら page split |
| 削除 | O(log n) | 該当エントリを削除（page merge は遅延） |
Page split の挙動
- 1 page の容量が満杯になると 2 page に分割される（split）
- split は親ページの参照更新を伴い、稀に再帰的に伝播
- bulk insert 時のオーバーヘッド: 主に page split + WAL 書き込み
- 対策: テーブルロード時に index を一旦 drop → bulk insert → 再作成
B-tree が有利な理由
| 特性 | 影響 |
|---|---|
| 高さが低い (4〜5 段) | 等価/範囲検索の I/O が常に少ない |
| 葉がソート順 + 双方向リンク | 範囲スキャン・ORDER BY が高速 |
| エントリが単純 (key + TID) | サイズが小さい、cache hit しやすい |
| 挿入コストが低い (O(log n)) | 書き込み多めのワークロードに向く |
3. GINインデックス
3.1 GINの仕組み
┌─────────────────────────────────────────────────────────────────┐
│ GIN (Generalized Inverted Index) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【転置インデックス】 │
│ │
│ 元データ: │
│ ┌─────┬─────────────────────────────┐ │
│ │ id │ tags │ │
│ ├─────┼─────────────────────────────┤ │
│ │ 1 │ ['java', 'spring', 'api'] │ │
│ │ 2 │ ['python', 'django', 'api']│ │
│ │ 3 │ ['java', 'android'] │ │
│ └─────┴─────────────────────────────┘ │
│ │
│ GINインデックス: │
│ ┌──────────────┬─────────────────┐ │
│ │ キー │ 行リスト │ │
│ ├──────────────┼─────────────────┤ │
│ │ 'android' │ [3] │ │
│ │ 'api' │ [1, 2] │ │
│ │ 'django' │ [2] │ │
│ │ 'java' │ [1, 3] │ │
│ │ 'python' │ [2] │ │
│ │ 'spring' │ [1] │ │
│ └──────────────┴─────────────────┘ │
│ │
│ → 'java'を含む記事を高速に検索可能 │
│ │
└─────────────────────────────────────────────────────────────────┘
3.2 GINの用途
-- 配列検索
CREATE TABLE articles (
id BIGINT PRIMARY KEY,
title TEXT,
tags TEXT[]
);
CREATE INDEX idx_articles_tags ON articles USING gin (tags);
-- 配列に特定の値を含む
SELECT * FROM articles WHERE tags @> ARRAY['java'];
-- 配列のいずれかを含む
SELECT * FROM articles WHERE tags && ARRAY['java', 'python'];
-- JSONB検索
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name TEXT,
attributes JSONB
);
CREATE INDEX idx_products_attributes ON products USING gin (attributes);
-- JSONBに特定のキーと値を含む
SELECT * FROM products
WHERE attributes @> '{"color": "red"}';
-- JSONBの特定のキーが存在
SELECT * FROM products
WHERE attributes ? 'size';
-- 全文検索
CREATE INDEX idx_articles_body_fts
ON articles USING gin (to_tsvector('english', body));
SELECT * FROM articles
WHERE to_tsvector('english', body) @@ to_tsquery('english', 'postgresql & index');
3.3 GINのオプション
-- fastupdate（デフォルト: on）
-- 更新を遅延させてバッチ処理することで書き込み性能向上
CREATE INDEX idx_articles_tags ON articles
USING gin (tags) WITH (fastupdate = on);
-- gin_pending_list_limit
-- 遅延リストの最大サイズ（デフォルト: 4MB）
CREATE INDEX idx_articles_tags ON articles
USING gin (tags) WITH (gin_pending_list_limit = 8192); -- 8MB
3.4 内部構造と計算量の詳細
データ保存形式
GIN は 2 段構造で構成される：
┌──────────────────────────────────────────────────────────┐
│ Entry Tree (B-tree 構造、キー値で索引) │
│ ┌──────────┬──────────┬──────────┬──────────┐ │
│ │ 'api' │ 'java' │ 'python' │ 'spring' │ ... │
│ └────┬─────┴────┬─────┴────┬─────┴────┬─────┘ │
└───────┼──────────┼──────────┼──────────┼─────────────────┘
▼ ▼ ▼ ▼
┌─────────┐ ┌─────────┐ ┌─────────┐ ┌─────────┐
│ Posting │ │ Posting │ │ Posting │ │ Posting │
│ List │ │ Tree │ │ List │ │ List │
│ [1,2,5] │ │(大規模) │ │ [2,7] │ │ [1] │
└─────────┘ └─────────┘ └─────────┘ └─────────┘
│
│ (posting list が大きくなると posting tree に昇格)
▼
┌─────────────────────────┐
│ Posting Tree (B-tree) │
│ 数万〜数百万 TID を保持 │
└─────────────────────────┘
- Entry Tree: B-tree 構造で ユニークなキー値を索引（K = 全テーブルのユニーク key 数）
- Posting List: 各キーに紐づく
TIDのリスト（少量なら配列、多量なら Posting Tree） - Posting Tree: posting list が 1 page (8KB) に収まらなくなった時点で B-tree に昇格（概ね数千 TID 規模）
1 行から複数エントリ
B-tree と決定的に違う点。1 行のデータから複数のインデックスエントリが生成される：
| 元データ | 生成されるエントリ |
|---|---|
tags = ['java','spring','api'] | java→TID, spring→TID, api→TID の 3 エントリ |
payload = '{"a":1,"b":2,"c":3}'::jsonb | a→TID, b→TID, c→TID + 値の分も含めて 6+ エントリ（opclass 次第） |
→ GIN index は B-tree よりサイズが大きくなる（特に JSONB の jsonb_ops）。
jsonb_path_ops opclass を使えばエントリ数を減らせる（ただし ? 演算子非対応など制約）。
計算量
| 操作 | 計算量 | 内訳 |
|---|---|---|
検索 (@>, ?, && 等) | O(log K + R) | entry tree O(log K) でキー特定 + posting (list/tree) O(R) で TID 取得 |
複数キー AND (@>) | O(M × log K + min(R)) | M キーぶん entry tree 引いて、posting list の積集合 |
| 複数キー OR (`? | `) | O(M × log K + sum(R)) |
| 範囲検索 | ❌ 不可 | GIN は等価/包含/全文検索のみ。範囲には GiST or B-tree |
| ソート対応 | ❌ 不可 | posting list は TID 順、key 値でソートされない |
| 挿入 | O(items × log K) | 1 行から複数 key を抽出 → それぞれ entry tree に挿入 |
| 挿入オーバーヘッド | btree の 数倍〜数十倍 | 1 行で複数 entry を更新するため。fastupdate で緩和 |
K= テーブル内のユニーク key 数R= 検索条件にマッチする posting list のサイズM= 検索クエリ内の key 数（例:@> '{"a":1,"b":2}'なら M=2）
fastupdate + Pending List
挿入オーバーヘッドを緩和する仕組み：
INSERT 時:
1. すぐに entry tree を 更新せず、Pending List に追記（高速）
2. Pending List がしきい値超え or VACUUM 時にまとめて entry tree に flush
- Pending List = まだ entry tree に取り込まれていない pending 更新
- 書き込み高速化: 1 件あたり O(1) の append
- トレードオフ: 検索時に entry tree + pending list の 両方をスキャンする → 検索やや遅くなる
設定で挙動を変えられる：
-- fastupdate off （即時 entry tree 更新、検索は速いが挿入遅い）
ALTER INDEX idx_articles_tags SET (fastupdate = off);
-- Pending List のサイズ上限
ALTER INDEX idx_articles_tags SET (gin_pending_list_limit = 8192); -- KB
3.5 Sequential Scan vs B-tree vs GIN 比較
データ構造・性能特性・用途を index なし (Seq Scan) も含めて一覧で比較。
📘 記号の意味
記号 意味 nテーブル全体の行数 k検索結果の件数（範囲 / ORDER BY などで取得する件数） KGIN の entry tree 内のユニークキー数（全テーブルで GIN にインデックス化されたキーの種類） RGIN の検索条件にマッチする posting list / posting tree のサイズ（該当 TID 数） MGIN の検索クエリ内のキー数（例: @> '{"a":1,"b":2}'なら M=2）log（対数）とは
log n= 「n を半分にしていって 1 になるまでの回数」と覚えると直感的。
n 半分にしていく回数 (≒ log₂ n) 8 3 回 (8 → 4 → 2 → 1) 1,024 10 回 1,000,000（100万） 約 20 回 100,000,000（1億） 約 27 回 B-tree が
O(log n)で速いのは、根 → 葉まで「分岐して絞り込む」操作が データが 100 倍になっても数回しか増えないから。二分探索のイメージ（実際はファンアウト数百なのでもっと少ない段数）。計算量の感覚（n = 100万行）:
オーダー 操作回数 実時間目安 説明 O(1)1 ~0.05ms 配列の添字アクセスのように常に一定 O(log n)~20 ~0.1ms 実質ほぼ瞬時。半分ずつ絞り込み O(n)1,000,000 ~100ms 行数に比例。全件スキャン O(n log n)~20,000,000 ~秒オーダー 行数 × log。ソート系 → O(log n) と O(n) の差はデータが増えるほど劇的に拡大。100万行なら 5万倍、1億行なら 370万倍の差。
| 観点 | Sequential Scan | B-tree | GIN |
|---|---|---|---|
| データ構造 | なし（テーブル本体を順次走査） | 階層型 B+tree | Entry Tree (B-tree) + Posting List/Tree の 2 段 |
| 追加ストレージ | 0（index なし） | 小 | 大（特に jsonb_ops、jsonb_path_ops で削減可能） |
| 1 行あたりのエントリ数 | n/a | 1 | 複数（配列要素数 / JSONB key 数） |
| 等価検索 | O(n) | ✅ O(log n) | ✅ O(log K + R) |
| 範囲検索 | O(n) | ✅ O(log n + k) | ❌ 不可 |
| 前方一致 LIKE | O(n) | ✅ ('abc%') | trigram (pg_trgm) 経由 |
| 中間/後方一致 LIKE | O(n) | ❌ | trigram 経由で対応 |
| ORDER BY | O(n log n) ※別途 Sort 必要 | ✅ O(k) | ❌ |
配列 (@>, &&) | O(n) | ❌ | ✅ |
**JSONB (@>, ?, `? | `)** | O(n) | ❌ |
全文検索 (tsvector @@) | O(n) | ❌ | ✅ (主用途) |
| 挿入コスト | 0（index 維持なし） | 低 O(log n) | 高 O(items × log K)、fastupdate で緩和 |
| 更新コスト | 0 | 低 | 高 |
| 削除コスト | 0 | 低 | 中 |
| 小規模データ（〜数百行） | しばしば最速（planner が選ぶ） | overhead 上回る場合あり | 同左 |
| 並列化 | ✅ Parallel Seq Scan | △ Parallel Bitmap Scan で部分対応 | △ 同左 |
| マルチカラム | n/a | ✅ 左端優先（順序重要） | ✅ 順序関係なし（各 key 独立） |
| NULL 対応 | n/a | ✅ | △ NULL は index されない |
| 主用途 | 集計、全件処理、データ少量 | 等価 / 範囲検索、ソート、UNIQUE | 配列、JSONB、全文検索 |
Sequential Scan が選ばれるケース
planner が 意図的に Seq Scan を選ぶことがある。これは正常な挙動：
| 状況 | 理由 |
|---|---|
| テーブルが小さい（数百行〜数千行） | index 経由のオーバーヘッド > 全件読みコスト |
| クエリ結果が大半の行を返す（selectivity 低） | index で絞っても結局多くの行を取りに行く |
集計クエリ（COUNT(*)、SUM、AVG） | 全行必要なので index 不要 |
| 大量バルク処理 | parallel seq scan で並列化が効く |
| planner の cost 推定で seq の方が安い | pg_stats、reltuples の統計次第 |
→ 「Seq Scan = 悪」ではない。問題は「index があるのに Seq Scan に倒れる」現象。
Seq Scan が予期せず選ばれる原因
| 原因 | 対策 |
|---|---|
| 統計情報が古い | ANALYZE 実行 |
WHERE 句で関数適用（例: LOWER(col) = ?） | 式インデックス追加 |
型変換が暗黙発生（例: text カラムに数値比較） | 型を揃える |
| leakproof でない関数 + RLS | dba-11 §8.6 参照 |
| 結果が全体の 30% 超 | index 経由が遅いと planner が判断（正しい挙動） |
選び分けの目安
- 単一値で完全一致 / 範囲 / ソート → B-tree
- 配列・JSONB・全文検索の含有判定 → GIN
- JSON value だが key が固定で
->>でアクセス → B-tree の expression indexCREATE INDEX ON orders ((payload ->> 'order_id')); - 小規模 (数百〜数千行) テーブル → Seq Scan で十分、index 不要
- 書き込み頻度 vs 読み込み頻度のバランス → B-tree 寄り（GIN は挿入コスト高い、Seq Scan は書き込み最速）
計算量サマリ（10 万件想定）
| クエリパターン | Seq Scan | B-tree | GIN |
|---|---|---|---|
WHERE id = 42 | 全件 ~3000 page | ~3 page アクセス | n/a |
WHERE created_at > '2024-01-01' | 全件 ~3000 page | log + k page | n/a |
WHERE tags @> ARRAY['java'] | 全件 ~3000 page | n/a | log K + R page |
WHERE payload @> '{"key":"value"}' | 全件 ~3000 page | n/a | log K + R page |
WHERE payload ->> 'key' = 'value' | 全件 ~3000 page | (expression index ありで) ~3 page | n/a |
ORDER BY created_at LIMIT 10 | 全件読み + Sort | 10 page 順 | ❌ |
COUNT(*) 全件 | ~3000 page (parallel 可) | ❌ 通常 Seq Scan 選ばれる | ❌ |
10 万件で 1 page あたり ~33 行と仮定。Seq Scan は どんなクエリでも全件 ~3000 page だが、index 経由は数 page で済む。
→ index ありで 約 1000 倍の差。Issue #1550 で実証した「10 万件の Seq Scan 30ms / Bitmap Index Scan 0.5ms」がまさにこの比率。
データ量と検索時間の関係（イメージ）
データ量が増えるほど Seq Scan は線形に悪化、index 系はほぼ横ばい。線形スケールで描くと一目で分かる：
→ X 軸を等差（10M 刻み）にすると、Seq Scan は 完全に比例の直線（O(n)）。B-tree / GIN は底辺に張り付くほど ほぼ水平（O(log n)）。両者の傾きの違いがそのまま「データ増加への耐性」の差。
⚠️ 数値はあくまでイメージ: 実時間は SSD/HDD 種別・shared_buffers サイズ・OS page cache の状態で 10 倍以上ブレうる。本番運用の意思決定は
EXPLAIN (ANALYZE, BUFFERS)で実測して判断する。
小規模域も含めた全体像（対数で見るなら）：
| データ量 | Seq Scan | B-tree | GIN |
|---|---|---|---|
| 1,000 行 | 0.1ms | 0.05ms | 0.05ms |
| 10,000 行 | 1ms | 0.05ms | 0.05ms |
| 100,000 行 | 10ms | 0.05ms | 0.1ms |
| 1,000,000 行 | 100ms | 0.1ms | 0.5ms |
| 10,000,000 行 | 1,000ms | 0.15ms | 1ms |
| 100,000,000 行 | 10,000ms (10秒) | 0.2ms | 5ms |
数値で見ると、Seq Scan は行数が 10倍になれば時間も 10倍。一方 index 系は 行数が 10万倍になっても時間は数倍程度しか増えない。
読み解き
- 小規模 (〜10,000 行): どれも 1ms 以内。index ありの差はほぼ無視可能 → Seq Scan で十分
- 中規模 (10万〜100万行): Seq Scan が体感できる遅さに → index 必須
- 大規模 (1000万行〜): Seq Scan は秒オーダー、index 経由は依然 ms オーダー → index なしは事実上 NG
- B-tree vs GIN: 同じ条件なら B-tree が僅かに速い（entry が単純）。GIN は posting list 走査分の overhead がある
設計判断のタイミング
| データ量予測 | index 設計の優先度 |
|---|---|
| 数千行で頭打ち（マスターデータ等） | 任意。PK だけで十分なケース多い |
| 数万〜数十万行 | 必須。WHERE/JOIN/ORDER BY に応じて btree 追加 |
| 数百万〜数千万行 | 必須 + 複合 index / partial index の検討 |
| 億単位 | 必須 + partitioning + cover index 等の高度設計 |
→ idp-server のような マルチテナント SaaS は、テナント数 × データ /テナントで容易に億単位に到達する。設計段階から index 戦略が必須。
4. GiSTインデックス
4.1 GiSTの仕組み
┌─────────────────────────────────────────────────────────────────┐
│ GiST (Generalized Search Tree) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【用途】 │
│ ・地理空間データ（PostGIS） │
│ ・範囲型（Range Types） │
│ ・全文検索（ts_vector） │
│ ・類似検索（pg_trgm） │
│ │
│ 【特徴】 │
│ ・「含む」「重なる」「近い」などの演算に対応 │
│ ・B-treeより柔軟だが、やや遅い場合もある │
│ │
└─────────────────────────────────────────────────── ──────────────┘
4.2 GiSTの用途
-- 範囲型（Range）での排他制約
CREATE TABLE reservations (
id BIGINT PRIMARY KEY,
room_id BIGINT NOT NULL,
reserved_period TSTZRANGE NOT NULL,
EXCLUDE USING GIST (room_id WITH =, reserved_period WITH &&)
);
-- 挿入時に重複期間があればエラー
INSERT INTO reservations (room_id, reserved_period)
VALUES (1, '[2024-03-01 10:00, 2024-03-01 12:00)');
-- 同じ部屋 で重なる期間は挿入不可
INSERT INTO reservations (room_id, reserved_period)
VALUES (1, '[2024-03-01 11:00, 2024-03-01 13:00)');
-- ERROR: conflicting key value violates exclusion constraint
-- 地理空間検索（PostGIS）
CREATE INDEX idx_locations_point ON locations USING gist (location);
SELECT * FROM locations
WHERE ST_DWithin(location, ST_MakePoint(139.7, 35.7)::geography, 1000);
-- 類似検索（pg_trgm）
CREATE INDEX idx_users_name_gist ON users USING gist (name gist_trgm_ops);
SELECT * FROM users
WHERE name % 'John'
ORDER BY name <-> 'John'
LIMIT 10;
5. その他のインデックス
5.1 Hashインデックス
-- 等価検索のみに使用
CREATE INDEX idx_users_email_hash ON users USING hash (email);
-- 有効: 等価検索
SELECT * FROM users WHERE email = 'alice@example.com';
-- 無効: 範囲検索
SELECT * FROM users WHERE email > 'a'; -- 使われない
-- PostgreSQL 10以降でWAL対応となり、実用的になった
-- ただし、B-treeでほとんどのケースはカバー可能
5.2 BRINインデックス
┌─────────────────────────────────────────────────────────────────┐
│ BRIN (Block Range Index) │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【特徴】 │
│ ・非常に小さいサイズ（B-treeの数%程度） │
│ ・連続したブロック範囲の最小/最大値を保持 │
│ ・時系列データに最適 │
│ │
│ 【仕組み】 │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Block 1-128 │ min: 2024-01-01, max: 2024-01-31 │ │
│ │ Block 129-256 │ min: 2024-02-01, max: 2024-02-28 │ │
│ │ Block 257-384 │ min: 2024-03-01, max: 2024-03-31 │ │
│ │ ... │ ... │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ → 2024-02-15を検索 → Block 129-256のみスキャン │
│ │
│ 【適用条件】 │
│ ・物理的な順序とカラム値の順序に相関がある │
│ ・テーブルが非常に大きい（数GB以上） │
│ ・時系列データ、ログテーブルなど │
│ │
└─────────────────────────────────────────────────────────────────┘
-- ログテーブル（時系列順に挿入される）
CREATE TABLE access_logs (
id BIGINT GENERATED ALWAYS AS IDENTITY,
accessed_at TIMESTAMPTZ NOT NULL,
user_id BIGINT,
path TEXT
);
-- BRINインデックス（pages_per_range でブロック範囲を指定）
CREATE INDEX idx_logs_accessed_at ON access_logs
USING brin (accessed_at) WITH (pages_per_range = 128);
-- サイズ比較
-- B-tree: 数GB
-- BRIN: 数MB
5.3 式インデックス（関数インデックス）
-- 関数を適用した結果にインデックス
CREATE INDEX idx_users_email_lower ON users (LOWER(email));
-- 使用例
SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';
-- JSONBの特定のキー
CREATE INDEX idx_products_color
ON products ((attributes->>'color'));
SELECT * FROM products WHERE attributes->>'color' = 'red';
-- 計算結果
CREATE INDEX idx_orders_year
ON orders (EXTRACT(YEAR FROM ordered_at));
SELECT * FROM orders WHERE EXTRACT(YEAR FROM ordered_at) = 2024;
6. 複合インデックス
6.1 複合インデックスの仕組み
┌─────────────────────────────────────────────────────────────────┐
│ 複合インデックス │
├─────────────────────────────────────────────────────────────────┤
│ │
│ CREATE INDEX idx ON orders (user_id, status, ordered_at); │
│ │
│ 【インデックスの構造】 │
│ ┌───────────────────────────────────────────────────────┐ │
│ │ user_id │ status │ ordered_at │ → row pointer │ │
│ ├─────────┼───────────┼────────────┼────────────────────┤ │
│ │ 1 │ completed │ 2024-01-01 │ → ... │ │
│ │ 1 │ completed │ 2024-02-01 │ → ... │ │
│ │ 1 │ pending │ 2024-03-01 │ → ... │ │
│ │ 2 │ completed │ 2024-01-15 │ → ... │ │
│ │ 2 │ pending │ 2024-02-15 │ → ... │ │
│ │ ... │ ... │ ... │ │ │
│ └───────────────────────────────────────────────────────┘ │
│ │
│ → 左から順にソートされている │
│ │
└─────────────────────────────────────────────────────────────────┘
6.2 複合インデックスの効果
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, ordered_at);
-- ✅ 効果あり（左端から使用）
SELECT * FROM orders WHERE user_id = 1;
SELECT * FROM orders WHERE user_id = 1 AND status = 'completed';
SELECT * FROM orders WHERE user_id = 1 AND status = 'completed'
AND ordered_at >= '2024-01-01';
-- ⚠️ 部分的に効果あり
SELECT * FROM orders WHERE user_id = 1 AND ordered_at >= '2024-01-01';
-- → user_id での絞り込みには使用、ordered_at は要検討
-- ❌ 効果なし（左端のカラムがない）
SELECT * FROM orders WHERE status = 'completed';
SELECT * FROM orders WHERE ordered_at >= '2024-01-01';
-- ソートでも効果あり
SELECT * FROM orders WHERE user_id = 1
ORDER BY status, ordered_at; -- ✅
SELECT * FROM orders WHERE user_id = 1
ORDER BY ordered_at; -- ⚠️ status を飛ばしている
6.3 カラム順序の設計
┌──────────── ─────────────────────────────────────────────────────┐
│ 複合インデックスのカラム順序 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【原則】 │
│ 1. 等価条件のカラムを先に │
│ 2. 範囲条件のカラムを後に │
│ 3. カーディナリティが高いカラムを先に │
│ │
│ 【例】 │
│ WHERE user_id = 1 AND status = 'completed' │
│ AND ordered_at >= '2024-01-01' │
│ │
│ ✅ 良い順序: (user_id, status, ordered_at) │
│ user_id = 等価 │
│ status = 等価 │
│ ordered_at = 範囲 (最後) │
│ │
│ ❌ 悪い順序: (ordered_at, user_id, status) │
│ 範囲条件が先頭だと、後続カラムの絞り込み効率が下がる │
│ │
└─────────────────────────────────────────────────────────────────┘
7. 部分インデックス
7.1 部分インデックスとは
┌─────────────────────────────────────────────────────────────────┐
│ 部分インデックス │
├────────────── ───────────────────────────────────────────────────┤
│ │
│ 【概念】 │
│ テーブルの一部の行のみをインデックス化 │
│ │
│ 【メリット】 │
│ ・インデックスサイズの削減 │
│ ・更新オーバーヘッドの削減 │
│ ・特定条件の検索が高速化 │
│ │
└─────────────────────────────────────────────────────────────────┘
7.2 部分インデックスの例
-- アクティブユーザーのみインデックス
CREATE INDEX idx_users_email_active
ON users (email)
WHERE is_active = true;
-- 使用される
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = true;
-- 使用されない（条件が一致しない）
SELECT * FROM users WHERE email = 'alice@example.com';
SELECT * FROM users WHERE email = 'alice@example.com' AND is_active = false;
-- 未処理の注文のみインデックス
CREATE INDEX idx_orders_pending
ON orders (user_id, ordered_at)
WHERE status = 'pending';
-- 使用される
SELECT * FROM orders WHERE user_id = 1 AND status = 'pending';
-- ソフトデリート対応
CREATE UNIQUE INDEX idx_users_email_not_deleted
ON users (email)
WHERE deleted_at IS NULL;
-- deleted_atがNULLの行でのみemailがユニーク
7.3 よくある部分インデックスのパターン
-- NULLでない行のみ
CREATE INDEX idx_orders_shipped_at
ON orders (shipped_at)
WHERE shipped_at IS NOT NULL;
-- 最近のデータのみ
CREATE INDEX idx_logs_recent
ON access_logs (user_id, accessed_at)
WHERE accessed_at >= '2024-01-01';
-- 特定のステータスのみ
CREATE INDEX idx_tasks_incomplete
ON tasks (assigned_to, due_date)
WHERE status NOT IN ('completed', 'cancelled');
8. カバリングインデックス
8.1 カバリングインデックスとは
┌─────────────────────────────────────────────────────────────────┐
│ カバリングインデックス │
├─────────────────────────────────────────────────────────────────┤
│ │
│ 【通常のIndex Scan】 │
│ 1. インデックスで行の位置を特定 │
│ 2. テーブル（Heap）にアクセスしてデータ取得 │
│ │
│ 【Index Only Scan（カバリングインデックス使用時）】 │
│ 1. インデックスのみでデータ取得完了 │
│ 2. テーブルへのアクセス不要 → 高速 │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Query: SELECT id, email FROM users WHERE email = '...' │ │
│ │ │ │
│ │ Index (email) INCLUDE (id): │ │
│ │ ┌──────────────────────┬─────┐ │ │
│ │ │ email │ id │ ← インデックスに含まれる │ │
│ │ ├──────────────────────┼─────┤ │ │
│ │ │ alice@example.com │ 1 │ │ │
│ │ │ bob@example.com │ 2 │ │ │
│ │ └──────────────────────┴─────┘ │ │
│ │ │ │
│ │ → テーブルにアクセスせず回答可能 │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
└─────────────────────────────────────────────────────────────────┘
8.2 INCLUDEを使用したカバリングインデックス
-- PostgreSQL 11+
CREATE INDEX idx_users_email_covering
ON users (email) INCLUDE (id, name);
-- このクエリはIndex Only Scanになる可能性が高い
SELECT id, name FROM users WHERE email = 'alice@example.com';
-- 複合インデックス + INCLUDE
CREATE INDEX idx_orders_user_covering
ON orders (user_id, status)
INCLUDE (total_amount, ordered_at);
-- Index Only Scan
SELECT total_amount, ordered_at
FROM orders
WHERE user_id = 1 AND status = 'completed';
8.3 Index Only Scanの 条件
-- Index Only Scanが使われるための条件
-- 1. SELECTするカラムがすべてインデックスに含まれている
-- 2. Visibility Map で可視性が確認できる（VACUUMが実行されている）
-- 確認方法
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, email FROM users WHERE email = 'alice@example.com';
-- "Index Only Scan" と表示されればOK
-- "Heap Fetches: 0" なら最高効率
9. インデックスの運用
9.1 インデックスの確認
-- テーブルのインデックス一覧
SELECT
indexname,
indexdef
FROM pg_indexes
WHERE tablename = 'users';
-- インデックスのサイズ
SELECT
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'users'
ORDER BY pg_relation_size(indexrelid) DESC;
-- インデックスの使用状況
SELECT
indexrelname AS index_name,
idx_scan AS times_used,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
WHERE relname = 'users';
-- 未使用のインデックスを検出
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
idx_scan AS times_used,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelname NOT LIKE '%_pkey'
ORDER BY pg_relation_size(indexrelid) DESC;
9.2 インデックスの作成オプション
-- 通常の作成（テーブルロック発生）
CREATE INDEX idx_users_email ON users (email);
-- CONCURRENTLY: ロックなしで作成（時間はかかる）
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- 失敗した場合の対処
-- INVALIDなインデックスが残ることがある
DROP INDEX CONCURRENTLY idx_users_email;
-- 再作成
CREATE INDEX CONCURRENTLY idx_users_email ON users (email);
-- インデックスの再構築
REINDEX INDEX idx_users_email;
-- テーブル全体のインデックス再構築
REINDEX TABLE users;
-- CONCURRENTLY で再構築（PostgreSQL 12+）
REINDEX INDEX CONCURRENTLY idx_users_email;
9.3 インデックスの膨張対策
-- インデックスの膨張確認
SELECT
nspname AS schema_name,
relname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan,
idx_tup_read
FROM pg_stat_user_indexes
JOIN pg_class ON pg_class.oid = indexrelid
JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;
-- 膨張が疑われる場合はREINDEX
REINDEX INDEX CONCURRENTLY idx_users_email;
-- または pg_repack を使用
-- pg_repack -d mydb --only-indexes -t users
10. 設計のベストプラクティス
10.1 インデックス設計のチェックリスト
┌─────────────────────────────────────────────────────────────────┐
│ インデックス設計チェックリスト │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ✅ 主キーにはインデックスが自動作成される │
│ ✅ UNIQUE制約にもインデックスが自動作成される │
│ ✅ 外部キーには明示的にインデックスを作成する │
│ ✅ WHERE句で頻繁に使われるカラムにインデックス │
│ ✅ JOIN条件のカラムにインデックス │
│ ✅ ORDER BYで使われるカラムにインデックス │
│ │
│ ⚠️ 低カーディナリティ（値の種類が少ない）はインデックス効果薄 │
│ ⚠️ 書き込みが多いテーブルはインデックス最小限に │
│ ⚠️ 未使用のインデックスは削除を検討 │
│ │
└─────────────────────────────────────────────────────────────────┘
10.2 よくあるパターン
-- 外部キーにはインデックスを作成
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
...
);
CREATE INDEX idx_orders_user_id ON orders (user_id);
-- ステータス検索が多い場合
CREATE INDEX idx_orders_status ON orders (status)
WHERE status IN ('pending', 'processing');
-- 日時範囲検索
CREATE INDEX idx_orders_ordered_at ON orders (ordered_at DESC);
-- 複合条件（よく一緒に使われるカラム）
CREATE INDEX idx_orders_user_status_date
ON orders (user_id, status, ordered_at DESC);
-- 全文検索
CREATE INDEX idx_articles_body_fts
ON articles USING gin (to_tsvector('english', body));
-- JSONB
CREATE INDEX idx_users_preferences
ON users USING gin (preferences jsonb_path_ops);
10.3 アンチパターン
-- ❌ 全カラムにインデックス
-- 書き込み性能が大幅に低下
-- ❌ 低カーディナリティカラムへのインデックス
CREATE INDEX idx_users_gender ON users (gender); -- 効果薄い
-- ❌ 重複するインデックス
CREATE INDEX idx_a ON orders (user_id);
CREATE INDEX idx_b ON orders (user_id, status);
-- idx_a は idx_b でカバーできる
-- ❌ 使われないインデックス
-- 定期的に pg_stat_user_indexes を確認して削除