データベースのパフォーマンス問題の多くは、適切なインデックス設計で解決できます。しかし、インデックスは「とりあえず貼る」ものではなく、データ構造とクエリパターンを理解した上で設計する必要があります。本記事では、インデックスの内部構造から実践的な最適化手法まで、体系的に解説します。
インデックスとは
本の索引とのアナロジー
インデックスは、本の巻末にある「索引」に例えられます。
flowchart LR
subgraph NoIndex["インデックスなしの検索"]
A1["1ページ目から順に全ページをスキャン"] --> A2["O(n) の時間計算量"]
end
subgraph WithIndex["インデックスありの検索"]
B1["索引でキーワードを検索"] --> B2["ページ番号を取得"] --> B3["O(log n) の時間計算量"]
end
インデックスの基本動作
-- インデックスなし: Full Table Scan（全件スキャン）
SELECT * FROM users WHERE email = 'user@example.com';
-- 100万行すべてをスキャン
-- インデックスあり: Index Scan
CREATE INDEX idx_users_email ON users(email);
SELECT * FROM users WHERE email = 'user@example.com';
-- インデックスを使用して高速に検索
インデックスの種類
1. B-Tree インデックス
最も一般的なインデックス構造で、PostgreSQLのデフォルトです。
flowchart TB
R["[50]"] --> L["[25]"]
R --> RI["[75]"]
L --> LL["[10,20]"]
L --> LR["[30,40]"]
RI --> RL["[60,70]"]
RI --> RR["[80,90]"]
LL --> D1["データページ"]
LR --> D2["データページ"]
RL --> D3["データページ"]
RR --> D4["データページ"]
特徴:
- 等価検索（=）: O(log n)
- 範囲検索（<, >, BETWEEN）: O(log n + m)
- ソート済みデータへのアクセス: 効率的
-- B-Treeに適した操作
SELECT * FROM orders WHERE created_at > '2025-12-01'; -- 範囲検索
SELECT * FROM products ORDER BY price; -- ソート
SELECT * FROM users WHERE id = 12345; -- 等価検索
2. Hash インデックス
等価検索のみに特化したインデックスです。
flowchart LR
Input["'user@example.com'"] --> Hash["hash() = 42"]
subgraph Buckets["バケット配列"]
B0["[0] → NULL"]
B1["[1] → NULL"]
B42["[42] → (user@example.com, row_pointer)"]
B99["[99] → NULL"]
end
Hash --> B42
特徴:
- 等価検索（=）: O(1)
- 範囲検索: 不可
- ソート: 不可
-- PostgreSQL 10+ でHash インデックスが改善
CREATE INDEX idx_users_email_hash ON users USING hash(email);
-- 適した操作
SELECT * FROM users WHERE email = 'user@example.com'; -- ✓
-- 不適な操作
SELECT * FROM users WHERE email LIKE 'user%'; -- ✗
SELECT * FROM users ORDER BY email; -- ✗
3. GIN（Generalized Inverted Index）
配列、JSONB、全文検索に適したインデックスです。
-- JSONB カラムへのGINインデックス
CREATE INDEX idx_products_metadata ON products USING gin(metadata);
-- 効率的に検索可能
SELECT * FROM products
WHERE metadata @> '{"category": "electronics"}';
-- 配列カラム
CREATE INDEX idx_posts_tags ON posts USING gin(tags);
SELECT * FROM posts WHERE tags @> ARRAY['javascript', 'react'];
-- 全文検索
CREATE INDEX idx_articles_fts ON articles
USING gin(to_tsvector('japanese', title || ' ' || content));
4. GiST（Generalized Search Tree）
地理空間データ、範囲型に適したインデックスです。
-- PostGIS 地理空間インデックス
CREATE INDEX idx_locations_geom ON locations USING gist(geom);
-- 範囲検索
SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(139.7, 35.6), 1000); -- 1km以内
-- 範囲型
CREATE INDEX idx_reservations_period ON reservations
USING gist(daterange(start_date, end_date));
インデックス種類の比較
| インデックス | 等価検索 | 範囲検索 | ソート | 用途 |
|---|---|---|---|---|
| B-Tree | ◎ | ◎ | ◎ | 汎用 |
| Hash | ◎ | ✗ | ✗ | 等価のみ |
| GIN | ◎ | △ | ✗ | 配列・JSONB・全文検索 |
| GiST | ○ | ◎ | ✗ | 地理空間・範囲 |
| BRIN | △ | ◎ | △ | 大規模・時系列 |
複合インデックス
列の順序が重要
複合インデックスでは、列の順序がクエリのパフォーマンスに大きく影響します。
-- 複合インデックス
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- このインデックスが使われるクエリ:
SELECT * FROM orders WHERE user_id = 123; -- ✓
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid'; -- ✓
SELECT * FROM orders WHERE user_id = 123 ORDER BY status; -- ✓
-- このインデックスが使われないクエリ:
SELECT * FROM orders WHERE status = 'paid'; -- ✗ 先頭列がない
flowchart TB
Root["(user_id=100, status=active)"] --> Left["(user_id=50, status=*)"]
Root --> Right["(user_id=150, status=*)"]
Left --> LL["ソート済みリーフ"]
Right --> RL["ソート済みリーフ"]
検索の流れ:
- 先頭列（user_id）でまずフィルタリング
- その後、次の列（status）でフィルタリング
列順序の決定基準
-- 基本原則: カーディナリティ（値の種類数）が高い列を先に
-- users テーブル
-- user_id: 100万種類（高カーディナリティ）
-- status: 3種類（低カーディナリティ）
-- country: 200種類（中カーディナリティ）
-- 推奨: 高カーディナリティ → 低カーディナリティ
CREATE INDEX idx_users_composite ON users(user_id, country, status);
-- ただし、クエリパターンも考慮
-- WHERE status = 'active' が頻繁なら status を先に入れることも
カバリングインデックス（INCLUDE句）
-- PostgreSQL 11+ のINCLUDE句
CREATE INDEX idx_orders_covering ON orders(user_id, status)
INCLUDE (total_amount, created_at);
-- Index Only Scan が可能に
SELECT user_id, status, total_amount, created_at
FROM orders
WHERE user_id = 123 AND status = 'paid';
-- テーブルへのアクセスなしでインデックスのみで完結
実行計画の読み方
EXPLAIN ANALYZE
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 AND status = 'paid';
Index Scan using idx_orders_user_status on orders
(cost=0.43..8.45 rows=1 width=100)
(actual time=0.025..0.026 rows=1 loops=1)
Index Cond: ((user_id = 123) AND (status = 'paid'))
Planning Time: 0.150 ms
Execution Time: 0.050 ms
flowchart TB
subgraph Cost["cost=0.43..8.45"]
C1["0.43: 最初の行を返すまでのコスト<br/>（スタートアップコスト）"]
C2["8.45: 全行を返すまでの総コスト"]
end
subgraph Estimate["推定値"]
E1["rows=1: 推定行数"]
E2["width=100: 1行あたりの推定バイト数"]
end
subgraph Actual["実測値"]
A1["actual time=0.025..0.026: 実際の実行時間（ms）"]
A2["rows=1: 実際に返された行数"]
A3["loops=1: この操作の実行回数"]
end
スキャン方式の比較
-- Seq Scan（シーケンシャルスキャン）: 全件スキャン
Seq Scan on orders (cost=0.00..18584.00 rows=100000 width=100)
Filter: (status = 'paid')
-- Index Scan: インデックスを使用してテーブルにアクセス
Index Scan using idx_orders_status on orders (cost=0.43..8.45 rows=1)
Index Cond: (status = 'paid')
-- Index Only Scan: インデックスのみで完結（最速）
Index Only Scan using idx_orders_covering on orders (cost=0.43..4.45 rows=1)
Index Cond: (status = 'paid')
-- Bitmap Index Scan: 複数インデックスの組み合わせ
Bitmap Heap Scan on orders (cost=5.00..100.00 rows=100)
-> Bitmap Index Scan on idx_orders_status (cost=0.00..4.50 rows=100)
Index Cond: (status = 'paid')
インデックス設計のベストプラクティス
1. クエリパターンの分析
-- pg_stat_statements でよく実行されるクエリを確認
SELECT query, calls, mean_time, total_time
FROM pg_stat_statements
ORDER BY total_time DESC
LIMIT 20;
-- 遅いクエリの特定
SELECT query, mean_time, calls
FROM pg_stat_statements
WHERE mean_time > 100 -- 100ms以上
ORDER BY mean_time DESC;
2. インデックス使用状況の監視
-- 使われていないインデックスの検出
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
ORDER BY pg_relation_size(indexrelid) DESC;
-- 重複インデックスの検出
SELECT
a.indexrelid::regclass AS index1,
b.indexrelid::regclass AS index2,
a.indkey AS columns1,
b.indkey AS columns2
FROM pg_index a
JOIN pg_index b ON a.indrelid = b.indrelid
AND a.indexrelid < b.indexrelid
AND a.indkey <@ b.indkey;
3. 部分インデックス
特定の条件のデータのみインデックス化します。
-- アクティブユーザーのみインデックス化
CREATE INDEX idx_users_active ON users(email)
WHERE status = 'active';
-- 最近のデータのみ
CREATE INDEX idx_orders_recent ON orders(created_at)
WHERE created_at > '2024-01-01';
-- NULLでないデータのみ
CREATE INDEX idx_products_sku ON products(sku)
WHERE sku IS NOT NULL;
4. 式インデックス
計算結果や関数の結果をインデックス化します。
-- 大文字小文字を区別しない検索
CREATE INDEX idx_users_email_lower ON users(lower(email));
SELECT * FROM users WHERE lower(email) = 'user@example.com';
-- 日付の一部
CREATE INDEX idx_orders_year_month ON orders(
date_trunc('month', created_at)
);
SELECT * FROM orders
WHERE date_trunc('month', created_at) = '2025-12-01';
-- JSONBの特定キー
CREATE INDEX idx_users_settings_theme ON users((settings->>'theme'));
SELECT * FROM users WHERE settings->>'theme' = 'dark';
インデックスのアンチパターン
1. 過剰なインデックス
-- アンチパターン: すべての列にインデックス
CREATE INDEX idx_users_id ON users(id); -- 主キーで自動作成済み
CREATE INDEX idx_users_name ON users(name); -- 本当に必要？
CREATE INDEX idx_users_email ON users(email); -- 本当に必要？
CREATE INDEX idx_users_created ON users(created_at);
CREATE INDEX idx_users_updated ON users(updated_at);
CREATE INDEX idx_users_status ON users(status);
-- 問題点:
-- - INSERT/UPDATE/DELETE が遅くなる
-- - ストレージ容量の浪費
-- - VACUUM処理の負荷増大
2. 低選択性のインデックス
-- アンチパターン: 値の種類が少ないカラム
CREATE INDEX idx_users_gender ON users(gender); -- 男/女/その他の3種類
-- 問題点:
-- - インデックスを使っても大量の行にアクセス
-- - Seq Scanの方が効率的な場合が多い
-- 対策: 他の列と組み合わせる
CREATE INDEX idx_users_gender_age ON users(gender, birth_date);
3. 関数を使ったWHERE句
-- アンチパターン: インデックスが使われない
SELECT * FROM users WHERE YEAR(created_at) = 2025;
-- インデックスが使われる書き方
SELECT * FROM users
WHERE created_at >= '2025-12-01'
AND created_at < '2026-01-01';
-- または式インデックスを作成
CREATE INDEX idx_users_created_year ON users(EXTRACT(YEAR FROM created_at));
4. 型の不一致
-- アンチパターン: 暗黙の型変換
-- user_id は INTEGER 型
SELECT * FROM users WHERE user_id = '123'; -- 文字列リテラル
-- インデックスが使われる書き方
SELECT * FROM users WHERE user_id = 123; -- 整数リテラル
パフォーマンスチューニング例
ケーススタディ: ECサイトの注文検索
-- 元のクエリ（遅い）
SELECT o.*, u.name AS user_name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
AND o.created_at > NOW() - INTERVAL '7 days'
ORDER BY o.created_at DESC
LIMIT 50;
-- 実行計画分析
EXPLAIN ANALYZE ...
-- Seq Scan on orders: 10秒
-- 最適化ステップ1: 適切なインデックス作成
CREATE INDEX idx_orders_status_created ON orders(status, created_at DESC);
-- 最適化ステップ2: 部分インデックス（pending のみ）
CREATE INDEX idx_orders_pending ON orders(created_at DESC)
WHERE status = 'pending';
-- 最適化ステップ3: カバリングインデックス
CREATE INDEX idx_orders_pending_covering ON orders(created_at DESC)
INCLUDE (user_id, total_amount)
WHERE status = 'pending';
-- 結果: 10秒 → 5ms
インデックスのメンテナンス
定期的なREINDEX
-- 肥大化したインデックスの再構築
REINDEX INDEX idx_orders_status;
-- テーブル内の全インデックス
REINDEX TABLE orders;
-- PostgreSQL 12+ : CONCURRENTLY オプション
REINDEX INDEX CONCURRENTLY idx_orders_status;
インデックスの肥大化確認
-- インデックスの肥大化率を確認
SELECT
schemaname,
relname AS table_name,
indexrelname AS index_name,
pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
idx_scan AS number_of_scans,
idx_tup_read AS tuples_read,
idx_tup_fetch AS tuples_fetched
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;
まとめ
データベースインデックスは、適切に設計・管理することで劇的なパフォーマンス向上を実現できます。
設計の原則
- クエリパターン優先: 実際のクエリに基づいて設計
- 複合インデックス: 列の順序は選択性とクエリパターンを考慮
- 部分インデックス: 必要なデータのみインデックス化
- 過剰インデックスを避ける: 書き込みコストとのバランス
監視と最適化
- EXPLAIN ANALYZE: 実行計画の定期的な確認
- pg_stat_statements: 遅いクエリの特定
- 未使用インデックス: 定期的な棚卸し
- REINDEX: 肥大化したインデックスの再構築
インデックスは「設計して終わり」ではなく、継続的な監視と最適化が重要です。