필사 모드: PostgreSQL インデックス完全ガイド: 設計から廃止までのインデックスのライフサイクル

• はじめに
• 1. インデックスのライフサイクルという視点
• 2. 設計 — どのカラムをどの順序で
  • カバリングインデックスとINCLUDE
• 3. 方式選択 — 六つのうちどれを
• 4. 部分インデックスと式インデックス
• 5. 作成 — CONCURRENTLYと失敗時の復旧
  • 失敗したとき
  • パーティションテーブルの例外
• 6. 検証 — 本当に使われているか
• 7. 運用 — 膨張したインデックスを扱う
• 8. 廃止 — 元に戻せる形で削除する
• クイズ: 理解度を確認しましょう
• おわりに
• 参考資料
• 続けて読む

はじめに

インデックスについての文章はたいてい二種類に分かれます。一つはB-treeとは何かを説明するデータ構造の話、もう一つは「作ったインデックスがなぜ使われないのか」を扱うオプティマイザの話です。このブログにも両方あります。PostgreSQL 高度なインデックス設計完全ガイドが前者に近く、インデックスを作ったのに使われない理由が後者です。

この記事は三つ目の視点を取ります。インデックスをライフサイクルを持つ運用資産として扱う視点です。インデックスは作られ、検証され、老化し、最終的には削除されます。現場で事故が起きるのはたいてい「どんなデータ構造か」ではなく「いつどうやって作り、いつどうやって消すか」という地点です。運用中のテーブルにインデックスを作って書き込みを数分間止めてしまったり、誰も使っていないインデックスが十二個も毎回のINSERTでコストを請求し続けていたりする状況のことです。

基準エンジンはPostgreSQL 18です。パラメータの既定値、ロックレベル、EXPLAINの動作はすべてPostgreSQL 18の文書を読んで記載しており、バージョンによって変わる項目はその都度明記しています。MySQLのインデックスはクラスタ化インデックスというまったく異なる前提の上に成り立っているため、この記事では混同しません。

1. インデックスのライフサイクルという視点

インデックスを一つ作ると、その瞬間からデータベースは三つのコストを払い続けます。

• 書き込みコスト — 該当テーブルへのINSERT、UPDATE、DELETEのたびにインデックスエントリを更新しなければなりません。インデックスが十個あれば十回です。
• 容量コスト — インデックスもディスクとバッファキャッシュを占有します。キャッシュ内でインデックスが場所を取れば、その分テーブルデータが押し出されます。
• メンテナンスコスト — VACUUMはテーブルだけでなくインデックスも掃除しなければなりません。インデックスが多いほどVACUUMが長引きます。

読み取りの利点はよく見えますが、この三つのコストはよく見えません。だからインデックスは増える一方で減りません。ライフサイクルという視点は、この非対称性を正そうとする試みです。インデックスごとに「なぜ作ったのか」と「いつ削除するのか」を一緒に管理しようということです。

ライフサイクルは六つの段階に分けられます。設計、方式選択、作成、検証、運用、廃止です。以下の節ではこの順序をそのまま踏襲します。

2. 設計 — どのカラムをどの順序で

複合インデックスにおいてカラムの順序は、性能を数倍ではなく桁単位で変えます。順序を決める規則はシンプルです。

等価条件に使うカラムを先に、範囲条件に使うカラムを後に置きます。B-treeインデックスは先頭カラムから順に整列されているため、範囲条件が一度登場すると、それ以降のカラムはもう検索条件として絞り込みに使われず、フィルタとしてのみ残ります。

-- 頻繁に実行されるクエリ
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42
  AND status = 'PAID'
  AND created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;

-- 良い順序: 等価(tenant_id, status)の後に範囲/整列(created_at)
CREATE INDEX idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

-- 悪い順序: 範囲カラムが先頭だと後ろの等価条件をインデックスで絞り込めない
CREATE INDEX idx_orders_created_tenant_status
  ON orders (created_at DESC, tenant_id, status);

整列までインデックスに乗せるには、ORDER BYの方向をインデックス定義に反映する必要があります。上の例でcreated_at DESCとして作っておけば、整列演算そのものが消えます。PostgreSQLはインデックスを逆方向にも読めるため単一カラムの整列であれば方向はさほど重要ではありませんが、複合整列(a ASC, b DESC)ではインデックス定義が正確に一致して初めて整列を省略できます。

カバリングインデックスとINCLUDE

インデックスだけを読んでテーブルを訪問しない実行方式をIndex Only Scanと呼びます。参照するカラムがすべてインデックスにあれば可能です。検索条件には使わないが結果として必要なカラムはINCLUDE句に入れる方が良いです。

CREATE INDEX idx_orders_cover
  ON orders (tenant_id, status)
  INCLUDE (total_amount, created_at);

PostgreSQLの文書はINCLUDEで指定した非キーカラムについて、「非キーカラムはインデックススキャンの検索条件には使えず、一意性や排他制約を判定する際にも無視される」と明記しています。その代わりインデックスエントリから値を直接取り出せるため、Index Only Scanが可能になります。INCLUDEはB-tree、GiST、SP-GiSTでのみサポートされ、非キーカラムを持つB-treeインデックスは重複排除(deduplication)が無効になります。

一つ注意点があります。Index Only Scanが実際にテーブルを飛ばすには、可視性マップ(visibility map)で該当ページが全体可視(all-visible)と記録されている必要があります。VACUUMが回っていない更新直後のテーブルでは、Index Only Scanと表示されていてもHeap Fetchesの値が大きく出ます。実行計画でこの数値を必ず確認してください。

3. 方式選択 — 六つのうちどれを

PostgreSQLは六つのインデックス方式を提供します。btree、hash、gist、spgist、gin、brinで、USING句を省略すればB-treeになります。データ構造の説明より判断基準の方が重要なので、表にまとめます。

文書が明記するB-treeの追加機能を二つ覚えておくと実務でよく使えます。一つ目は、LIKE 'foo%'のように先頭が固定されたパターンはB-treeが処理できることです。LIKE '%foo'は処理できません。二つ目は、B-treeだけが整列済みの順序でデータを返せることです。

BRINは誤解の多い方式です。BRINは連続したブロック範囲の要約(最小値と最大値)しか保存しないため、インデックスサイズが極端に小さくなります。ただし文書が強調する通り、カラムの値が物理的な行の順序とよく相関しているときだけ効果があります。時系列でのみ積み上がるログテーブルのcreated_atには最適で、ランダムに更新される状態カラムには役に立ちません。

4. 部分インデックスと式インデックス

インデックスを小さく保つための二つの道具です。実務で最も過小評価されている機能でもあります。

部分インデックスはWHERE句で索引対象の行を制限します。全行の1%しか照会対象にならないなら、インデックスも1%だけ作ればよいのです。

-- 未処理のジョブだけを照会するキューテーブル
CREATE INDEX idx_jobs_pending
  ON jobs (created_at)
  WHERE status = 'PENDING';

-- ソフト削除された行を除いた一意制約
CREATE UNIQUE INDEX idx_users_email_active
  ON users (lower(email))
  WHERE deleted_at IS NULL;

二つ目の例は部分インデックスと式インデックスを組み合わせたもので、ソフト削除を使うスキーマで「生きている行同士だけでメールアドレスが一意であるべき」という要件を制約として表現する定石です。アプリケーションコードで検査すると同時実行の穴が生まれますが、一意インデックスならデータベースが保証します。

式インデックスはカラムではなく式にインデックスを張ります。注意点は、クエリの式とインデックスの式が正確に一致しなければならないことです。lower(email)でインデックスを作ったなら、クエリもlower(email) = ...でなければならず、email ILIKE ...はこのインデックスを使えません。

式インデックスは統計にも影響します。PostgreSQLは式インデックスに対して別途統計を収集するため、インデックスを作るだけで行数推定が正確になる副次効果があります。インデックスなしで統計だけ欲しければ、CREATE STATISTICSで式の統計だけを別途作ることもできます。

5. 作成 — CONCURRENTLYと失敗時の復旧

ここが運用事故の最も多い地点です。

警告: 通常のCREATE INDEXは対象テーブルへの書き込みを完了まで止めます。PostgreSQLの文書は、通常のCREATE INDEXが「テーブルへの書き込み(挿入、更新、削除)を完了するまでロックする」と明記しています。読み取りは許可されますが書き込みは待たされます。数億行のテーブルでは、このコマンドは数十分にわたってサービスの書き込み経路を止めます。運用中のテーブルには必ずCONCURRENTLYを付けてください。

-- 運用テーブルにはこれだけを使う
CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created
  ON orders (tenant_id, status, created_at DESC);

CONCURRENTLYは挿入、更新、削除を止めるロックを取りません。その代わり代償があります。文書によれば、二回のテーブルスキャンを二つの別々のトランザクションで実行し、各スキャンの前にインデックスを変更または使用しうる既存のトランザクションがすべて終わるのを待ちます。二回目のスキャンの後には、そのスキャンより前のスナップショットを持つトランザクションが終わるのをさらに待ちます。そのため長時間開いたままの一つのトランザクションがインデックス作成全体を無期限に遅延させることがあります。インデックスを作る前にpg_stat_activityで長寿命トランザクションを確認する習慣をつけてください。

また、CREATE INDEX CONCURRENTLYはトランザクションブロックの中では実行できません。マイグレーションツールがすべてのマイグレーションを一つのトランザクションで包む設定になっていると、このコマンドは失敗します。ツールごとにトランザクションを無効化するオプションを探しておく必要があります。

失敗したとき

問題が起きるとコマンドは失敗しますが、無効(invalid)なインデックスが残ります。文書の表現によれば、このインデックスは「不完全な可能性があるためクエリでは無視されるが、更新のオーバーヘッドは発生し続ける」状態です。つまりコストだけ払って利益がない最悪の状態です。

-- 無効なインデックスを探す
SELECT c.relname AS index_name, i.indisvalid, i.indisready
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE NOT i.indisvalid;

-- 復旧: 削除して作り直すか
DROP INDEX CONCURRENTLY idx_orders_tenant_status_created;
-- または再構築
REINDEX INDEX CONCURRENTLY idx_orders_tenant_status_created;

インデックス作成作業が終わったと報告する前に、この照会を一度実行することを手順に入れてください。無効なインデックスは静かに残ります。

パーティションテーブルの例外

文書は「パーティション化されたテーブルへのインデックスの同時作成は現在サポートされていない」と明言しています。代わりに推奨される回避策があります。各パーティションに個別にCONCURRENTLYでインデックスを作ってから、最後に親のパーティションテーブルに非同時的にインデックスを作れば、書き込みがロックされる時間を減らせます。親にインデックスを作る際、すでに名前の一致するインデックスが各パーティションにあれば、それをアタッチして使います。

6. 検証 — 本当に使われているか

作ったインデックスが使われているかを確認する方法は二つの層があります。

クエリ単位ではEXPLAINを見ます。PostgreSQL 18ではANALYZEを使うとバッファ情報が自動的に含まれます。文書に「Buffers information is automatically included when ANALYZE is used」と明記されています。17以下ではBUFFERSを明示的に付ける必要がありました。

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, total_amount
FROM orders
WHERE tenant_id = 42 AND status = 'PAID'
ORDER BY created_at DESC
LIMIT 50;

 Limit  (cost=0.56..42.18 rows=50 width=20)
        (actual time=0.041..0.180 rows=50 loops=1)
   Buffers: shared hit=54
   ->  Index Only Scan Backward using idx_orders_cover on orders
         (cost=0.56..8321.44 rows=10004 width=20)
         (actual time=0.039..0.171 rows=50 loops=1)
         Index Cond: ((tenant_id = 42) AND (status = 'PAID'::text))
         Heap Fetches: 0
         Buffers: shared hit=54
 Planning Time: 0.212 ms
 Execution Time: 0.221 ms

ここで見るべきは三つです。Index Condに条件が入っているか(入らずにFilterにあればインデックスで絞り込めていません)、Heap Fetchesが0か、そしてrowsの推定値と実際の値の差です。

警告: EXPLAIN ANALYZEは文をそのまま実行します。文書が明記する通り、SELECTの出力は捨てられますが他の副作用はそのまま発生します。INSERT、UPDATE、DELETE、MERGEを分析するときはBEGIN / EXPLAIN ANALYZE ... / ROLLBACKで包んでください。

ワークロード単位では統計ビューを見ます。pg_stat_user_indexesのidx_scanは該当インデックスで開始されたスキャンの回数で、last_idx_scanは最後にスキャンされた時刻です。

SELECT s.schemaname, s.relname, s.indexrelname,
       s.idx_scan, s.last_idx_scan,
       pg_size_pretty(pg_relation_size(s.indexrelid)) AS size
FROM pg_stat_user_indexes s
JOIN pg_index i ON i.indexrelid = s.indexrelid
WHERE NOT i.indisunique
ORDER BY s.idx_scan ASC, pg_relation_size(s.indexrelid) DESC;

この数値を読むときは二つ注意が必要です。一つ目は、統計は最後の初期化以降の累積値だということです。いつ初期化したか分からなければ0の意味も分かりません。二つ目は、月一回のバッチや四半期決算レポートだけに使われるインデックスは、一週間の観察では0に見えることです。少なくとも一回の業務サイクル全体を観察してください。

7. 運用 — 膨張したインデックスを扱う

PostgreSQLのMVCCでは、更新が新しい行バージョンを作り、インデックスもそれに合わせてエントリを追加します。死んだエントリはVACUUMが整理しますが、整理された空間が常に再利用可能な形で残るとは限りません。時間が経つとインデックスは論理的なデータ量より大きくなります。これがインデックスbloatです。

症状は静かです。クエリが急に遅くなるのではなく、少しずつ遅くなります。インデックスがキャッシュに入りにくくなり、スキャンがより多くのページを読みます。

対応は再構築です。

警告: REINDEXをCONCURRENTLYなしで実行すると、対象テーブルにACCESS EXCLUSIVEロックがかかります。このロックはすべてのロックモードと衝突するため、読み取りまで完全に止まります。同じ理由でVACUUM FULLとCLUSTERも運用中にむやみに使ってはいけません。PostgreSQLの文書自体が「管理者は通常のVACUUMを使い、VACUUM FULLは避けるよう努めるべきだ」と勧めています。安全な代替策はREINDEX INDEX CONCURRENTLYです。このコマンドはSHARE UPDATE EXCLUSIVEロックしか取らないため、読み取りと書き込みを止めません。

-- 安全: 読み取りと書き込みを止めない
REINDEX INDEX CONCURRENTLY idx_orders_tenant_status_created;

-- テーブルのすべてのインデックスを同時に再構築
REINDEX TABLE CONCURRENTLY orders;

REINDEX ... CONCURRENTLYも失敗すると無効なインデックスを残すことがあります。名前の末尾に_ccnewが付いた残留インデックスがあれば整理が必要です。

再構築の周期を決めるときは更新パターンを見てください。追記専用に近いテーブルはインデックスがあまり膨張しません。同じ行を繰り返し更新する状態テーブルやキューテーブルは急速に膨張します。キューテーブルは部分インデックスと組み合わせるとbloatの影響が大きく減ります。

8. 廃止 — 元に戻せる形で削除する

使われていないインデックスは削除すべきです。ただし「使われていない」という判断が間違っている可能性があるため、元に戻せる手順で進めます。

まず重複インデックスを探します。(a)と(a, b)の両方があれば、前者はたいてい不要です。B-treeは先頭カラムだけを使う条件も処理できるからです。ただし(a)が一意インデックスであるか、(a)だけでIndex Only Scanが成立する場合は例外です。

廃止の手順は三段階です。

第1段階 — 候補の選定。6節の照会でidx_scanが0か極端に低いインデックスを抽出します。一意制約を支えるインデックスと外部キーの参照側インデックスは除外します。外部キーが設定された子テーブルにインデックスがないと、親行の削除時に全表スキャンが発生します。

第2段階 — 無効化の実験。削除する前に、まずオプティマイザに無視させます。PostgreSQLにはインデックスを無効化する公式コマンドはありませんが、システムカタログを直接書き換えて無効と表示させる方法が知られています。ただしカタログの直接修正は危険なため、より安全な方法はセッション単位でenable_indexscanとenable_bitmapscanを無効にし、代表的なクエリの計画と実行時間を比較することです。この二つのパラメータの既定値はどちらもonです。

-- セッションでのみインデックス経路を除外し、最悪の場合を測定する
SET enable_indexscan = off;
SET enable_bitmapscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ...;
RESET enable_indexscan;
RESET enable_bitmapscan;

第3段階 — 削除。削除も同時モードで行います。

DROP INDEX CONCURRENTLY idx_orders_legacy_status;

そして必ず再作成スクリプトを一緒にコミットしておいてください。インデックス削除のロールバックは再作成であり、再作成には時間がかかります。障害時に定義を記憶から復元しようとすると失敗します。pg_get_indexdef()で定義を事前に控えておくのが良い方法です。

SELECT indexrelid::regclass AS index_name,
       pg_get_indexdef(indexrelid) AS definition
FROM pg_index
WHERE indrelid = 'orders'::regclass;

クイズ: 理解度を確認しましょう

Index Only Scan using idx_orders_cover on orders
  (actual time=0.05..820.4 rows=48000 loops=1)
  Index Cond: (tenant_id = 42)
  Heap Fetches: 47912
  Buffers: shared hit=1204 read=46180

SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

CREATE UNIQUE INDEX CONCURRENTLY idx_users_email_alive
  ON users (lower(email))
  WHERE deleted_at IS NULL;

CREATE INDEX idx_a ON events (tenant_id);
CREATE INDEX idx_b ON events (tenant_id, occurred_at);

おわりに

インデックス問題の大半は知識不足ではなく手順の不在から来ます。B-treeが何かを知っている人は多いですが、運用テーブルにインデックスを作るときにCONCURRENTLYを付け、長寿命トランザクションを先に確認し、終わったら無効なインデックスを照会するという手順を持つチームは少ないです。

三つだけをチームのルールにしても事故の大半は消えます。一つ目は、運用テーブルのインデックス作成・削除・再構築は常にCONCURRENTLYで行うことです。二つ目は、インデックスを作るときになぜ作るのか、どのクエリのためのものかをマイグレーションファイルのコメントに残すことです。三つ目は、四半期に一度idx_scanを照会して廃止候補を検討することです。

ここで登場したSQLはこのサイトのPostgres プレイグラウンドでそのまま実行してみることができます。

参考資料

• PostgreSQL 18, CREATE INDEX: https://www.postgresql.org/docs/18/sql-createindex.html (2026-08-15 確認)
• PostgreSQL 18, Index Types: https://www.postgresql.org/docs/18/indexes-types.html (2026-08-15 確認)
• PostgreSQL 18, EXPLAIN: https://www.postgresql.org/docs/18/sql-explain.html (2026-08-15 確認)
• PostgreSQL 18, Explicit Locking: https://www.postgresql.org/docs/18/explicit-locking.html (2026-08-15 確認)
• PostgreSQL 18, Routine Vacuuming: https://www.postgresql.org/docs/18/routine-vacuuming.html (2026-08-15 確認)
• PostgreSQL 18, The Statistics Collector: https://www.postgresql.org/docs/18/monitoring-stats.html (2026-08-15 確認)
• PostgreSQL 18, Query Planning: https://www.postgresql.org/docs/18/runtime-config-query.html (2026-08-15 確認)
• PostgreSQL 18, Client Connection Defaults: https://www.postgresql.org/docs/18/runtime-config-client.html (2026-08-15 確認)

続けて読む

• 次回: SQL実行計画完全ガイド — オプティマイザがインデックス経路を選ぶ過程
• PostgreSQL 高度なインデックス設計完全ガイド — GIN、GiST、BRINの内部構造
• インデックスを作ったのに使われない理由 — オプティマイザがインデックスを拒否するケース
• PostgreSQL VACUUMとMVCCの内部構造 — bloatが生まれる仕組み
• Postgres プレイグラウンド — この記事のSQLを直接実行してみる
• SQL プレイグラウンド — クエリ構文の実験