PostgreSQL インデックス設計の実務（B-tree / GIN / GiST / BRIN・複合列順・カバリング・部分・式インデックス・CONCURRENTLY）

インデックスは「速くする魔法」ではありません。読み取りを速くする代わりに、書き込みを遅くし、容量を食い、肥大化を招くトレードオフです。だから「全カラムに張る」は最悪手。正解は「pg_stat_statements 上位のクエリが使う述語にだけ、適切な型で張る」こと。

この記事は、その「適切な型」「適切な列順」「適切な範囲」を、PostgreSQL 公式ドキュメントに忠実に、実務の判断基準とともに整理します。パフォーマンス総論の §1 で示した「効く順番」の第1レイヤー——最も費用対効果が高い領域です。

この記事のルール：インデックス型の特性・対応演算子・SQL構文・PostgreSQL 18 の新機能は、すべて PostgreSQL 18 公式ドキュメント（2026年6月時点） に基づきます。プランナーが実際にどの索引を使うかはコストと統計で決まるため、最終判断は必ず EXPLAIN (ANALYZE, BUFFERS) で確認してください。

1. まず型を選ぶ：6種類の使い分け

PostgreSQL には6つのインデックスアクセスメソッドがあります。9割のケースは B-tree ですが、「B-tree では解けない問題」に他の型が効きます。

B-tree（既定）— 等値・範囲・並び替えの万能選手

公式が挙げる対応演算子は < <= = >= >。これに加えて BETWEEN・IN・IS NULL・IS NOT NULL も等価な構成として扱えます。さらに重要なのは2点。

• 前方一致の LIKE/正規表現：col LIKE 'foo%' や col ~ '^foo' のように定数で先頭固定なら索引が効きます。LIKE '%bar'（後方一致）は効きません。
• 並び替え：B-tree はソート順を保持するので、ORDER BY をインデックスで満たせます（ソート処理を省略できる）。

注意（ロケール）：C ロケール以外では、前方一致 LIKE に索引を効かせるのに専用の演算子クラス text_pattern_ops が必要です。

-- 等値・範囲は素直に B-tree
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- 前方一致検索を効かせる（非Cロケールでは text_pattern_ops が必要）
CREATE INDEX idx_users_email_prefix ON users (email text_pattern_ops);
-- これで WHERE email LIKE 'tanaka%' が索引を使える

Hash — 等値専用、ほぼ B-tree で足りる

Hash は値の32bitハッシュを格納し、等値比較のみ。範囲も並び替えもできません。WAL対応（クラッシュ安全）は PG10 で入りましたが、「等値しか使わない巨大な列で B-tree より小さくしたい」という限定状況以外、実務では B-tree で十分です。

GIN — 「1行に複数の値」を検索する（配列・JSONB・全文検索）

GIN は転置インデックスで、「1つの値に複数の構成要素が含まれる」データ向け。配列の包含 @>、JSONB のキー存在 ? や包含 @>、全文検索 tsvector @@ tsquery を高速化します。JSONB の索引設計は奥が深いので JSONB の記事で詳述します。

-- タグ配列の「このタグを含む行」を高速化
CREATE INDEX idx_articles_tags ON articles USING gin (tags);
-- WHERE tags @> ARRAY['postgresql'] が GIN を使う

-- 全文検索（日本語は専用設定が必要だが構造は同じ）
CREATE INDEX idx_docs_fts ON docs USING gin (to_tsvector('english', body));

GIN はビルドと更新のコストが高め（更新は内部の保留リストにバッファされる）。PostgreSQL 18 では GIN索引の並列ビルドが入り、巨大表での作成が速くなりました。

BRIN — 時系列・追記ログの「ほぼタダな索引」

BRIN は連続する物理ブロック範囲ごとに最小値・最大値の要約だけを持ちます。索引サイズは B-tree の数百〜数千分の1。ただし効くのは列の値が物理的な行順と強く相関しているときだけ——典型は「created_at 昇順で追記される時系列テーブル」です。

-- 数億行の時系列ログ。created_at が物理順とほぼ一致するなら BRIN が劇的に小さく速い
CREATE INDEX idx_events_ts_brin ON events USING brin (created_at);

2. 複合インデックスの列順：ここで9割が間違える

複数列の B-tree（最大32列、INCLUDE 含む）は列順が命です。公式の鉄則を引きます。

複数列インデックスは、先頭（左側）の列に制約があるときに最も効率的。正確な規則は、先頭列の等値制約＋等値制約を持たない最初の列の非等値（範囲）制約までが、スキャンするインデックスの範囲を絞る、というもの。

噛み砕くと——「等値（=）で絞る列を先に、範囲（<,>,BETWEEN）で絞る列を後に」。範囲列より後ろの列は、絞り込み（スキャン範囲の限定）には寄与せず、行フィルタにしか使えません。

-- クエリ: WHERE tenant_id = $1 AND status = $2 AND created_at >= $3 ORDER BY created_at
-- 正しい列順：等値2つ → 範囲1つ（範囲かつ ORDER BY 対象を最後に）
CREATE INDEX idx_orders_lookup
  ON orders (tenant_id, status, created_at);

この索引は tenant_id = ? AND status = ? で範囲を絞り、created_at で範囲スキャン＋整列順の提供までこなします。逆に (created_at, tenant_id, status) の順だと、tenant_id/status の等値はスキャン範囲を絞れません。

公式の戒め：「複合インデックスは控えめに。3列を超える索引が役立つことは稀」。あれもこれもと列を足すより、ワークロードに合った2〜3列の索引を数本持つ方が効きます。

PostgreSQL 18：スキップスキャンで列順の制約が緩和

PG18 で B-tree スキップスキャンが入り、「先頭列の等値制約がない」ケースでも複合索引を使える場面が増えました。公式の説明：

B-tree インデックススキャンがスキップスキャン最適化を適用できる場合、先頭列に等値制約がなくても、インデックスを反復探索しながら全列の制約を適用する。これにより読むべきインデックス範囲を縮められる。

例えば (x, y) の索引で WHERE y = 7700（x に条件なし）でも、x の各値に対する動的な等値プローブで索引を活用できます。ただし x のカーディナリティ（異なり値数）が低いほど効く。低カーディナリティの列が先頭にある複合索引は、PG18 で価値が上がりました。

並び替え順（ASC/DESC・NULLS）まで合わせる

B-tree の既定は 昇順・NULLS LAST（ASC は NULLS LAST、DESC は NULLS FIRST）。問題は列ごとに昇順・降順が混在する ORDER BY です。

-- クエリ: ORDER BY priority ASC, created_at DESC
-- 既定の (priority, created_at) では DESC 混在を索引で満たせない。混在順を明示する
CREATE INDEX idx_tasks_board
  ON tasks (priority ASC, created_at DESC);

混在順の ORDER BY が頻出するなら、索引もその順で作る。そうでなければ既定のままで十分です。

3. カバリングインデックス（INCLUDE）と index-only scan

通常のインデックススキャンは「索引で行の位置を特定 → ヒープ（本体）を読んで列値を取得」の2段階です。index-only scan は、必要な列がすべて索引に入っていればヒープを読まずに済み、I/O を大幅に削減します。

INCLUDE 句で「検索条件には使わないが、取得したい列」を索引に同梱できます（B-tree・GiST・SP-GiST のみ対応）。

-- WHERE customer_id = $1 で引いて id, amount を返すクエリを索引だけで完結させる
CREATE INDEX idx_orders_cust_covering
  ON orders (customer_id) INCLUDE (id, amount);

ただし重要な条件があります。公式曰く、index-only scan が成立しても「実際に得をするのは、テーブルのヒープページの相当割合が『all-visible』ビットを立てているときだけ」。

なぜか。PostgreSQL は MVCC のため、「その行が全トランザクションから可視か」を可視性マップで管理します。可視性マップのビットが立っていれば索引だけで可視性を判断できますが、立っていなければ結局ヒープを見に行く（＝普通のインデックススキャンと変わらない）。可視性マップを最新化するのは VACUUM です。

-- index-only scan が効いているかは EXPLAIN の "Heap Fetches" で分かる
EXPLAIN (ANALYZE) SELECT id, amount FROM orders WHERE customer_id = 42;
--  Index Only Scan ... Heap Fetches: 0   ← 0 が理想（ヒープを一度も見ていない）

設計の含意：カバリング索引は「更新が穏やかで、よく VACUUM される表」でこそ効きます。更新が激しい表に payload 列を詰め込んでも、可視性マップが追いつかず Heap Fetches が増えて意味が薄れます（MVCC/VACUUM の記事）。

4. 部分インデックス：索引を「必要な行だけ」に絞る

部分インデックス（WHERE 付き）は、条件を満たす行だけを索引化します。索引が小さくなり、更新コストも下がります。公式が挙げる3つの定石：

① よくある値を索引しない：数%以上の行に出る値は、どのみち索引が使われない。除外すれば索引が小さくなる。

-- 大半が status='done' のタスク表。未完了だけを索引（active な行は少数）
CREATE INDEX idx_tasks_open ON tasks (assignee_id)
  WHERE status <> 'done';

② 関心のない行を索引から外す：典型的なクエリが見ない行（論理削除済みなど）を最初から除外。

CREATE INDEX idx_users_active ON users (email)
  WHERE deleted_at IS NULL;

③ 部分集合だけにユニーク制約：「アクティブな行の中だけ重複禁止」を表現できる。

-- 「成功した試行」の (subject, target) だけ一意にしたい
CREATE UNIQUE INDEX uq_tests_success
  ON tests (subject, target) WHERE success;

-- 実務例：1ユーザーにつき「有効なサブスクは1つだけ」を DB で保証する
CREATE UNIQUE INDEX uq_one_active_subscription
  ON subscriptions (user_id) WHERE status = 'active';

③は業務不変条件を DB の制約で守る強力なパターンです。アプリのif文に頼らず、データ層で「不正な状態を表現不能」にできます。

5. 式インデックス：計算結果を索引する

WHERE lower(email) = $1 のように列を関数で加工して検索する場合、素の email 索引は使えません。式インデックスは計算結果を索引化します。

-- 大小無視のメール検索を効かせる（式は二重括弧が必要なケースに注意）
CREATE INDEX idx_users_email_lower ON users (lower(email));
-- WHERE lower(email) = 'a@example.com' が索引を使う

-- 連結など関数呼び出し以外の式は二重括弧
CREATE INDEX idx_people_fullname
  ON people ((first_name || ' ' || last_name));

トレードオフは明確です。公式曰く、式インデックスは「INSERT と非HOT更新のたびに式を再計算するので維持コストが高い」。一方で検索時には再計算不要（既に格納済み）。つまり「読み取り速度 > 書き込み速度」のときに使う道具です。

6. 本番でのインデックス追加：CREATE INDEX CONCURRENTLY

通常の CREATE INDEX は対象テーブルへの書き込みをロックします。本番の稼働中テーブルでこれをやると、索引ができるまで全 INSERT/UPDATE/DELETE が止まる——事故です。

CONCURRENTLY は書き込みを止めずに索引を作ります。本番では必須の作法です。ただし公式が明記する3つの注意があります。

1. 2回スキャンするので遅い：通常ビルドより総作業量が多く、完了まで大幅に時間がかかる。
2. トランザクションブロック内では実行できない：マイグレーションツールが各DDLを暗黙トランザクションで囲む場合は要設定（後述）。
3. 失敗すると INVALID な索引が残る：デッドロックや一意制約違反で失敗すると「無効な索引」が残る。DROP INDEX して作り直すか、REINDEX INDEX CONCURRENTLY で再構築する。

-- 本番の稼働中テーブルに無停止で索引を追加
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);

-- 失敗して INVALID が残っていないか確認
SELECT indexrelid::regclass AS index, indisvalid
FROM pg_index WHERE NOT indisvalid;

-- 無効な索引は消して再実行
DROP INDEX CONCURRENTLY IF EXISTS idx_orders_status;

マイグレーションでの実装例（各ツールで「トランザクション外で実行」を指定する必要があります）。

// 例：node-pg-migrate / Drizzle などで CONCURRENTLY を使うときの考え方
// マイグレーションランナーの「トランザクションを張らない」オプションを有効にしたうえで実行する。
// （CONCURRENTLY はトランザクションブロック内では失敗するため）
export async function up(sql: (q: string) => Promise<unknown>) {
  await sql(`CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_status ON orders (status)`);
}

7. 「効いているか」「無駄がないか」を計測する

インデックスは作って終わりではありません。使われているか、重複していないかを定期的に棚卸しします。

-- 使われていない索引（idx_scan が極端に少ない＝削除候補）
SELECT
  relname        AS table,
  indexrelname   AS index,
  idx_scan       AS scans,
  pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC, pg_relation_size(indexrelid) DESC
LIMIT 20;

使われない索引は、容量を食い、書き込みを遅くし、肥大化するだけの純粋な負債です。idx_scan がほぼ0の索引は削除候補（主キーや一意制約は除く）。

B-tree の fillfactor：B-tree の既定 fillfactor は 90。更新が多い表ではページ分割を緩和するため WITH (fillfactor = 70) 等で下げる選択肢もありますが、まずは「不要な索引を消す」「列順を直す」の方が効きます。

8. インデックス設計のチェックリスト

新しい索引を足す前に、この問いに答えてください。

1. そのクエリは pg_stat_statements 上位にいるか？ いないなら、まだ索引は要らない。
2. 述語は SARGable か？ WHERE lower(col) = … や WHERE col + 1 = … は素の索引が効かない → 式インデックス or クエリ修正。
3. 複合なら列順は「等値→範囲」か？
4. 取得列まで含めて index-only scan を狙えるか？（更新が穏やかな表なら INCLUDE）
5. 行を絞れるなら部分インデックスにできないか？
6. 本番なら CONCURRENTLY で張ったか？
7. 作った後、EXPLAIN (ANALYZE, BUFFERS) で実際に使われたか確認したか？

9. まとめ

• 型を正しく選ぶ：等値・範囲・整列は B-tree、配列/JSONB/全文は GIN、巨大な時系列は BRIN。9割は B-tree で足りる。
• 複合は「等値→範囲」の列順。3列超はほぼ無駄。PG18 のスキップスキャンで先頭列制約なしのケースが一部救われる。
• カバリング索引（INCLUDE）＋index-only scan は VACUUM が効いた表でこそ光る（Heap Fetches: 0 を目指す）。
• 部分・式インデックスで「必要な行・必要な形」だけを索引化。業務不変条件を一意部分索引で DB に守らせる。
• 本番追加は CONCURRENTLY。失敗時の INVALID 索引の後始末まで手順化する。
• 定期棚卸しで使われない索引（純粋な負債）を消す。

索引は「速さ」と「書き込み・容量・肥大化」のトレードオフを、ワークロードに合わせて配分する設計行為です。作った索引が本当に効いているかは、必ず EXPLAIN で確かめましょう。

参考（PostgreSQL 18 公式ドキュメント）

• 11.2. Index Types
• 11.3. Multicolumn Indexes（列順・スキップスキャン）
• 11.9. Index-Only Scans and Covering Indexes
• 11.8. Partial Indexes
• 11.7. Indexes on Expressions
• CREATE INDEX（CONCURRENTLY・fillfactor）