PostgreSQLインデックス最適化の現場手順：遅いクエリを再現・診断・改善する実践プレイブック

「CPUは余っているのに画面が遅い」「特定時間帯だけ API が詰まる」。この手の問題の多くは、アプリではなく SQL の実行計画に原因があります。特に PostgreSQL では、インデックス設計と統計情報の状態が性能をほぼ決めます。

本記事では、実務で使う手順に沿って、遅延クエリの改善を再現可能な形で解説します。単なる理論紹介ではなく、調査順序、判断基準、リリース時の注意点まで含めてまとめます。

まず守るべき3原則

推測でインデックスを作らない 体感で追加すると write 性能とストレージが悪化します。必ず実行計画を見てから判断します。
改善前後を数値で比較する P95、rows、shared read blocks を記録し、効果を証明します。
本番反映は CONCURRENTLY を基本にする テーブルロックで事故らないため、CREATE INDEX CONCURRENTLY を優先します。

ケース設定：注文一覧APIが遅い

次のクエリが遅いとします。

1
2
3
4
5
6
7


SELECT id, user_id, status, total_amount, created_at
FROM orders
WHERE tenant_id = $1
  AND status IN ('paid', 'shipped')
  AND created_at >= NOW() - INTERVAL '30 days'
ORDER BY created_at DESC
LIMIT 50;

データ量は orders 1.2億件、1テナントあたり数百万件。現象は「特定テナントだけ 3〜6 秒」です。

手順1：pg_stat_statementsで優先度をつける

まずは遅い順ではなく、**影響度順（総時間）**で見るのが現場では正解です。

1
2
3
4


SELECT queryid, calls, total_exec_time, mean_exec_time, rows, query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

ここで対象クエリの calls が多く、mean_exec_time が高いことを確認。改善効果が大きいと判断できます。

手順2：EXPLAIN ANALYZE BUFFERSでボトルネックを特定

1
2


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT ...;

典型的な悪い例は次の通りです。

Seq Scan on orders
Rows Removed by Filter が極端に多い
Sort Method: external merge Disk（メモリ不足でディスクソート）

この状態では、絞り込み条件に合うインデックスが不足しています。

手順3：最小コストで効くインデックス設計

今回の条件は tenant_id, status, created_at です。ORDER BY も created_at DESC。したがって候補は次です。

1
2


CREATE INDEX CONCURRENTLY idx_orders_tenant_status_created_at_desc
ON orders (tenant_id, status, created_at DESC);

ここで順序が重要です。先頭列は等価条件（tenant_id）、次に低カーディナリティ条件（status）、最後に範囲・並び替え列（created_at）を置きます。

部分インデックスの検討

status が多数あるが実際に使うのが paid/shipped だけなら、部分インデックスでさらに削減できます。

1
2
3


CREATE INDEX CONCURRENTLY idx_orders_recent_paid_shipped
ON orders (tenant_id, created_at DESC)
WHERE status IN ('paid', 'shipped');

この方式はサイズが小さく、キャッシュ効率が高いのが利点です。

手順4：改善効果を検証

同一条件で再度 EXPLAIN ANALYZE を実施します。

確認ポイント:

Index Scan か Bitmap Heap Scan に変わっているか
実行時間が目標値（例: 200ms 未満）に入ったか
shared read blocks が大幅に減ったか
rows=50 を早期に取り出せているか

改善後に 4.2 秒 → 120ms 程度まで落ちるケースは珍しくありません。

それでも遅い場合の追加施策

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

取得列が多いとテーブルアクセスが残ります。PostgreSQL では INCLUDE が使えます。

1
2
3


CREATE INDEX CONCURRENTLY idx_orders_covering
ON orders (tenant_id, status, created_at DESC)
INCLUDE (total_amount, user_id);

2) 統計情報の更新

データ偏りが強いと planner が誤判定します。

1
2
3


ANALYZE orders;
ALTER TABLE orders ALTER COLUMN status SET STATISTICS 1000;
ANALYZE orders;

3) パーティショニング

30日検索が多いなら、月次パーティションで読み取り範囲を削るのも有効です。既存移行はコストが高いので、まずは新規データから段階導入します。

リリース時の安全手順

本番では速度改善より安全性が優先です。次の順番を守ると事故が減ります。

負荷が低い時間帯を選ぶ
CREATE INDEX CONCURRENTLY を実行
進捗確認: pg_stat_progress_create_index
完了後に代表クエリで実行計画を確認
監視（CPU、I/O、lock wait、replication lag）を 30 分観察
不要化した旧インデックスは別日に削除

いきなり削除しない理由は、想定外クエリで回帰する可能性があるためです。1〜2日観測してから DROP INDEX CONCURRENTLY するのが安定運用です。

アンチパターン集

LIKE '%keyword%' に B-tree インデックスを貼る
- → pg_trgm + GIN を使う
すべての列に単体インデックスを作る
- → planner が迷う、write コスト増
UUID主キーだけ見て満足する
- → 実際の検索条件列を優先
autovacuum 設定を放置
- → bloat 増で index scan が遅くなる

計測テンプレート（運用向け）

改善作業を属人化しないために、次のテンプレートで記録すると再利用できます。

対象クエリID（pg_stat_statements）
改善前 mean/P95
改善前実行計画（テキスト保存）
追加したインデックスDDL
改善後 mean/P95
副作用（write増、ストレージ増、vacuum時間）
ロールバック手順

このフォーマットをWiki化しておくと、次回の性能障害対応が非常に速くなります。

まとめ

PostgreSQL の性能改善は、魔法のパラメータよりも「再現・診断・検証」の手順で決まります。特にインデックスは効果が大きい反面、副作用もあるため、実行計画と計測値で判断することが重要です。

遅延問題に直面したら、まず pg_stat_statements で対象を絞り、EXPLAIN ANALYZE BUFFERS で事実を取り、CONCURRENTLY で安全に改善する。この流れをチーム標準にすれば、DB運用の安定性は確実に上がります。

現場で使うトラブルシュート手順（夜間障害対応向け）

実際の障害対応では、理想的な調査順序を守れないことがあります。そこで夜間当番でも使える短縮手順を用意しておくと有効です。

まず pg_stat_activity で待機イベントを確認（lock か I/O か）
次に pg_locks で競合トランザクションを特定
対象クエリの EXPLAIN (ANALYZE, BUFFERS) を取得
直近デプロイ差分（SQL/マイグレーション）を確認
即効性のある一時回避（statement timeout、read replica 振り分け）を実施

短期回避後に恒久対策を行う、という二段運用が安定します。

ロック競合の例

ALTER TABLE と長時間 SELECT が競合すると、アプリの体感遅延が一気に悪化します。マイグレーションは LOCK TIMEOUT を短く設定し、失敗時に即リトライしない設計にしましょう。

1
2


SET lock_timeout = '2s';
SET statement_timeout = '30s';

クエリヒントが使えない前提での工夫

PostgreSQL は MySQL のようなヒント句が限定的なため、実行計画の誘導は以下で行います。

統計情報を正しく更新
不要な関数適用を避ける（索引利用阻害）
OR 条件を UNION ALL 分割で単純化

例えば WHERE date(created_at) = CURRENT_DATE は index を使いにくいため、次のように書き換えます。

1
2


WHERE created_at >= CURRENT_DATE
  AND created_at < CURRENT_DATE + INTERVAL '1 day'

この1点だけで scan 範囲が激減することがあります。

チーム運用に落とし込むためのルール

最後に、性能改善を個人依存にしないための運用ルールを提案します。

新規API追加時は必ず「想定SQL」と「必要インデックス案」を設計レビューに含める
週次で slow query 上位10件を確認し、改善オーナーを割り当てる
重要テーブルの index hit ratio と bloat 率を定期監視する

この運用が回ると、障害対応だけでなく機能開発の速度も上がります。DB 性能は裏方ではなく、プロダクト体験の中心です。

PostgreSQLインデックス最適化の現場手順：遅いクエリを再現・診断・改善する実践プレイブック#

まず守るべき3原則#

ケース設定：注文一覧APIが遅い#

手順1：pg_stat_statementsで優先度をつける#

手順2：EXPLAIN ANALYZE BUFFERSでボトルネックを特定#

手順3：最小コストで効くインデックス設計#

部分インデックスの検討#

手順4：改善効果を検証#

それでも遅い場合の追加施策#

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

2) 統計情報の更新#

3) パーティショニング#

リリース時の安全手順#

アンチパターン集#

計測テンプレート（運用向け）#

まとめ#

現場で使うトラブルシュート手順（夜間障害対応向け）#

ロック競合の例#

クエリヒントが使えない前提での工夫#

チーム運用に落とし込むためのルール#