PostgreSQLのインデックス設計——EXPLAINで遅いクエリを改善する

PostgreSQLのEXPLAIN ANALYZEを使って遅いクエリの原因を特定し、インデックスで改善する実践的な手順を解説します。

はじめに

「アプリのレスポンスが遅い」という問題の原因はよくDBのクエリにあります。PostgreSQLでは EXPLAIN ANALYZE を使うことでクエリの実行計画を確認でき、どこがボトルネックになっているか把握できます。

サンプルテーブル

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE orders (
    id         SERIAL PRIMARY KEY,
    user_id    INTEGER NOT NULL,
    status     VARCHAR(20) NOT NULL,
    amount     NUMERIC(10,2) NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 100万件のテストデータを挿入
INSERT INTO orders (user_id, status, amount, created_at)
SELECT
    (random() * 10000)::int,
    CASE (random() * 3)::int
        WHEN 0 THEN 'pending'
        WHEN 1 THEN 'completed'
        ELSE 'cancelled'
    END,
    (random() * 100000)::numeric(10,2),
    NOW() - (random() * interval '365 days')
FROM generate_series(1, 1000000);

EXPLAIN ANALYZEの読み方

1
2
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 42;

インデックスがない状態での出力例：

Seq Scan on orders  (cost=0.00..24053.00 rows=100 width=48)
                    (actual time=0.028..182.451 rows=98 loops=1)
  Filter: (user_id = 42)
  Rows Removed by Filter: 999902
Planning Time: 0.189 ms
Execution Time: 182.498 ms

Seq Scan（シーケンシャルスキャン）は全行を読んでいる状態です。Rows Removed by Filter: 999902 が示すとおり、100万行をすべてスキャンして98行だけ返しています。

インデックスの追加

1
CREATE INDEX idx_orders_user_id ON orders(user_id);

再度 EXPLAIN ANALYZE を実行：

Index Scan using idx_orders_user_id on orders
  (cost=0.42..432.43 rows=100 width=48)
  (actual time=0.042..0.681 rows=98 loops=1)
  Index Cond: (user_id = 42)
Planning Time: 0.312 ms
Execution Time: 0.714 ms

182ms → 0.7ms に改善されました。Index Scan に変わったことで、対象行だけを効率よく読めています。

複合インデックス

複数列を組み合わせた検索が多い場合は複合インデックスが有効です。

1
2
3
4
5
-- user_id で絞り込み、status でさらに絞るクエリ
SELECT * FROM orders WHERE user_id = 42 AND status = 'completed';

-- 複合インデックス（絞り込み条件の選択性が高い列を先に）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

複合インデックスは左端の列から順番にしか使えない点に注意します。user_id と status の複合インデックスは WHERE user_id = 42 にも効きますが、WHERE status = 'completed' だけには効きません。

部分インデックス（Partial Index）

特定の条件のレコードだけを対象にしたインデックスです。

1
2
3
-- 未処理のオーダーだけを扱うクエリが多い場合
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';

status = 'pending' の件数が全体の一部であれば、通常インデックスよりサイズが小さく、更新時のオーバーヘッドも少なくなります。

インデックスが使われないケース

インデックスを作っても使われないことがあります。代表的なケースを押さえておきます。

関数をWHEREに使っている

1
2
3
4
5
6
7
-- NG: インデックスが使われない
SELECT * FROM orders WHERE DATE(created_at) = '2026-01-01';

-- OK: 範囲検索に書き直す
SELECT * FROM orders
WHERE created_at >= '2026-01-01'
  AND created_at <  '2026-01-02';

NULLとの比較

IS NULL や IS NOT NULL に対しては通常インデックスは効きません（部分インデックスで対応可能）。

テーブルが小さい

数千件以下のテーブルではプランナーがSeq Scanの方が速いと判断することがあります。

インデックスの管理

不要なインデックスは更新・挿入時のオーバーヘッドになるため、定期的に使われていないものを確認して削除します。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
-- 使われていないインデックスを確認
SELECT relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY relname;

-- インデックスのサイズ確認
SELECT indexname, pg_size_pretty(pg_relation_size(indexrelid))
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY pg_relation_size(indexrelid) DESC;

まとめ

種類	用途
単一インデックス	1列での絞り込みが多い場合
複合インデックス	複数列の組み合わせ検索
部分インデックス	特定条件のレコードのみが対象

EXPLAIN ANALYZE でSeq Scanが出たら、まずインデックスが存在するか確認してください。存在しても使われていない場合はクエリの書き方に問題があることが多いです。インデックスは銀の弾丸ではなく、多すぎると書き込みが遅くなるため、使われているものだけを残すことが重要です。