PostgreSQLのインデックス: B-tree、GIN、そして使い分け

1. 概要

PostgreSQLは多くのインデックス型をサポートしていますが、B-treeとGINは日々の業務で最も頻繁に遭遇する2つの型です。インデックス型を誤ると、クエリが遅くなったり、インデックスがストレージを消費するのに役に立たなかったりします。

この記事では、B-treeとGINの動作を実践的なレベルで説明し、インデックスの正しい作成方法、EXPLAINを読んでインデックスが使われているか判定する方法、適切なインデックス型を選ぶためのチェックリストを提供します。この記事を読み終えれば、Order、Productテーブルへのインデックス追加を勘で行わずに自信を持って実行できます。

2. B-tree Index: デフォルトで最も一般的

2.1. B-treeの動作原理

B-tree（balanced tree）は値をソート順に保存し、検索、比較（<、>、BETWEEN）、そしてソート（ORDER BY）を非常に高速に実行できます — 時間複雑度はO(log n)です。これはCREATE INDEXを実行する際に型を指定しなかった場合のデフォルトのインデックス型です。

CREATE TABLE customer_order (
    id BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    status VARCHAR(20) NOT NULL,
    created_at TIMESTAMP NOT NULL,
    total_amount NUMERIC(12,2)
);

CREATE INDEX idx_order_customer_id ON customer_order (customer_id);

CREATE INDEX行はcustomer_id列にB-treeを作成します。WHERE customer_id = 123でクエリすると、PostgreSQLはツリーをトラバーサルしてテーブル全体をスキャンする代わりに高速に検索できます。

2.2. インデックスが使われているか確認する

EXPLAIN ANALYZE
SELECT * FROM customer_order WHERE customer_id = 123;

Index Scan using idx_order_customer_id on customer_order
  Index Cond: (customer_id = 123)
  Planning Time: 0.123 ms
  Execution Time: 0.045 ms

Index Scanはplannerがインデックスを使用することを選択したという意味です。Seq Scanが見えるが、インデックスが既に存在する場合、通常はテーブルが小さすぎるため（plannerは全スキャンの方が安いと判断）か、統計（statistics）が古いことが原因です — ANALYZE customer_order;を実行して更新してください。

2.3. 複合インデックスと列の順序

B-treeは複数列をサポートしていますが、列の順序が効率を決定します。

CREATE INDEX idx_order_customer_status
    ON customer_order (customer_id, status);

-- インデックスを完全に使用
SELECT * FROM customer_order
WHERE customer_id = 123 AND status = 'PAID';

-- インデックスの`customer_id`部分のみを使用
SELECT * FROM customer_order WHERE customer_id = 123;

-- このインデックスは使われません（`customer_id`がないため）
SELECT * FROM customer_order WHERE status = 'PAID';

原則として、複合インデックスは(customer_id, status)でソートされたアドレス帳のようなもので、customer_idで先に検索できますが、statusだけで直接検索することはできません。

注意: 列がクエリで独立して使われている場合、選択度が高い列（selectivityが高い、異なる値が多い）を前に配置してください。常に両方の列をまとめてクエリする場合は、最も効果的にフィルタリングする列を前に配置して、ツリーが範囲を素早く絞り込めるようにしてください。

3. GIN Index: 「1行に複数の値を含む」データ向け

3.1. B-treeが力不足な時

B-treeは1つの行が多くの「要素」を含み、それらを独立して検索する必要があるデータ型では効率的ではありません：配列（array）、JSON（jsonb）、全文検索（tsvector）です。GIN（Generalized Inverted Index）はこのケースのために設計され、値全体ではなく個々の要素にインデックスを付けます。

3.2. JSONBのためのGIN

CREATE TABLE product (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    attributes JSONB NOT NULL
);

CREATE INDEX idx_product_attributes ON product USING GIN (attributes);

INSERT INTO product (name, attributes) VALUES
('Laptop Dell', '{"brand": "Dell", "ram_gb": 16, "tags": ["laptop", "office"]}'),
('Laptop HP',   '{"brand": "HP", "ram_gb": 8, "tags": ["laptop", "gaming"]}');

SELECT * FROM product WHERE attributes @> '{"brand": "Dell"}';
SELECT * FROM product WHERE attributes -> 'tags' @> '["gaming"]';

USING GIN (attributes)はJSON列全体にインデックスを作成します。演算子@>（「contains」）はGINが最も最適化する操作です — 左側のJSONが右側の構造を含むかどうかをチェック毎回ドキュメント全体を再パースせずに。

Bitmap Heap Scan on product
  Recheck Cond: (attributes @> '{"brand": "Dell"}'::jsonb)
  ->  Bitmap Index Scan on idx_product_attributes
        Index Cond: (attributes @> '{"brand": "Dell"}'::jsonb)

3.3. 全文検索のためのGIN

ALTER TABLE product ADD COLUMN search_vector tsvector
    GENERATED ALWAYS AS (to_tsvector('simple', name)) STORED;

CREATE INDEX idx_product_search ON product USING GIN (search_vector);

SELECT name FROM product
WHERE search_vector @@ to_tsquery('simple', 'laptop & dell');

列search_vectorはnameからtsvectorを自動生成する生成列です。この列のGINインデックスは@@（match）を高速に実行できます。LIKE '%laptop%'でテーブル全体をスキャンするのではなく、大規模なテキストデータでも高速に検索できます。

3.4. 配列のためのGIN

CREATE TABLE article (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200),
    tag_list TEXT[]
);

CREATE INDEX idx_article_tags ON article USING GIN (tag_list);

SELECT * FROM article WHERE tag_list @> ARRAY['spring-boot'];

JSONB同様、GINは配列tag_listの各要素にインデックスを付けるため、「tag Xを含む記事」を検索するクエリがB-treeより何倍も高速です。

4. 比較とコスト

項目	B-tree	GIN
適したデータ	スカラー：数値、文字列、日付	配列、jsonb、tsvector
サポートされる演算子	=、<、>、BETWEEN、ORDER BY	@>、?、@@、&&
書き込み速度（INSERT/UPDATE）	高速	低速（特に大規模JSONBのUPDATE）
インデックスサイズ	より小さい	より大きい（各要素を保存）
インデックス構築速度	高速	大規模テーブルではより低速

GINは書き込みコストがより高いです。毎回のINSERT/UPDATEは複数のエントリを更新する必要があります（JSON/配列の各要素ごとに1つ）。B-treeのように1エントリではありません。

-- インデックスの実際のサイズを確認
SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'product';

5. インデックスが使われていることを確認するテスト（Spring Boot統合）

Spring Data JPAで働く場合、通常はインデックスが正しく動作することをデプロイ前に確認したいです。TestcontainersでPostgreSQL上で実テストを実行してください。

<dependency>
    <groupId>org.testcontainers</groupId>
    <artifactId>postgresql</artifactId>
    <version>1.20.1</version>
    <scope>test</scope>
</dependency>

@Testcontainers
@SpringBootTest
class ProductIndexTest {

    @Container
    static PostgreSQLContainer<?> postgres =
            new PostgreSQLContainer<>("postgres:16-alpine");

    @Autowired
    private JdbcTemplate jdbcTemplate;

    @Test
    void shouldUseGinIndexForJsonbQuery() {
        jdbcTemplate.execute("""
            INSERT INTO product (name, attributes)
            VALUES ('Laptop Dell', '{"brand": "Dell"}')
        """);

        String plan = jdbcTemplate.queryForList(
                "EXPLAIN SELECT * FROM product WHERE attributes @> '{\"brand\": \"Dell\"}'"
        ).stream()
         .map(row -> row.get("QUERY PLAN").toString())
         .reduce("", (a, b) -> a + b);

        assertThat(plan).containsIgnoringCase("idx_product_attributes");
    }
}

このテストはEXPLAINを実行（ANALYZEは不要）し、結果行を1つの文字列に統合して、planが期待されるインデックス名に言及しているか確認します。これはインデックスが実際にquery planに参加していることを確認する方法です。単に「スキーマに存在する」のではなく。

注意: EXPLAINは実際のクエリを実行せず、plannerに問い合わせるだけです。実際の実行時間を知りたい場合は、EXPLAIN ANALYZEを使用してください — ただし、それがステートメントを実行することに注意してください。DELETE/UPDATEでは使用を避けてください（トランザクションのロールバックでラップされている場合を除く）。

6. インデックス型を選ぶためのチェックリスト

• スカラー列（数値、文字列、日付）で=、<、>、またはソートでフィルタリングするクエリ → B-tree。
• 複数列を同時に「AND」形式でフィルタリングするクエリ → 複合B-tree。選択度の高い列または独立して使用される列を前に配置してください。
• JSONB列で@>、?、?&、?|を使用するクエリ → GIN。
• 全文検索（tsvectorでの@@） → GIN。
• 配列で@>、&&、<@を使用するクエリ → GIN。
• テーブルが頻繁に書き込まれ、読み込まれず、JSONBデータが頻繁に変わる → 列全体にGINをインデックスしないことを検討し、必要な部分式（expression index）だけにインデックスを付けてください。

-- 式インデックス: JSONB内の特定のキーだけにインデックスを付け、GINの列全体より軽量
CREATE INDEX idx_product_brand ON product ((attributes ->> 'brand'));

この式インデックスは実際にはattributes ->> 'brand'（テキスト値）の結果のB-treeで、常に1つの固定キーでクエリする場合に適切です。GINの柔軟性が不要な場合に使用してください。

7. よくある間違い

• 基数が低い列にインデックスを作成する（例：テーブルの百万行でstatusが3つの値しかない）：plannerは通常インデックスをスキップし、より安いため順序スキャンを選択します。インデックスがあるだけで使用されるわけではありません。
• 大量データの挿入後にANALYZEを忘れる：plannerは古い統計に基づいて行数を誤って推定し、悪いplanを選択します。
• 変更が少ないがわずかな書き込みが頻繁にある列にGINを使用する：インデックス更新の累積コストがクエリの利点より大きい場合があります — EXPLAIN ANALYZEでテストしてから決定してください。
• 複合B-treeの列順が間違っている：あまり使用されない列を前に配置すると、実際のクエリではインデックスがほぼ無用になります。
• 1つのテーブルに多すぎるインデックスを作成する：毎回のINSERT/UPDATEが関連するすべてのインデックスを更新する必要があり、書き込みパスを遅くします。でそれらのインデックスすべてが使用されていないかもしれません。

8. まとめ

B-treeはデフォルトの選択肢で、スカラーデータのほとんどのフィルタリング条件とソートに適切です。GINは「多値」データ（JSONB、配列、全文など）のための専用で、B-treeが効率的に表現できない場所で活躍します。

インデックスを追加する前に、必ずEXPLAIN ANALYZEで確認し、plannerが実際にそれを使用しているか、テーブルが高いINSERT/UPDATE頻度を持つ場合は書き込みコストを測定してください。インデックスは「念のため追加する」ものではありません — 各インデックスは読み取り速度と書き込み速度の間のトレードオフです。