PostgreSQLのインデックス入門（B-tree と EXPLAIN ANALYZE の最小例）

PostgreSQL で検索が遅いとき、最初に見たいのは「どの方法で行を探しているか」です。この記事では users テーブルへ 20 万行を入れ、B-tree インデックスを作る前後で同じ検索を EXPLAIN ANALYZE し、Seq Scan と Index Scan の違い、さらにインデックスを作っても速くならない例まで最小構成で確認できます。EXPLAIN ANALYZE は、SQL を実行したときに PostgreSQL がどの順序で処理したかという実行計画と、実際にかかった時間を見るためのコマンドです。

前提環境

• Windows 11
• WSL2（Ubuntu）
• VS Code（Remote - WSL）
• Docker Desktop
• PostgreSQL 17
• psql

以降のコマンドは、特記がない限り WSL 側ターミナルで実行します。

1. ゴールと非対象

到達する状態:

• B-tree インデックスを作る前後で、同じ SQL の探し方がどう変わるかを確認できる
• EXPLAIN ANALYZE で、まず Seq Scan と Index Scan を見分けられる
• インデックスを作っても常に速くなるわけではないと理解できる

扱わない内容:

• 複合インデックス
• 部分インデックス
• GIN / GiST / BRIN の比較
• sort / join / aggregate の実行計画読解
• VACUUM や統計情報の詳細チューニング

今回は「B-tree が効く最小例」と「効かない最小例」に絞ります。

2. 最小デモ環境を作る

記事用の空ディレクトリを作ります。

mkdir -p ~/projects/postgresql-index-btree-explain-analyze-demo
cd ~/projects/postgresql-index-btree-explain-analyze-demo
mkdir -p sql
code .

最終的な構成は次の 4 ファイルです。

postgresql-index-btree-explain-analyze-demo/
├─ compose.yml
├─ .env.example
└─ sql/
   ├─ 01-schema.sql
   └─ 02-seed.sql

compose.yml を作成し、次の内容を保存します。

services:
  db:
    image: postgres:17
    working_dir: /workspace
    environment:
      POSTGRES_DB: ${POSTGRES_DB}
      POSTGRES_USER: ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - db-data:/var/lib/postgresql/data
      - ./:/workspace
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 5s
      timeout: 3s
      retries: 20

volumes:
  db-data:

.env.example を作成し、次の内容を保存します。

POSTGRES_DB=app
POSTGRES_USER=app
POSTGRES_PASSWORD=app

起動は次のコマンドです。

cp .env.example .env
docker compose up -d
docker compose exec db pg_isready -U app -d app
docker compose exec db psql -U app -d app -c "SELECT version();"

pg_isready が accepting connections を返せば準備完了です。

詰まったときは次を確認してください。

docker compose logs db
docker compose down -v

3. テーブルと初期データ（seed）を用意し、比較の前提をそろえる

今回は users テーブル 1 本だけです。email は 1 件を狙いやすい列、status は同じ値が大量に並ぶ列として使います。ここでいう B-tree は PostgreSQL で標準的に使われるインデックス方式です。Seq Scan は表を先頭から順に読む方法、Index Scan はインデックスから候補を絞って必要な行を取りにいく方法を指します。

先に、探し方の違いだけ図で置いておきます。

flowchart LR
    A["WHERE email = ..."] --> B{"探し方"}
    B -->|インデックスなし| C["Seq Scan<br>表を先頭から順に読む"]
    B -->|B-tree あり| D["Index Scan<br>インデックスから候補を絞る"]

sql/01-schema.sql を作成し、次の内容を保存します。

DROP TABLE IF EXISTS users;

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('active', 'inactive')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

sql/02-seed.sql を作成し、次の内容を保存します。

TRUNCATE TABLE users RESTART IDENTITY;

INSERT INTO users (email, status, created_at)
SELECT
  'user' || lpad(gs::text, 6, '0') || '@example.com',
  CASE WHEN gs % 20 = 0 THEN 'inactive' ELSE 'active' END,
  NOW() - (gs || ' seconds')::interval
FROM generate_series(1, 200000) AS gs;

ANALYZE users;

generate_series(1, 200000) は 1 から 20 万までの連番を作る関数で、lpad(..., 6, '0') は番号を 6 桁の文字列にそろえるために使っています。

スキーマと初期データを適用します。

docker compose exec db psql -U app -d app -f sql/01-schema.sql
docker compose exec db psql -U app -d app -f sql/02-seed.sql
docker compose exec db psql -U app -d app -c "SELECT COUNT(*) FROM users;"
docker compose exec db psql -U app -d app -c "SELECT status, COUNT(*) FROM users GROUP BY status ORDER BY status;"

期待する件数は次のとおりです。

status	count
active	190000
inactive	10000

ここで ANALYZE users; を入れているのは、PostgreSQL にデータ分布、つまりどの値がどれくらい入っているかを知らせるためです。この情報が統計情報になり、どの探し方を選ぶかの判断材料になります。今回のように EXPLAIN ANALYZE を見比べる記事では、統計情報を更新してから確認したほうが意図どおりの比較になりやすくなります。

4. インデックスなしで Seq Scan を見る

最初に、B-tree インデックスがない状態で email の 1 件検索を実行します。EXPLAIN ANALYZE の出力は実行計画と呼ばれ、SQL をどの順序でどう実行したかが木構造で並びます。Seq Scan や Index Scan のような各行は、処理単位としてのノードです。

docker compose exec db psql -U app -d app -c "EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user199999@example.com';"

実行計画の例は次のとおりです。

Seq Scan on users  (cost=0.00..4370.00 rows=1 width=46) (actual time=13.725..13.727 rows=1 loops=1)
  Filter: (email = 'user199999@example.com'::text)
  Rows Removed by Filter: 199999
Planning Time: 0.196 ms
Execution Time: 13.744 ms

ここで見たいのは 3 点です。

• 先頭のノード名が Seq Scan になっている
• Rows Removed by Filter: 199999 となっていて、ほぼ全行を見たあとに 1 行だけ残している
• 実行時間そのものより、「表を先頭から順に読んでいる」ことが分かる

Seq Scan は悪ではありません。小さいテーブルや、多くの行を返す検索では妥当なこともあります。ただ、今回の email のように 1 行だけを狙う検索では、全件を順に確認する形はもったいない状態です。

5. B-tree インデックスを作り、Index Scan へ変わることを確認する

次に email 列へ B-tree インデックスを作ります。CREATE INDEX でインデックス方式を省略した場合、PostgreSQL では B-tree になります。

docker compose exec db psql -U app -d app -c "CREATE INDEX idx_users_email ON users (email);"
docker compose exec db psql -U app -d app -c "ANALYZE users;"
docker compose exec db psql -U app -d app -c "EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'user199999@example.com';"

今度の計画は次のとおりです。

Index Scan using idx_users_email on users  (cost=0.42..8.44 rows=1 width=46) (actual time=0.039..0.040 rows=1 loops=1)
  Index Cond: (email = 'user199999@example.com'::text)
Planning Time: 0.162 ms
Execution Time: 0.052 ms

変化の要点は次のとおりです。

• ノード名が Seq Scan から Index Scan に変わった
• Filter ではなく Index Cond が出ている。Index Cond はインデックスを使って候補を絞る条件で、Filter は読んだあとに条件で落とす表示です
• 全件を見てから落とすのではなく、インデックスを使って候補を絞っている
• 実行時間の桁も大きく下がりやすい

今回の検証では、約 13.7 ms だった検索が約 0.05 ms まで下がりました。数値は環境で変わりますが、「ノードが変わったか」と「何行を見に行っているか」は別環境でも再確認しやすいポイントです。

6. EXPLAIN ANALYZE はこの 4 点だけ先に見る

実行計画は情報量が多いので、最初は次の 4 点だけで十分です。

見る項目	何を見るか	今回の例
ノード名	どの方法で行を探しているか	Seq Scan / Index Scan
Index Cond / Filter	どこで条件を使っているか	Index Cond: (email = ...)
rows	何行返る見込みか、実際に何行返ったか	rows=1
actual time / loops	どのノードで時間がかかっているか	actual time=... loops=1

Seq Scan と Index Scan の違いをざっくりまとめると、次の表になります。

観点	Seq Scan	Index Scan
探し方	表を先頭から順に読む	インデックスから候補を絞る
目印	Filter と Rows Removed by Filter が大きい	Index Cond が出やすい
向く場面	小さい表、多くの行を返す検索	返す行が少ない検索

最初は「なぜそのコストなのか」まで掘らなくて構いません。ノード名、条件の使われ方、返る行数、時間のかかり方の 4 点が確認できれば十分です。

補足すると、cost は PostgreSQL の内部見積もりで、ミリ秒そのものではありません。最初は actual time とノード名を優先して見れば十分です。

7. インデックスを作っても速くならない例を見る

次は、インデックスがあっても Seq Scan のままになる例です。status 列には active が 19 万行、inactive が 1 万行入っています。

docker compose exec db psql -U app -d app -c "CREATE INDEX idx_users_status ON users (status);"
docker compose exec db psql -U app -d app -c "ANALYZE users;"
docker compose exec db psql -U app -d app -c "EXPLAIN ANALYZE SELECT * FROM users WHERE status = 'active';"

実行計画の例は次のとおりです。

Seq Scan on users  (cost=0.00..4370.00 rows=190040 width=46) (actual time=0.007..14.148 rows=190000 loops=1)
  Filter: (status = 'active'::text)
  Rows Removed by Filter: 10000
Planning Time: 0.125 ms
Execution Time: 18.273 ms

idx_users_status は存在していても、PostgreSQL は Seq Scan を選びました。理由は単純で、19 万行を返すならインデックスをたどって大量の行へ飛ぶより、表を順に読んだほうが得だと判断しやすいからです。

ここで押さえたいのは次の 2 点です。

• インデックスが「使われなかった」こと自体は異常ではない
• 大量の行を返す検索では、Seq Scan のほうが自然なこともある

「インデックスを作ったのに遅い」という相談では、まずその検索が本当に少数行を返す条件なのかを確認する必要があります。

8. まとめ

今回の最小例で押さえたいのは次の 3 点です。

• B-tree インデックスは、email = ... のように少数行を狙う検索で効きやすい
• EXPLAIN ANALYZE では、まず Seq Scan か Index Scan かを見る
• インデックスを作っても、返す行が多い検索では Seq Scan が選ばれることがある

次に進むなら、sort / join / aggregate を含む SQL を同じ読み方で追うと、EXPLAIN ANALYZE の使いどころが広がります。更新系の並行実行やロックを見たい場合は、PostgreSQLのトランザクション入門（BEGIN / COMMIT / ROLLBACK と同時更新） や PHP + PostgreSQLで在庫引当を安全に実装する（SELECT … FOR UPDATE 最小構成） も続けて読むとつながりやすくなります。