B-tree、GIN、GiST インデックス：実践的な PostgreSQL ガイド

選ぶときに本当に決めていること

多くの PostgreSQL のインデックス問題は同じパターンで始まります：行が 1,000 件のときは一覧が速いのに、1,000,000 件になると遅くなる。テストでは速かった検索ボックスが本番で数秒の遅延を生む。そんなときに「どのインデックスが最適か？」と聞きたくなりますが、より良い問いは「この画面はデータベースに何をさせようとしているか？」です。

同じテーブルでも画面ごとに読み方が違えば別のインデックスが必要になります。あるビューは status で絞って created_at でソートする。別の画面はフルテキスト検索を行う。別の画面は JSON フィールドにキーが含まれているかをチェックする。別の画面は地図上の点の近くを探す。アクセスパターンが違えば、どの場面でも一つのインデックスが万能にはなりません。

インデックスを選ぶということは、アプリがデータにどうアクセスするかを選ぶことです。主に等価検索、範囲検索、ソートをするのか？ドキュメントや配列の中を検索するのか？「この場所の近くは？」や「このレンジと重なるか？」を問うのか？その答えが B-tree、GIN、GiST のどれにすべきかを決めます。

B-tree、GIN、GiST を平易に説明すると

インデックス選びは列の型よりも、クエリがその列に対して何をするかで決まります。PostgreSQL は列が「text」か「json」かではなく、=、<、@>、@@ のような演算子に基づいてインデックスを利用します。だから同じフィールドでも画面によって必要なインデックスが変わるのです。

B-tree：順序付き検索に強い

B-tree はデフォルトで最も一般的な選択です。等価検索、範囲検索、特定の順序で結果が欲しい場合に強みを発揮します。

典型的な例は、status でフィルタして created_at でソートする管理画面です。(status, created_at) の B-tree インデックスはフィルタとソート両方を助けます。B-tree はユニーク制約（unique）のためにもよく使われます。

GIN：各行が多数の検索キーを持つときに速い

GIN は「この行はこの用語/値を含むか？」という問いに向いており、1 行が多くのキーにマッチしうる場合に設計されています。一般的な例はフルテキスト検索（ドキュメントが単語を含むか）や JSONB/配列のメンバーシップ（JSON がキー/値を含むか）です。

顧客レコードに JSONB の preferences があり、画面が { "newsletter": true } を含むユーザーで絞るなら、それは GIN 的な検索です。

GiST：レンジ、ジオ、類似検索など柔軟に対応

GiST は単純な順序付けに当てはまらないデータ型のための汎用フレームワークです。レンジ（重なり、包含）、幾何/地理的なクエリ（近い/内側）、類似検索などに自然に合います。

B-tree vs GIN vs GiST を考えるときは、まず忙しい画面が使う演算子を書き出してください。正しいインデックスはそれを見れば大抵明らかになります。

一般的な画面向けの判断表（フィルタ、検索、JSON、ジオ）

多くのアプリは数パターンのインデックスで足ります。コツは画面の振る舞いをクエリの演算子に結びつけることです。

エンドポイントが異なれば複数のインデックスが共存できます。たとえば管理画面はソートのために (tenant_id, created_at) の B-tree を必要とし、検索ページは @@ のための GIN を必要とするかもしれません。両方のクエリが頻繁に使われるなら両方を保持します。

迷ったらまず演算子を見てください。インデックスはデータベースがテーブルの大部分をスキップできる時に効果を発揮します。

フィルタとソート：B-tree が勝つ場面

日常的な画面の大半では、B-tree が堅実な選択です。クエリが「ある列が特定の値に等しい、場合によってはソートしてページを表示する」なら、まず B-tree を試す価値があります。

等価フィルタは典型的なケースです。status、user_id、account_id、type、tenant_id のような列はダッシュボードや管理パネルに頻出します。B-tree は該当値に直接ジャンプできます。

範囲フィルタも B-tree に向きます。時間や数値の範囲で絞るとき、順序付き構造が役に立ちます：created_at >= ...、price BETWEEN ...、id > ...。UI に「過去 7 日」や「$50〜$100」があるなら B-tree が期待通りに働きます。

ソートとページネーションでは B-tree が大きな効果を生むことがあります。インデックスの並び順が ORDER BY と一致すれば、PostgreSQL は大きなセットをメモリでソートせずにインデックス順で返せます。

-- A common screen: "My open tickets, newest first"
CREATE INDEX tickets_user_status_created_idx
ON tickets (user_id, status, created_at DESC);

複合インデックスは単純なルールに従います：PostgreSQL はインデックスの先頭部分（leftmost）しか効率よく使えません。つまり「左から右」を意識してください。(user_id, status, created_at) なら user_id（およびオプションで status）で絞るクエリが効きます。status のみで絞るクエリは通常効きません。

部分インデックスは画面がデータの一部だけを気にする場合に強力です。よくあるスライスは「アクティブな行だけ」「soft-deleted でないもの」「最近のアクティビティのみ」などです。インデックスが小さく速くなります。

高選択性列とインデックスの追加コスト

高選択性（high-cardinality）列はユニークな値が多く、user_id や order_id、email、秒単位まである created_at などが該当します。こうした列はフィルタでテーブルを急速に絞り込めるためインデックスが効果を発揮します。

逆に低選択性列はブールや小さな列挙型です。is_active や status IN ('open','closed')、plan IN ('free','pro') のような列にインデックスを張ると期待外れになりやすいです。各値がテーブルの大部分にマッチするため、Postgres はシーケンシャルスキャンを選ぶ場合があります。

もう一つの微妙なコストは行のフェッチです。インデックスで一致する ID を見つけても、残りのカラムを得るためにテーブルにアクセスする必要があるかもしれません。クエリが少数のフィールドしか必要としないならカバリングインデックスが有効ですが、そうするとインデックス自体が大きくなり保守コストも増えます。

追加インデックスはすべて書き込みコストを伴います。挿入時は各インデックスに書き込み、インデックス化された列を更新するとそのインデックスも更新されます。「念のため」にインデックスを追加するとアプリ全体が遅くなることがあります。

実践的な指針：

• 忙しいテーブルには最初に 1〜2 個の汎用インデックスを作る。実際のフィルタとソートに基づくこと。\n- WHERE や ORDER BY で使う高選択性列を優先する。\n- ブールや小さな列挙型に安易にインデックスを張らない（他の選択性の高い列と組み合わせるか、部分インデックスにする）。\n- 新しいインデックスは、それが速くする具体的なクエリを名前で言えるときに追加する。\n 例：assignee_id（高選択性）でフィルタするサポートチケット一覧はインデックスの恩恵を受けるが、is_archived = false 単独ではあまり効かないことが多い。

検索画面：フルテキスト、プレフィックス、包含

検索ボックスは単純に見えますが、ユーザーは複数語、語形変化、適切なランキングを期待します。PostgreSQL では通常フルテキスト検索を使い、tsvector（準備済みテキスト）を保存し、ユーザー入力は tsquery（用語に分解した検索式）として扱います。

フルテキスト検索には GIN が一般的なデフォルトです。「このドキュメントはこれらの用語を含むか？」を多くの行に対して高速に答えるためです。代償として書き込みは重くなりがちです：挿入や更新のコストが増えます。

GiST でもフルテキスト検索は可能です。サイズが小さく更新コストが低いことがありますが、読み取りは通常 GIN より遅くなる傾向があります。データが頻繁に変わる（イベントのような）場合、読み書きのトレードオフが重要になります。

プレフィックス検索はフルテキストではない

プレフィックス検索は「先頭一致」で、メールのプレフィックス検索などが該当します。これはフルテキスト用ではありません。プレフィックスでは文字列の順序と一致するため、適切なオペレータクラスを使った B-tree が役立つことがあります。

ILIKE '%error%' のような「包含」検索では普通の B-tree は助けになりません。ここでトライグラムインデックスや別の検索アプローチが意味を持ちます。

フィルタ＋テキスト検索がある場合

多くの実際の画面は検索とフィルタを組み合わせます：status、assignee、日付範囲、tenant など。実用的な構成は：

• tsvector カラムに対する GIN（あるいは場合によって GiST）インデックス。\n- 最も選択性の高いフィルタ用の B-tree（例：account_id、status、created_at）。\n- インデックスは最小限に保つルールを厳守する（インデックスが多すぎると書き込みが遅くなる）。

例：refund delayed を検索しつつ status = 'open' と特定の account_id で絞るサポートチケット画面。フルテキストは関連する行を返し、B-tree がアカウントとステータスで絞り込みを助けます。

JSONB フィールド：GIN とターゲットの B-tree 式インデックスの選び方

JSONB は柔軟で便利ですが、普通の列のように扱うと遅くなりがちです。核心は単純です：JSON 内のどこでも検索するのか、それとも繰り返し特定のパスで絞るのか？

metadata @> '{"plan":"pro"}' のような包含クエリには GIN が第一候補です。ドキュメントがその形を含むかを答えるのに作られており、キー存在チェック（?、?|、?&）もサポートします。

しかしアプリが一つか二つの JSON パスで主に絞るなら、式インデックス（B-tree）がより速く小さいことが多いです。抽出した値でソートや数値比較が必要な場合にも向きます。

-- Broad support for containment and key checks
CREATE INDEX ON customers USING GIN (metadata);

-- Targeted filters and sorting on one JSON path
CREATE INDEX ON customers ((metadata->>'plan'));
CREATE INDEX ON events (((payload->>'amount')::numeric));

経験則：

• 多数のキーやタグ、ネストされた構造をユーザーが検索するなら GIN を使う。\n- いくつかの安定したパスを繰り返しフィルタするなら式インデックス（B-tree）を使う。\n- 実際の画面に出るものだけをインデックスする。すべてをインデックスしない。\n- もしパフォーマンスが数個の JSON キーに依存するなら、それらを正規のカラムに昇格することも検討する。

例：サポート画面で metadata->>'priority' で絞って created_at でソートするなら、JSON の priority パスと通常の created_at にインデックスを張る。タグやネスト属性も検索するのでなければ広範な GIN は不要です。

ジオとレンジクエリ：GiST が最適な場面

ジオとレンジの画面は GiST が明らかに向くことが多いです。GiST は「これがあれと重なるか、包含するか、近いか？」のような問いに対してテーブルの多くをスキップして高速に答えます。

ジオデータは通常点（店舗位置）、線（ルート）、多角形（配達エリア）などです。よくある画面は「近くの店舗」「10km 以内の求人」「地図のボックス内を表示」「住所がサービスエリア内か？」などです。GiST（多くは PostGIS の geometry/geography 型を通じて）を使うと空間演算子が速くなり、ほとんどの行を調べずに済みます。

レンジも同様です。PostgreSQL のレンジ型（daterange、int4range など）では重なりの判定が典型的な問いです。GiST は重なりや包含演算子を効率的に処理するため、カレンダーやスケジューリング、可用性チェックでよく使われます。

B-tree がジオに関係しないわけではありません。多くのページはまずテナント、ステータス、時間で絞ってから空間条件を適用し、最後にソートします。例えば「自社の配送だけ、過去 7 日間、近い順」。GiST が空間部分を処理し、B-tree が選択的なフィルタとソートを助けます。

インデックスを選ぶ手順

インデックス選びは列名ではなく演算子が主です。同じ列でも =、>、LIKE 'prefix%'、フルテキスト、JSON の包含、ジオ距離のどれを使うかで必要なインデックスが変わります。

クエリをチェックリストのように読みます：WHERE がどの行を選ぶか、JOIN がテーブルをどう結びつけるか、ORDER BY が出力順、LIMIT が実際に必要な行数を決めます。最初の 20 行を早く見つけることを助けるインデックスが最良な場合が多いです。

ほとんどの画面で使えるシンプルな手順：

1. 画面が使う正確な演算子を書き出す（例：status =、created_at >=、name ILIKE、meta @>、ST_DWithin）。
2. 最も選択的なフィルタかデフォルトのソートに合うインデックスをまず作る。画面が created_at DESC でソートするならそこを起点にする。
3. 同じフィルタが常に一緒に使われる場合にのみ複合インデックスを追加する。等価フィルタを先、次に範囲、最後にソートキーの順に置く。
4. 画面が常に部分集合を使うなら部分インデックスを、計算値をクエリするなら式インデックスを使う。
5. EXPLAIN ANALYZE で検証する。実行時間と読み取る行数が大幅に減れば残す。

具体例：サポートダッシュボードが status で絞って最新順にソートするなら (status, created_at DESC) の B-tree が有力な第一候補です。同じ画面が meta @> '{"vip": true}' のような JSONB フラグでも絞るなら、それは別の演算子なので通常は別の JSON 特化インデックスが必要になります。

時間の無駄と書き込みを遅くする一般的なミス

よくある失望の元は、間違った演算子に対して「正しい」インデックスを選ぶことです。PostgreSQL はクエリがインデックスで答えられる形でなければ使いません。ILIKE '%term%' を使っているのにテキスト列に普通の B-tree を張っても使われず、テーブルスキャンが発生します。

別の落とし穴は巨大なマルチカラムインデックスを「念のため」に作ることです。安全そうに見えますが保守コストが高く、実際のクエリパターンに合わないことが多いです。左端の列がフィルタに使われないなら、残りの列は効かない可能性があります。

低選択性列への過度なインデックスも要注意です。ブール列や小さな enum は、部分インデックスにしない限りほとんど効果がありません。

JSONB も特有の落とし穴があります。広範な GIN は柔軟ですが、特定のパスを頻繁に使うなら式インデックスの方が速くて小さいことが多いです。もし画面が常に payload->>'customer_id' で絞るなら、その式にインデックスを張る方が全体 JSON をインデックスするより良いです。

最後に、追加インデックスは書き込みに対して負担になります。頻繁に更新されるテーブル（チケットや注文など）では、各挿入や更新がすべてのインデックスを更新する必要があります。

インデックスを追加する前に立ち止まって確認しましょう：

• インデックスは実際のクエリが使う正確な演算子にマッチしているか？
• 広いマルチカラムインデックスを 1〜2 個の焦点を絞ったインデックスに置き換えられないか？
• 部分インデックスにして低選択性のノイズを避けられないか？
• JSONB の場合、式インデックスの方が合わないか？
• テーブルは書き込みが多く、インデックスコストがリード側の利得を上回らないか？

インデックスを追加（または残す）前の簡単なチェック

新しいインデックスを作る前に、アプリが実際に何をしているかを具体化してください。「あったら便利」なインデックスは書き込みを遅くし、ストレージを消費するだけで効果がないことがあります。

まずは上位三つの画面（または API エンドポイント）を選び、それぞれの正確なクエリ形（WHERE と ORDER BY のパターン、方向、NULL ハンドリングを含む）を書き出してください。多くの「インデックス問題」は実は「不明確なクエリ問題」です。

簡単なチェックリスト：

• 3 つの実画面をピックし、正確な WHERE と ORDER BY パターンを書き出す。\n- 演算子の種類を確認する：等価（=）、範囲（>、BETWEEN）、プレフィックス、包含、重なり、距離。\n- 各一般的な画面パターンにつき 1 つのインデックスを選び、テストして実行時間や読み取りが大きく減るものだけ残す。\n- テーブルが書き込み重めならインデックスは厳選する：余分なインデックスは書き込みコストを倍増させ、VACUUM 圧力を高める。\n- 機能変更後は再確認する。新しいフィルタやデフォルトソート、検索方式の変更は既存インデックスを無意味にすることがある。

例：ダッシュボードに新しいデフォルトソート last_activity DESC を追加した場合、status にしかインデックスがなければフィルタは速くてもソートで余分な作業が発生する可能性があります。

例：実際の画面を適切なインデックスにマッピングする

判断表は実際に出荷している画面に当てはめて初めて役に立ちます。ここに 3 つの一般的な画面とそれに合うインデックスの例を示します。

実用的な適用方法は UI から始めることです：

• 結果を絞るコントロール（フィルタ）をすべて列挙する。\n- デフォルトのソート順をメモする。\n- 検索の振る舞いを具体化する（フルテキストか、先頭一致か、包含か）。\n- “特殊” なフィールド（JSONB、ジオ、レンジ）を明示する。

次のステップ：インデックスを開発プロセスの一部にする

良いインデックスは人がクリックするフィルタ、期待するソート順、実際に使われる検索ボックスに従います。開発中からインデックスを習慣にすると、本番でのパフォーマンスの驚きを避けられます。

繰り返し可能に保つには：各画面の 1〜3 個の実際のクエリを特定し、それらに合う最小のインデックスを追加し、実データでテストし、効果がなければ削除する流れを作ることです。

社内ツールやカスタマーポータルを作るなら、インデックスニーズを早めに計画してください。これらのアプリはフィルタや一覧画面を増やして成長することが多いです。AppMaster (appmaster.io) を使っている場合は、各画面のフィルタとソート設定を具体的なクエリパターンとして扱い、その実際のクリックをサポートするインデックスだけを追加することを推奨します。