インデックス

DB の検索を「フルスキャン → ピンポイント参照」に変える仕組み。 B-tree / Hash / GIN / GiST / BRIN、複合・部分・関数式まで一通り。

そもそもインデックスとは

本の巻末索引と同じ仕組み
列の値 → 行の位置を持つ別データ構造
SELECT は速くなる、INSERT/UPDATE は遅くなる
容量も食う

フルスキャン vs インデックススキャン

SELECT * FROM users WHERE email = 'alice@ex.com';

なし → 全行を上から舐める（O(N)）
あり → B-tree で 1 件にたどり着く（O(log N)）

主要なインデックス種別（Postgres 中心）

B-tree（既定）

最も一般的。= / < / > / BETWEEN / IN / ORDER BY に効く
9 割のケースはこれ
CREATE INDEX でデフォルト

CREATE INDEX users_email_idx ON users (email);
CREATE INDEX posts_created_idx ON posts (created_at DESC);

Hash

= 専用。範囲検索には使えない
非常に大きいキー（巨大文字列）の等価比較で B-tree より速い
普段は B-tree で OK（あえて選ばない）

GIN（Generalized Inverted Index）

多値カラム用: 配列、JSONB、tsvector（全文検索）
「この配列に X が含まれる」「この JSON のキーに X」

-- 配列
CREATE INDEX posts_tags_gin ON posts USING GIN (tags);
SELECT * FROM posts WHERE tags @> ARRAY['react'];

-- JSONB
CREATE INDEX posts_meta_gin ON posts USING GIN (meta);
SELECT * FROM posts WHERE meta @> '{"category": "tech"}';

-- 全文検索
CREATE INDEX posts_fts ON posts USING GIN (to_tsvector('simple', title));
SELECT * FROM posts WHERE to_tsvector('simple', title) @@ to_tsquery('react');

GiST（Generalized Search Tree）

幾何 / 範囲 / 全文検索 / 近傍検索など多目的
PostGIS（地理空間）の標準
近傍検索（ORDER BY 距離）も可能

SP-GiST

非平衡データ構造（クアッドツリー、k-d 木）に向く特殊ケース。

BRIN（Block Range Index）

巨大テーブル + 値が物理的に並んでいるときに有効
サイズが小さい（B-tree の 1% 以下）
時系列ログ + created_at で活躍

CREATE INDEX events_time_brin ON events USING BRIN (created_at);

HNSW / IVFFlat（pgvector）

ベクトル類似検索用
HNSW: グラフベース、高精度・遅い構築
IVFFlat: クラスタリング、軽量

CREATE INDEX embeddings_idx ON embeddings USING HNSW (embedding vector_cosine_ops);

複合インデックス（マルチカラム）

CREATE INDEX posts_user_status_idx ON posts (user_id, status, created_at DESC);

左から使われる（user_id だけ / user_id + status / 全部、には効くが status 単独には効かない）
カラムの順序は絞り込み効率が高い順に
ORDER BY も同じ順序で書くと効く

UNIQUE インデックス

CREATE UNIQUE INDEX users_email_uniq ON users (email);

-- 通常は UNIQUE 制約として書く（同じ）
ALTER TABLE users ADD CONSTRAINT users_email_uniq UNIQUE (email);

部分インデックス

条件付きでサブセットだけインデックス化。容量と速度を両立。

-- 公開済み投稿だけ
CREATE INDEX posts_published_idx
  ON posts (created_at DESC)
  WHERE status = 'published';

-- 未読通知だけ
CREATE INDEX notifications_unread_idx
  ON notifications (user_id, created_at DESC)
  WHERE read_at IS NULL;

関数式インデックス

式の計算結果にインデックス。大文字小文字を無視した検索などに。

CREATE INDEX users_email_lower ON users (LOWER(email));
SELECT * FROM users WHERE LOWER(email) = LOWER('Alice@Ex.com');

-- 日付の年月でグルーピング
CREATE INDEX orders_yymm ON orders (DATE_TRUNC('month', created_at));

カバリングインデックス（INCLUDE）

WHERE では使わないが返したい列を含める。Index Only Scan が可能になる。

CREATE INDEX posts_user_idx
  ON posts (user_id)
  INCLUDE (title, created_at);

-- これでテーブルにアクセスせず索引だけで返せる
SELECT title, created_at FROM posts WHERE user_id = 42;

降順 / 昇順

CREATE INDEX posts_created_desc ON posts (created_at DESC);

-- B-tree は逆順スキャンも可能なので、必須ではない
-- ただし複合 + ORDER BY が混じる時は明示的に

NULL の扱い

NULL もインデックスに入る（Postgres）
NULLS FIRST / NULLS LAST で位置を制御
WHERE col IS NOT NULL も使える

インデックス更新コスト

INSERT: インデックス数 × ログ N の追加コスト
UPDATE: 該当列が変わるとインデックスも更新
DELETE: 索引から消す
巨大テーブルへの無闇なインデックス追加は禁物

使うべきインデックスの判断

WHERE / JOIN / ORDER BY で頻出する列
FK 列（JOIN で必須）
カーディナリティが高い列（値の種類が多い）
RLS で参照される列（SELECT のたびに評価）
絞り込みが効くなら部分インデックス

使うべきでないとき

カーディナリティが極端に低い（boolean 単体など、フルスキャンの方が速い）
テーブル全体の 30% 以上を返すクエリ
頻繁に更新される列

インデックスが効かないパターン

NG パターン	修正
`WHERE LOWER(col) = ...` + 普通 INDEX	関数式インデックス
`WHERE col \|\| '%' LIKE ...`	前方一致なら使える、中間一致は使えない
暗黙の型変換 `WHERE id = '42'`	型を合わせる
OR が多い	UNION ALL に書き換えるか個別 INDEX
NOT IN / NOT EXISTS（場合による）	JOIN に書き換え
関数を WHERE に直接	関数式インデックス

EXPLAIN で確認

EXPLAIN ANALYZE
SELECT * FROM posts WHERE user_id = 42 AND status = 'published';

出力例:

Index Scan using posts_user_status_idx on posts
  Index Cond: ((user_id = 42) AND (status = 'published'))
  Rows: 23  loops: 1
Planning Time: 0.123 ms
Execution Time: 0.456 ms

Seq Scan: 全件読み（小テーブルなら OK）
Index Scan: 索引から行特定 + テーブル参照
Index Only Scan: 索引だけで完結（最速）
Bitmap Heap Scan: 多くの行を効率的に

CONCURRENTLY（オンライン作成）

本番テーブルにロックを取らずインデックスを作成:

CREATE INDEX CONCURRENTLY posts_search_idx ON posts USING GIN (...);

時間はかかる（通常の倍以上）
トランザクション内では使えない
失敗すると INVALID 状態になるので監視必要
大規模テーブルへの本番追加では必須

未使用インデックスの検出

-- Postgres
SELECT schemaname, relname, indexrelname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelname NOT LIKE '%pkey'
ORDER BY pg_relation_size(indexrelid) DESC;

使われていないインデックスは削除候補（容量と更新コストの無駄）。

インデックスの容量

SELECT relname, pg_size_pretty(pg_total_relation_size(oid))
FROM pg_class
WHERE relkind = 'i'
ORDER BY pg_total_relation_size(oid) DESC LIMIT 10;

REINDEX

膨れ上がったインデックスを再構築:

REINDEX INDEX CONCURRENTLY posts_user_idx;
REINDEX TABLE CONCURRENTLY posts;

典型パターン集

ユーザの最新投稿リスト

CREATE INDEX posts_user_created_idx
  ON posts (user_id, created_at DESC)
  WHERE deleted_at IS NULL;

未読通知

CREATE INDEX notif_unread_idx
  ON notifications (user_id, created_at DESC)
  WHERE read_at IS NULL;

大文字小文字無視のメール検索

CREATE UNIQUE INDEX users_email_lower_uniq ON users (LOWER(email));

全文検索

CREATE INDEX posts_fts_idx
  ON posts
  USING GIN (to_tsvector('simple', title || ' ' || coalesce(content, '')));

JSON のキー検索

-- jsonb_path_ops は @> のみだが小さくて速い
CREATE INDEX posts_meta_gin ON posts USING GIN (meta jsonb_path_ops);

位置情報（PostGIS）

CREATE INDEX places_geom_gist ON places USING GIST (geom);
SELECT * FROM places ORDER BY geom <-> ST_MakePoint(139, 35) LIMIT 10;

失敗しやすいパターン

症状	対処
「全部に INDEX 付けた」	更新が重くなる、未使用は削除
「LIKE '%foo%' が遅い」	pg_trgm + GIN / GiST の trigram
「ORDER BY DESC LIMIT が遅い」	DESC INDEX で対応
「LIKE 大文字小文字が効かない」	citext or 関数式インデックス
「JSON クエリが重い」	GIN + path_ops、必要なら列に展開
「初日は速いが後日遅い」	VACUUM / ANALYZE / REINDEX

付ける順番のセオリー

1) 主キー（自動）/ 2) FK 列 / 3) 頻出 WHERE 列 / 4) ORDER BY 列 / 5) RLS で参照される列。困ったら EXPLAIN ANALYZE で「想定通りに使われているか」確認。