PostgreSQL インデックスとクエリ最適化：実践ガイド

スロークエリが決済 API を落としたインシデント

昨年の 11.11 セール前夜、決済 API の P99 が 80ms から 2.3s に跳ねた。CPU は張り付き、コネクションプールは枯渇した。最初はアプリケーションコードのせいだと思い、ビジネスロジックを延々と読んだ——しかし EXPLAIN ANALYZE を叩くと、注文を user_id + created_at の範囲で絞る SQL が シーケンシャルスキャン（Seq Scan） で 800 万行を舐めていた。

複合インデックスを一つ貼ると、P99 は 60ms に戻った。あれ以来、私の鉄則はこれだ：スロークエリには、まず「インデックスを使っているか」を問え。それから他を語れ。

PostgreSQL のインデックスを深掘りする。焦点は「いつどれを、なぜ」だ。

1. PostgreSQL のインデックス一覧

インデックスは全部同じ形ではない。PostgreSQL にはいくつかのアクセスメソッドが組み込まれている。

種類	用途	内部構造
B-tree	等価、範囲、ソート、プレフィックス	平衡木（既定）
GIN	多値／転置：配列、`jsonb`、全文検索	転置インデックス
GiST	幾何、範囲、類似度、全文検索	汎用探索木
BRIN	物理的に順序保存される大規模表（時系列等）	ブロック範囲要約
Hash	純等価（ほぼ B-tree に取代わられた）	ハッシュバケツ

多くの業務ワークロードは B-tree で足りる。B-tree を深く見たあと、見落とされがちな GIN と部分／式インデックスを補足する。

2. B-tree の動き

B-tree はインデックス列を平衡木にソートして持つ。値を探すときは木を降りて葉に達する。計算量は O(log n)。以下を支援する：

等価：WHERE status = 'paid'
範囲：WHERE created_at > '2026-01-01'
ソート：ORDER BY id DESC
プレフィックス LIKE：WHERE email LIKE 'ada%'（後方の '%ada' は使えない）

複合インデックスと最左プレフィックス

複合インデックス (a, b, c) は a、次に b、次に c の順にソートされる。だからクエリが最左プレフィックスに触れたときだけ効く：

-- (user_id, created_at) を使える
WHERE user_id = 5 AND created_at > '2026-01-01';
WHERE user_id = 5;
WHERE user_id = 5 AND created_at = '2026-01-01';

-- 使えない（user_id を飛ばしている）
WHERE created_at > '2026-01-01';

だからあのインシデントでは、created_at 単体のインデックスは無意味だった——クエリはまず user_id で絞っていたのだ。

3. EXPLAIN ANALYZE を読む

インデックスを貼るだけでは足りない。計画を読まねばならない。EXPLAIN は見積もり、EXPLAIN ANALYZE は実行する（本当に走る。書き込みには EXPLAIN (ANALYZE, BUFFERS) を慎重に）。

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE user_id = 5 AND created_at > '2026-01-01'
ORDER BY created_at DESC
LIMIT 20;

三つのポイント：

Seq Scan vs Index Scan：前者は全表走査で、遅延の一番の容疑者。
cost と actual time：(cost=0.00..123.45 rows=10) のなかの actual time=0.02..1.20 はミリ秒。実時間を見て、見積もりに騙されない。
Buffers：Buffers: shared hit=3 read=120 は 120 ブロックをディスクから読み、キャッシュは 3 だけ。ヒット率低いのはデータがメモリにない証拠——インデックスがカバーしていないか shared_buffers が小さい。

典型的な悪い計画：

Seq Scan on orders  (cost=0.00..182345.00 rows=812 width=64)
  Filter: ((user_id = 5) AND (created_at > '2026-01-01'::date))
  Rows Removed by Filter: 7999999

Rows Removed by Filter が全行数に近いのは、一行ずつ絞っている証。インデックス不足のサインだ。

4. 複合インデックス設計の三原則

等価列を前に、範囲列を後に。 B-tree は最初の範囲条件を越えると順序性を使えなくなる。(user_id, created_at) が正しい順（user_id の等価を前）だ。
選択性の高い列を前に（等価条件のなかで）。カーディナリティが高いほど早く絞れる。
ORDER BY の列をインデックスに含め、余計な Sort ノードを避ける。WHERE user_id=5 ORDER BY created_at DESC に (user_id, created_at) を使えば、順序通りに読める——Sort 不要。

カバリングインデックス（INCLUDE）

クエリがインデックス内の列だけで済むなら、PostgreSQL は表に触らない——「Index Only Scan」。絞りには使わないが SELECT には要る列は INCLUDE で付ける：

CREATE INDEX idx_orders_user_time
  ON orders (user_id, created_at)
  INCLUDE (status, total);

こうすれば SELECT status, total FROM orders WHERE user_id=5 AND created_at > ... は純インデックスで返り、ヒープ参照が不要になる。

5. 部分インデックスと式インデックス

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

「未払い」の注文だけを頻繁に引くなら、全表にインデックスを貼るのは無駄。気になる行だけに貼る：

CREATE INDEX idx_orders_pending
  ON orders (created_at)
  WHERE status = 'pending';

体積も維持コストも小さく、WHERE status = 'pending' に的確に効く。

式インデックス（Expression Index）

クエリが列を関数で包むと、ふつうのインデックスは効かない。大文字小文字を無視したメール検索の例：

CREATE INDEX idx_users_lower_email ON users (lower(email));

-- この書き方だけインデックスに乗る
SELECT * FROM users WHERE lower(email) = 'ada@example.com';

別の例：「月」ごとの集計で date_trunc('month', created_at) をよく書くなら、CREATE INDEX ... ON orders (date_trunc('month', created_at)) を貼る。インデックスの式とクエリの式は一字違わず一致しないと使われない。

6. GIN：配列、jsonb、全文検索

一つの値が複数の要素に対応するとき、GIN が本命だ。

jsonb 内のフィールド

CREATE INDEX idx_events_payload ON events USING gin (payload jsonb_path_ops);

SELECT * FROM events WHERE payload @> '{"type": "click"}';

@> は jsonb 包含演算子で、GIN が効率的に扱う。

配列の包含

CREATE INDEX idx_posts_tags ON posts USING gin (tags);

SELECT * FROM posts WHERE tags @> ARRAY['go','postgres'];

全文検索

CREATE INDEX idx_articles_body ON articles USING gin (to_tsvector('zhcfg', body));

SELECT * FROM articles
WHERE to_tsvector('zhcfg', body) @@ to_tsquery('zhcfg', 'データベース 索引');

to_tsvector('zhcfg', ...) の設定名はインデックスと完全一致させないと乗らない点に注意。

7. 統計情報と autovacuum：インデックスが「突然効かなくなる」理由

もう一つの穴：半年間快調だったクエリがある日突然遅くなり、計画が Index Scan から Seq Scan に退行した。SQL は変わっていない——統計情報が古くなっていたのだ。

プランナは pg_statistic のデータ分布から行数を見積もる。大量書き込み後に ANALYZE が timely に走らないと見積もりが狂い、プランナは計算を誤ってインデックスを捨てる。

autovacuum は ANALYZE も兼ねるが、一括ロード後は追いつかないことがある。
手動実行：ANALYZE orders; または VACUUM ANALYZE orders;
確認：SELECT * FROM pg_stats WHERE tablename = 'orders';

教訓：一括 ETL／削除のあとは必ず明示的に ANALYZE を。また、列の値が極端に偏っていると（例：99% が status='done'）、プランナは 'done' の検索はほぼ全表だと知ってインデックスを避ける——その場合、部分インデックス（非 done の 1% だけを索引）が本当の解だ。

8. スロークエリ最適化チェックリスト

私の順番：

EXPLAIN ANALYZE —— Seq Scan か？
Rows Removed by Filter を見る。見積もりが実行数と大きく乖離していないか？
WHERE / ORDER BY / JOIN の列にインデックスがあるか、複合順序は正しいか。
関数で包まれた列？lower()、date_trunc() 等には式インデックスが必要。
最左プレフィックスに触れているか。
統計は新鮮か（ANALYZE）。
列の選択性が低すぎないか？部分インデックスを検討。
カバリングインデックスでヒープ参照を避けられないか。

9. 実践：注文クエリの最適化

元の SQL（遅い、2.1s）：

SELECT id, status, total, created_at
FROM orders
WHERE user_id = 42
  AND status IN ('paid', 'shipped')
  AND created_at >= '2026-01-01'
ORDER BY created_at DESC
LIMIT 50;

800 万行。計画は Seq Scan だった。

ステップ 1：等価列 user_id と status を前に、範囲 created_at を後に置く複合インデックスを貼る。status は IN（複数値）だが中間に置いても、B-tree は user_id の順序プレフィックスを使える：

CREATE INDEX idx_orders_user_status_time
  ON orders (user_id, status, created_at)
  INCLUDE (total);

ステップ 2：ANALYZE orders; で統計を更新。

ステップ 3：計画を再確認——Index Scan になり、Buffers: shared hit が小さく、actual time が 1ms 台になっていることを確認。

なぜ IN を中間に置けるか：(user_id, status, created_at) では user_id=42 がそのユーザーに絞り、status IN (...) がその範囲内でさらに絞りつつ created_at の順序を保つ。だから ORDER BY created_at DESC LIMIT 50 はインデックスを逆順に 50 行読むだけで済む——ソートも全走査も不要。この一本のインデックスで 2.1s が一桁ミリ秒になった。

よくある質問

Q1：インデックスは多いほど良い？

その逆。インデックスは書き込みを遅くし（INSERT/UPDATE/DELETE はすべて保守する）、ディスクを食い、プランナを惑わす。書き込みの激しい表で 5〜6 本を超えたら警戒。各インデックスは「どの実クエリを速くするか」に答えねばならない。

Q2：インデックスを貼ったのに使われない？

最多：関数で包まれた列、最左プレフィックス未到達、統計の陳腐化、選択性の低さ、あるいはクエリが表の大半を返す（そのときは Seq Scan のほうが安い）。EXPLAIN ANALYZE で確認し、勘に頼るな。

Q3：LIKE はインデックスに乗る？

'abc%' の先頭ワイルドカードは B-tree に乗る（順序があるから）。'%abc' の後方ワイルドカードは不可。後方／包含マッチには pg_trgm の GIN trigram インデックスか全文検索を。

Q4：UNIQUE 制約は自動でインデックスを作る？

作る。PRIMARY KEY も UNIQUE も自動で B-tree インデックスを作る。手動で重ねる必要はない。

Q5：表のインデックスとそのサイズはどう見る？

SELECT indexname, pg_size_pretty(pg_relation_size(indexname::regclass))
FROM pg_indexes
WHERE tablename = 'orders';