PostgreSQLインデックスの種類｜B-tree/GIN/GiST/BRIN実測

PostgreSQLのインデックスの種類は、B-tree・Hash・GiST・SP-GiST・GIN・BRINの6つです。 迷ったら B-tree、配列・jsonb・全文検索・部分一致（LIKE '%…%'）なら GIN、範囲型や位置情報の「重なり・近さ」なら GiST（または SP-GiST）、時系列で物理順に並んだ巨大テーブルの範囲検索なら BRIN、という使い分けになります。

この記事では、PostgreSQL 16.13 で 100 万行のテーブルを実際に作り、6 種類すべてのインデックスの「作成時間・サイズ・EXPLAIN ANALYZE の実行時間」を測った結果を表にまとめました。公式ドキュメントや図解記事にはない「列の型 × クエリの形 → 使うインデックス」の逆引き表、コピペで使える CREATE INDEX 集（部分・式・複合・INCLUDE・CONCURRENTLY・pg_trgm）、BRIN が効く条件の実測、使われていないインデックスの見つけ方と肥大化の直し方まで、1 本で押さえられる構成です。

PostgreSQLインデックス6種類の早見表

実測はいずれも PostgreSQL 16.13（2 vCPU・8 GB・設定は既定値）で 2 回実行した 2 回目（キャッシュに乗った状態）の値です。数値は台数・設定・負荷で変わるので、比率の目安としてご覧ください。

検証環境と100万行テーブルの作り方

注文（orders）を題材に、インデックスごとに向いた型の列を用意しました。psql で \timing on を付けると各 SQL の所要時間が表示されます。

-- PostgreSQL 16.13 / psql（\timing on）
CREATE EXTENSION IF NOT EXISTS pg_trgm;       -- 部分一致用（標準同梱）
CREATE EXTENSION IF NOT EXISTS pgstattuple;   -- 肥大化の確認用（標準同梱）

CREATE TABLE orders (
  id          integer PRIMARY KEY,
  customer_id integer   NOT NULL,
  email       text      NOT NULL,
  status      text      NOT NULL,
  amount      integer   NOT NULL,
  memo        text      NOT NULL,   -- ランダムな英数字64文字（部分一致検索用）
  tags        text[]    NOT NULL,   -- 配列（GIN用）
  period      tsrange   NOT NULL,   -- 範囲型（GiST / SP-GiST用）
  created_at  timestamp NOT NULL,   -- 挿入順に並ぶ（BRINが効く）
  random_ts   timestamp NOT NULL    -- バラバラ（BRINが効かない）
);

INSERT INTO orders
SELECT i,
       1 + (random() * 99999)::int,
       'User' || i || '@example.com',
       CASE WHEN r < 0.02 THEN 'pending' WHEN r < 0.10 THEN 'paid'
            WHEN r < 0.30 THEN 'shipped' ELSE 'done' END,
       (random() * 10000)::int,
       md5(i::text) || md5((i * 7)::text),
       ARRAY[(ARRAY['new','repeat','gift','bulk','vip','sale','rush','export','sample','wholesale'])[1 + (random() * 9)::int],
             (ARRAY['web','store','phone','partner','campaign'])[1 + (random() * 4)::int]],
       tsrange(ts, ts + (1 + random() * 72) * interval '1 hour'),
       ts,
       timestamp '2024-01-01' + random() * interval '347 days'
FROM (SELECT i, random() AS r,
             timestamp '2024-01-01' + i * interval '30 seconds' AS ts
      FROM generate_series(1, 1000000) AS i) s;

UPDATE orders SET tags = tags || ARRAY['priority'] WHERE id % 1000 = 0;  -- 希少タグを1,000行に
VACUUM ANALYZE orders;

INSERT 0 1000000
Time: 11523.642 ms (00:11.524)   -- テーブルサイズ 211 MB

created_at は挿入順に増え、random_ts はバラバラです。この違いが、後述する BRIN の実測にそのまま表れます。

実測結果：6種類の作成時間・サイズ・実行時間

同じクエリを「インデックスなし（Seq Scan）」と「インデックスあり」で EXPLAIN (ANALYZE, BUFFERS) し、Execution Time を比べました。

読み取れることは 4 つです。

• B-tree は等価も範囲も速く、サイズも小さい。 Hash は = では B-tree と同程度ですが、BETWEEN では Parallel Seq Scan（Rows Removed by Filter: 332984）に戻り、今回はサイズも約 3.4 倍でした。Hash は「= でしか検索しない」列専用と覚えておくと迷いません。
• GIN は「値 → その値を含む行の一覧」を持つ転置インデックスで、「配列に含む」「部分一致」を 100 倍以上速くします。一方、pg_trgm の GIN はテーブル（211 MB）に近い 175 MB、作成に 22 秒かかりました。ヒットが多いクエリ（tags @> ARRAY['vip','phone']、27,859 行）では 62.7 ms と全件走査（120〜190 ms）との差が縮むので、ヒットが少ない検索ほど GIN が活きます。
• GiST と SP-GiST は同じ範囲検索に使え、どちらも Index Only Scan になりましたが、今回のデータでは GiST の方が検索も作成も速い結果でした（読んだバッファは GiST 63 ブロック、SP-GiST 1,480 ブロック）。範囲型はまず GiST、データの偏りが強いときに SP-GiST を試すのが実務的な順番です。
• BRIN は 24 kB で B-tree（21 MB）とほぼ同じ速さを出せますが、列が物理順に並んでいないと効きません（後述）。

列の型×クエリの形で選ぶ（逆引き表）

「どの列に、どんな WHERE を書くか」で決めます。 迷ったら上から順に当てはめてください。

種類ごとにできること・できないことも押さえておくと設計で迷いません。

GIN・GiST に通常の数値列も含めた複合インデックスを作りたいときは、標準同梱の拡張 btree_gin / btree_gist を使います。

コピペ用 CREATE INDEX 集（実行結果つき）

すべて上の orders テーブルで実行し、EXPLAIN の要点行と Execution Time を添えています。

部分インデックス（一部の行だけ）

-- status = 'pending'（全体の約2%）の行だけを持つ小さなインデックス
CREATE INDEX idx_orders_pending ON orders (created_at) WHERE status = 'pending';
-- サイズ 456 kB（全行の created_at に張ると 21 MB）
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders WHERE status = 'pending' AND created_at >= '2024-11-01';
--   ->  Index Only Scan using idx_orders_pending on orders (actual time=0.008..0.239 rows=2424 loops=1)
--  Execution Time: 0.368 ms

WHERE 句がインデックスの条件と一致する場合だけ使われます。 status = 'paid' で検索すると、この部分インデックスは使われず、別のインデックスで 111,819 行をフィルタしたため 17.6 ms かかりました。

式インデックス（関数の結果に張る）

CREATE INDEX idx_orders_email_lower ON orders (lower(email));
EXPLAIN (ANALYZE, COSTS OFF) SELECT id FROM orders WHERE lower(email) = 'user12345@example.com';
--   ->  Bitmap Index Scan on idx_orders_email_lower (actual time=0.005..0.005 rows=1 loops=1)
--         Index Cond: (lower(email) = 'user12345@example.com'::text)
--  Execution Time: 0.015 ms

クエリ側も同じ式 lower(email) で書くのが条件です。WHERE email = 'User12345@example.com' と書くと、この式インデックスは使われず Parallel Seq Scan（56.9 ms）になりました。

複合インデックス（列の順番が大事）

-- 「この会員の最新5件」：等価条件の列を先、並べ替えの列を後に
CREATE INDEX idx_orders_cust_created ON orders (customer_id, created_at DESC);
EXPLAIN (ANALYZE, COSTS OFF)
SELECT id, created_at FROM orders WHERE customer_id = 12345 ORDER BY created_at DESC LIMIT 5;
--  Limit (actual time=0.005..0.008 rows=5 loops=1)
--    ->  Index Scan using idx_orders_cust_created on orders (actual time=0.005..0.007 rows=5 loops=1)
--          Index Cond: (customer_id = 12345)
--  Execution Time: 0.014 ms

Sort ノードが出ていない点に注目してください。インデックスの並び順がそのまま ORDER BY に使われるので、並べ替えのコストがゼロになります。先頭の列（customer_id）を使わないクエリには効きにくいため、「等価で絞る列を先頭、範囲や並べ替えの列を後ろ」が基本です。

INCLUDE（Index Only Scan を狙う）

-- customer_id で検索して amount を集計するクエリを、テーブルを読まずに済ませる
CREATE INDEX idx_orders_cust_incl ON orders (customer_id) INCLUDE (amount);
EXPLAIN (ANALYZE, COSTS OFF) SELECT sum(amount) FROM orders WHERE customer_id = 12345;
--    ->  Index Only Scan using idx_orders_cust_incl on orders (actual time=0.006..0.008 rows=13 loops=1)
--          Heap Fetches: 0
--  Execution Time: 0.023 ms

Heap Fetches: 0 は、テーブル本体を 1 ページも読まずに答えが出たことを示します。INCLUDE 列は検索条件には使えませんが、キーを増やさずに取り出せる列を足せます（B-tree・GiST・SP-GiST で使えます）。

CONCURRENTLY（本番で書き込みを止めずに作る）

-- 通常の CREATE INDEX はテーブルへの書き込みを待たせる。CONCURRENTLY は止めない（所要時間は約2倍）
CREATE INDEX CONCURRENTLY idx_orders_status ON orders (status);   -- 今回 530 ms
-- 途中で失敗すると INVALID なインデックスが残るので確認する
SELECT indexrelid::regclass, indisvalid FROM pg_index WHERE indexrelid = 'idx_orders_status'::regclass;
--     indexrelid     | indisvalid
--  idx_orders_status | t

indisvalid = f のときは DROP INDEX して作り直すか、REINDEX INDEX CONCURRENTLY で有効にします。トランザクションの中（BEGIN〜COMMIT）では実行できないので、マイグレーションツールではトランザクション外で実行する設定が必要です。

pg_trgm の GIN（LIKE '%…%' を速くする）

CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_gin_memo_trgm ON orders USING gin (memo gin_trgm_ops);   -- 今回 22.1 秒 / 175 MB
EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM orders WHERE memo LIKE '%7a3f9%';

 Aggregate (actual time=1.623..1.623 rows=1 loops=1)
   ->  Bitmap Heap Scan on orders (actual time=1.559..1.617 rows=59 loops=1)
         Recheck Cond: (memo ~~ '%7a3f9%'::text)
         Rows Removed by Index Recheck: 17
         Heap Blocks: exact=76
         ->  Bitmap Index Scan on idx_gin_memo_trgm (actual time=1.548..1.549 rows=76 loops=1)
               Index Cond: (memo ~~ '%7a3f9%'::text)
 Execution Time: 1.638 ms

全件走査の 169〜192 ms が 1.64 ms になりました。Rows Removed by Index Recheck: 17 は、3 文字ずつ（トライグラム）の一致で候補を絞ったあと、本当に一致するかを確認して外された行数です。検索文字列は 3 文字以上にすると効果が出ます。ILIKE や正規表現 ~ にも使えます。

そのほか、よく使う形

CREATE UNIQUE INDEX idx_orders_email_uq ON orders (email);                 -- UNIQUE（B-tree のみ）
CREATE INDEX idx_t_attrs ON t USING gin (attrs jsonb_path_ops);            -- jsonb（@> 専用で小さく速い）
CREATE INDEX idx_t_fts ON t USING gin (to_tsvector('simple', body));       -- 全文検索
ALTER TABLE reservations ADD CONSTRAINT no_overlap
  EXCLUDE USING gist (room_id WITH =, period WITH &&);                     -- 期間が重ならない排他制約
CREATE INDEX idx_t_col_desc ON t (col DESC NULLS LAST);                    -- ORDER BY col DESC NULLS LAST 用

BRINが効く条件を実測で確かめる（物理順に並んでいるか）

BRIN は「ブロック範囲（既定 128 ページ）ごとの最小値・最大値」だけを持つインデックスです。値の順と物理的な並び順が一致している列なら、読むべきブロック範囲が一瞬で絞れます。逆に並びがバラバラだと、どの範囲にも該当値が含まれるため絞れません。

まず pg_stats の correlation を見る

SELECT attname, correlation FROM pg_stats
 WHERE tablename = 'orders' AND attname IN ('created_at', 'random_ts', 'customer_id');

   attname   |  correlation
-------------+---------------
 created_at  |     0.9978262
 customer_id | -0.0045842514
 random_ts   |   0.009743469

correlation が 1 または -1 に近い列が BRIN 向きです。0 付近の列に張っても効きません。

物理順に並んだ列（created_at）：24 kB で 0.85 ms

CREATE INDEX idx_brin_created ON orders USING brin (created_at);   -- 0.18 秒 / 24 kB
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF)
SELECT count(*) FROM orders WHERE created_at BETWEEN '2024-06-01' AND '2024-06-02';

 Aggregate (actual time=0.836..0.837 rows=1 loops=1)
   Buffers: shared hit=130
   ->  Bitmap Heap Scan on orders (actual time=0.120..0.682 rows=2881 loops=1)
         Rows Removed by Index Recheck: 1864
         Heap Blocks: lossy=128
         ->  Bitmap Index Scan on idx_brin_created (actual time=0.031..0.031 rows=1280 loops=1)
 Execution Time: 0.850 ms

Heap Blocks: lossy=128 が BRIN の特徴で、「このブロック範囲に該当行がある」とだけわかるので、128 ページを読んで 1,864 行を除外（Recheck）しています。それでも全件走査（約 27,000 ブロック）より圧倒的に少なく、21 MB の B-tree（0.44 ms）とほぼ同じ速さを 24 kB で実現できています。WITH (pages_per_range = 16) にすると 72 kB・読むブロック 112 と精度は上がりますが、今回の規模では実行時間（0.92 ms）に差は出ませんでした。数億行のログやイベントのように B-tree のサイズ自体が負担になるテーブルで、BRIN の価値が最大になります。

バラバラな列（random_ts）：プランナが使わない

CREATE INDEX idx_brin_random ON orders USING brin (random_ts);   -- 0.18 秒 / 24 kB
EXPLAIN (ANALYZE, COSTS OFF)
SELECT count(*) FROM orders WHERE random_ts BETWEEN '2024-06-01' AND '2024-06-02';
--               ->  Parallel Seq Scan on orders (actual time=0.070..64.985 rows=952 loops=3)
--                     Rows Removed by Filter: 332381
--  Execution Time: 72.418 ms

インデックスがあるのに Seq Scan が選ばれました。プランナは correlation を見て「BRIN を使っても絞れない」と判断しています。SET enable_seqscan = off で無理に使わせると次のようになります。

               ->  Parallel Bitmap Heap Scan on orders (actual time=0.286..124.947 rows=952 loops=3)
                     Rows Removed by Index Recheck: 332381
                     Heap Blocks: lossy=12550
                     ->  Bitmap Index Scan on idx_brin_random (actual time=0.564..0.564 rows=269470 loops=1)
 Execution Time: 131.894 ms

ほぼ全ブロック（lossy=12,550 × 3 ワーカー）を読んで 99.7% の行を Recheck で捨てるため、全件走査より遅い 131.9 ms になりました。BRIN を張る前に correlation を確認する理由がここにあります。並び順が崩れたテーブルは CLUSTER や pg_repack で物理順を整えると BRIN が効くようになります。

使われていないインデックスの見つけ方と肥大化の直し方

pg_stat_user_indexes で idx_scan を見る

インデックスは検索を速くする代わりに、INSERT / UPDATE / DELETE のたびに更新コストとディスクを消費します。使われていないものは外すのが基本です。

SELECT indexrelname, idx_scan, idx_tup_read, pg_size_pretty(pg_relation_size(indexrelid)) AS size
  FROM pg_stat_user_indexes WHERE relname = 'orders'
 ORDER BY idx_scan, pg_relation_size(indexrelid) DESC;

      indexrelname       | idx_scan | idx_tup_read |  size
-------------------------+----------+--------------+---------
 idx_orders_email_uq     |        0 |            0 | 39 MB
 idx_orders_amount       |        0 |            0 | 6968 kB
 idx_orders_status       |        0 |            0 | 6792 kB
 idx_brin_created        |        0 |            0 | 24 kB
 idx_btree_created       |        1 |       121601 | 21 MB
 ...

候補は「idx_scan = 0 で、UNIQUE でも主キーでもないもの」です。次のクエリで一覧できます。

SELECT s.indexrelname, 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 s.idx_scan = 0 AND NOT i.indisunique AND NOT i.indisprimary
 ORDER BY pg_relation_size(s.indexrelid) DESC;
--   indexrelname    |  size
--  idx_orders_amount | 6968 kB
--  idx_orders_status | 6792 kB
--  idx_brin_created  | 24 kB

肥大化を pgstatindex で確かめ、REINDEX CONCURRENTLY で直す

大量の UPDATE / DELETE を繰り返した B-tree は、ページの中に空きが増えて実データより大きくなります（肥大化）。標準同梱の pgstattuple 拡張で葉ページの詰まり具合（avg_leaf_density）が見られます。

CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE TABLE items (id integer PRIMARY KEY, stock integer NOT NULL);   -- 備品テーブル100万行
INSERT INTO items SELECT i, (random() * 1000)::int FROM generate_series(1, 1000000) i;
CREATE INDEX idx_items_stock ON items (stock);
VACUUM ANALYZE items;
SELECT pg_size_pretty(pg_relation_size('idx_items_stock')) AS size,
       round(avg_leaf_density::numeric, 1) AS leaf_density, leaf_pages
  FROM pgstatindex('idx_items_stock');                 -- (1) 作成直後
UPDATE items SET stock = stock + 1;                    -- 全行更新 → 新旧の版が両方インデックスに入る
VACUUM ANALYZE items;                                  -- 不要な版は回収されるが、ページは縮まない
-- 同じ SELECT ... FROM pgstatindex(...)                 -- (2) 肥大化後
REINDEX INDEX CONCURRENTLY idx_items_stock;            -- 書き込みを止めずに作り直す（PostgreSQL 12 以降）
-- 同じ SELECT ... FROM pgstatindex(...)                 -- (3) REINDEX 後

  size   | leaf_density | leaf_pages
---------+--------------+------------
 7096 kB |         86.3 |        880     -- (1) 作成直後
 21 MB   |         57.3 |       1846     -- (2) 全行 UPDATE + VACUUM 後（約3倍に肥大化）
 7096 kB |         86.3 |        880     -- (3) REINDEX CONCURRENTLY 後（0.49 秒）

VACUUM は不要な行を回収しますが、インデックスのページ数は戻しません。 avg_leaf_density が 50〜60% 台まで下がったインデックスは REINDEX で元のサイズに戻ります。REINDEX TABLE CONCURRENTLY orders; でテーブルの全インデックスをまとめて作り直せます。なお、PostgreSQL 13 以降の B-tree は同じ値の重複を 1 つにまとめる「重複排除」が効くため、stock のように値の種類が少ない列のインデックスは小さくなります（1,000 種類で 7 MB、100 万種類の created_at では 21 MB）。

よくあるつまずき

実測：LIKE 前方一致は text_pattern_ops で効くようになる

このデータベースのロケールは C.UTF-8 なので、既定の演算子クラスの B-tree（CREATE INDEX ON orders (memo)）では memo LIKE '7a3f9%' が Parallel Seq Scan（70.3 ms）のままでした（= 検索には使われます）。text_pattern_ops を付けると次のとおりです。

CREATE INDEX idx_btree_memo_pattern ON orders USING btree (memo text_pattern_ops);
EXPLAIN (ANALYZE, COSTS OFF) SELECT count(*) FROM orders WHERE memo LIKE '7a3f%';

 Aggregate (actual time=0.022..0.022 rows=1 loops=1)
   ->  Index Only Scan using idx_btree_memo_pattern on orders (actual time=0.014..0.019 rows=13 loops=1)
         Index Cond: ((memo ~>=~ '7a3f'::text) AND (memo ~<~ '7a3g'::text))
         Filter: (memo ~~ '7a3f%'::text)
 Execution Time: 0.035 ms

Index Cond に ~>=~ '7a3f' と ~<~ '7a3g' が現れ、前方一致が「範囲検索」に書き換えられてインデックスで処理されています。ロケールは SELECT datcollate FROM pg_database WHERE datname = current_database(); で確認できます。

よくある質問

Q. GIN と GiST の違いは何ですか？ A. GIN は「値 → 行の一覧」を持つ転置インデックスで、配列・jsonb・全文検索・pg_trgm の部分一致のように「1 行が複数のキーを持つ」データ向けです。検索が速く、更新と作成はやや重めです。GiST は「範囲や領域の重なり・包含・近さ」を扱う汎用の木構造で、範囲型・幾何型・位置情報・排他制約に使います。同じ pg_trgm でも、GIN 版（gin_trgm_ops）は検索重視、GiST 版（gist_trgm_ops）は <-> による類似度順の並べ替えに対応します。

Q. BRIN はどんなテーブルで使うべきですか？ A. 「挿入順と値の順が一致する列」を「範囲で検索する」巨大テーブルです。今回の実測では、B-tree 21 MB に対して BRIN 24 kB で、速度はほぼ同じでした。pg_stats の correlation が 1 か -1 に近いことを確認してから張ってください。並びがバラバラな列では、プランナが使わないか、使っても全件走査より遅くなります。

Q. Hash インデックスを使う場面はありますか？ A. = 検索しかしない列、特に長い文字列（URL やトークンなど）で、B-tree よりインデックスが小さくなる場合に候補になります。今回の整数列では B-tree の方が小さく速かったので、まず B-tree で測ってから判断するのが安全です。PostgreSQL 10 以降は WAL に記録されるため、障害時の復旧やレプリケーションでも B-tree と同じように扱えます。

Q. インデックスを作ったのに使われません。 A. よくある原因は、ヒット行が多すぎてプランナが全件走査を選んでいる、クエリの式や型がインデックスの定義と一致していない（式・部分インデックス・text_pattern_ops）、統計が古い（ANALYZE で更新）、の 3 つです。SET enable_seqscan = off で一時的に強制し、「使えるが選ばれていない」のか「使えない」のかを切り分けると早く原因にたどり着けます。

まとめ

• PostgreSQL のインデックスは B-tree・Hash・GIN・GiST・SP-GiST・BRIN の 6 種類。迷ったら B-tree で、UNIQUE・ソート・範囲検索はこれだけで対応できます。
• GIN は配列・jsonb・全文検索・LIKE '%…%'（pg_trgm）に。 100 万行の部分一致が 169〜192 ms → 1.64 ms になりましたが、サイズ 175 MB・作成 22 秒のコストも見込みます。
• GiST は範囲・幾何・位置情報の「重なり・近さ」に。 tsrange の重なり検索が 139〜142 ms → 1.66 ms。SP-GiST は同じ用途で偏ったデータ向けの選択肢です。
• BRIN は物理順に並んだ列の範囲検索に。 24 kB で B-tree（21 MB）と同等の 0.85 ms。correlation が 0 付近の列には効きません。
• Hash は = 専用。 範囲検索は Seq Scan に戻り、複数列・UNIQUE にも使えません。
• 部分・式・複合・INCLUDE は、クエリの形と定義を一致させて初めて効きます。 本番では CONCURRENTLY で作り、indisvalid を確認します。
• pg_stat_user_indexes の idx_scan = 0 を定期的に見直し、pgstatindex の密度が下がったら REINDEX CONCURRENTLY。 実測では 21 MB → 7 MB に戻りました。

株式会社RJCでは、こうしたインデックス設計や実行計画の読み方を、実際にデータを作って測りながらプロジェクトの中で仲間と学び合っています。こうした知識を共有しながら、日々技術を磨いています。

株式会社RJC ― SI事業・SES事業・AI駆動開発。RJCは一緒に成長を楽しめる会社です。