
PostgreSQLのインデックスで検索8秒→0.07秒にした実測記録
スローログを見て血の気が引いた話
先月、本番のスローログを眺めていたら、ある検索クエリが平均8秒かかっていることに気づいた。
8秒。
ユーザーが検索ボタンを押してから、コーヒーを一口飲んで、また画面を見る間も待ち続けているような数字だ。
問題のAPIは注文履歴の検索エンドポイントで、日付やステータスを組み合わせて絞り込む機能だった。
データ件数は当時で約300万件。
開発初期は数千件だったため誰も気にしていなかったが、サービスが成長するにつれてじわじわ遅くなり、ある日突然サポートに「検索が遅すぎて使えない」と報告が来た。
インデックスを正しく設計し直したら、同じクエリが0.07秒で返ってきた。
約114倍の改善。
体感でも「一瞬で返ってくる」レベルに変わった。
ただし途中で盛大に失敗もした。
インデックスを増やしすぎて書き込みが2倍以上遅くなり、CPUが膨らんで別のアラートが鳴った。
この記事では、成功の手順だけでなく、そのあたりの失敗も正直に書く。
インデックスが「あるのに効かない」状態とは
最初に確認したのは、インデックスの有無ではなく「使われているか」だった。
user_id にインデックスは貼ってあった。
でもクエリは遅かった。
PostgreSQLには EXPLAIN ANALYZE というコマンドがある。
実行計画を表示することで、どのインデックスが使われているか、Seq Scan(全件スキャン)になっていないかを実際の数字で確認できる。
EXPLAIN ANALYZE
SELECT *
FROM orders
WHERE user_id = 12345
AND status = 'shipped'
AND created_at BETWEEN '2025-01-01' AND '2025-03-31'
ORDER BY created_at DESC
LIMIT 20;実行してみると、出力の中に Seq Scan on orders という行があった。
300万件を全件スキャンしている。
インデックスは user_id 単体に貼ってあったのに、複合条件のせいで実質的に活用されていなかった。
原因は2つあった。
1つは、status と created_at を組み合わせた絞り込みをインデックス設計から無視していたこと。
もう1つは、ORDER BY created_at のソートにインデックスが対応していなかったため、ソート処理でも全件を読んでいたことだ。
単純に「インデックスがある=速い」ではない。
クエリのパターンに合わせて設計しないと、あってもなくても同じになる。
この事実に気づいてなかったのが、問題が長期間放置された原因だった。
EXPLAIN ANALYZEの読み方
EXPLAIN ANALYZE の出力は最初は暗号のように見えるが、見るべきポイントは3つに絞られる。
まず Seq Scan という文字列を探す。
これが出ていれば全件スキャンが起きている証拠で、最優先で潰す対象になる。
次に rows= の数字を確認する。
実際に処理した行数が表示される。
期待する絞り込み後の件数と桁が大きく違う場合は、インデックスが効いていないか、テーブル統計が古い可能性がある。ANALYZE コマンドで統計情報を更新するだけで改善するケースも稀にある。
最後に actual time= の数字を見る。
各ノードの実際の処理時間が出るので、どのステップがボトルネックかがわかる。
当時の出力はこんな感じだった。
Seq Scan on orders (cost=0.00..85432.12 rows=15438 width=256)
(actual time=0.042..7923.871 rows=12 loops=1)
Filter: ((user_id = 12345) AND (status = 'shipped') AND ...)
Rows Removed by Filter: 2999988300万件を読んで12件しか残っていない。
99.9%以上を捨てているのに、全部読んでいる。
この状態で速いわけがなかった。
複合インデックスの設計と0.07秒まで落とした手順
問題が明確になったので、複合インデックスを設計した。
ポイントは「WHERE句の条件」と「ORDER BYの列」を同時に考えること。
この2つをセットで扱わないと、ソートで全件読む問題が残る。
最初に試したのがこれだ。
CREATE INDEX idx_orders_user_status_created
ON orders (user_id, status, created_at DESC);列の順序に意味がある。
PostgreSQLはインデックスを左から順番に使う。
user_id で大きく絞り込み、status でさらに絞り込み、created_at の順番でそのまま並べる。
この順番でないと効果が半減する。
特に等値条件(=)の列を前に、範囲条件(BETWEEN, >, <)の列を後に置くのが基本だ。
インデックス作成後、同じクエリを EXPLAIN ANALYZE で確認した。
Index Scan using idx_orders_user_status_created on orders
(cost=0.56..42.31 rows=12 width=256)
(actual time=0.035..0.071 rows=12 loops=1)
Index Cond: ((user_id = 12345) AND (status = 'shipped') AND ...)Seq Scan が消えて Index Scan に変わった。
処理時間は0.071秒。
8秒から0.071秒。
ここで1時間溶かした落とし穴を書いておく。created_at DESC でソートするとき、NULLがどこに来るかの扱いだ。
PostgreSQLは DESC の場合デフォルトで NULLS FIRST(NULLを先頭に持ってくる)になる。
アプリ側の期待と一致していないと、ソート順がおかしいバグとして表れる。
明示的に NULLS LAST を指定するか、アプリ側で揃えるかを最初に決めておくべきだった。
インデックスを増やしすぎて書き込みが2倍遅くなった失敗
問題が解決したあと、調子に乗って他のスローログのクエリにも同様の複合インデックスを貼っていった。
検索の遅い箇所を見つけては複合インデックスを追加する、という作業を繰り返した。
5本追加したあたりで、まずいことが起きた。
数日後、EC2のCPU使用率が普段の3倍に膨らんでいた。
原因を追いかけると、そのテーブルへのINSERT/UPDATEの処理時間が倍以上に伸びていた。
注文が入るたびに5本のインデックスを全部更新するコストが積み重なっていた。
インデックスはSELECTを速くするが、INSERT/UPDATE/DELETEを遅くする。
インデックスが増えるほど書き込み時の更新コストが増える。
読み取り専用のデータなら全インデックスを貼っていいが、書き込みが多いテーブルでは本当に必要なものだけに絞るべきだ。
どのインデックスが実際に使われているかは、PostgreSQLのシステムビューで確認できる。
SELECT
indexrelname AS index_name,
idx_scan AS times_used,
idx_tup_read AS tuples_read,
pg_size_pretty(pg_relation_size(indexrelname::regclass)) AS index_size
FROM pg_stat_user_indexes
WHERE relname = 'orders'
ORDER BY idx_scan DESC;idx_scan が0か極端に少ないインデックスは、実際には使われていない可能性が高い。
この確認を怠っていたため、使われもしないインデックスを量産していた。
不要だと判明したインデックス3本を削除したら、CPU使用率は元に戻った。
定期的に pg_stat_user_indexes を眺めて、不要なインデックスを整理する習慣をつけることを強く勧める。
部分インデックスでさらに30%改善した話
もう一つ効果的だったのが部分インデックス(Partial Index)だ。
インデックスの対象を特定の条件を満たすレコードだけに絞り込む機能で、PostgreSQL特有の機能の中でも特に使い勝手がいい。
注文テーブルの場合、status = 'pending'(処理待ち)のレコードだけを頻繁に検索していた。
完了済み(shipped, cancelled)のレコードは参照されることがほとんどない。
全体の10%程度がpendingで、残り90%はほぼ死んでいるデータだ。
通常のインデックスは全レコードを対象にするが、部分インデックスは条件で絞れる。
CREATE INDEX idx_orders_pending_user
ON orders (user_id, created_at DESC)
WHERE status = 'pending';WHERE status = 'pending' を付けることで、インデックスが持つデータ量が全体の10%程度になった。
サイズが小さいほどメモリに乗りやすく、I/Oが減る。
部分インデックスを入れたあと、pending 状態の検索クエリはさらに30%速くなった。
インデックスのサイズ確認はこれで確認できる。
SELECT
indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE tablename = 'orders'
ORDER BY pg_relation_size(indexname::regclass) DESC;通常インデックスが1.2GBだったのに対し、部分インデックスは120MB程度で済んだ。
この差はディスクコストだけでなく、バキューム処理の速度やキャッシュヒット率にも効いてくる。
やること一択のチェックリスト
今回の改善で変わった数字を整理すると、クエリの応答時間が8秒から0.07秒になり、インデックス過剰追加でCPUが3倍に上がり、不要インデックス削除で元に戻り、部分インデックス導入でさらに30%改善した。
インデックス設計で最初にやることは EXPLAIN ANALYZE を読むことだ。
感覚で貼るより、実行計画を読んでから設計するほうが10倍速い。
感覚で貼った結果が今回の最初の状態だった。
作業の流れとしては、スローログでボトルネックを特定、EXPLAIN ANALYZE で原因を確認、複合インデックスか部分インデックスを設計、効果を再度 EXPLAIN ANALYZE で測定、pg_stat_user_indexes で定期的に棚卸しする、という順番が無駄が少ない。
「インデックスを貼れば速くなる」という誤解が問題を長引かせた。
適切な列の組み合わせ、列の順序、部分インデックスの活用、書き込みコストとのトレードオフ——これを全部考えて初めて機能する。
300万件規模では今回の手法でほぼ解決できるが、1000万件を超えてくるとパーティショニングやクラスタリング、あるいはそもそもの正規化設計を見直す必要も出てくる。
その話はまたいつか書く。