はじめに
今回は、数百万件スケールのテーブルを扱う API が「最大約 38 秒」かかっていたところから、「約 40 ミリ秒」まで短縮した取り組みをまとめます。
実際に行ったのは、派手な最適化ではなく 基本の徹底 です。この記事が、同じ課題を抱える方の再現可能なチェックリストになれば幸いです。
結論
今回行ったのは、EXPLAINで特定したボトルネックに対する、以下の3つの基本的な改善です。
-
複合インデックスの作成:
WHERE句とORDER BY句をカバーし、検索を高速化 -
取得カラムの限定:
SELECT *をやめ、本当に必要なデータだけを取得 -
集計クエリの分離: 件数取得には
COUNT(*)を使い、不要なデータ転送をなくす
この「基本の徹底」により、APIの応答時間は 38秒から40ミリ秒（99.9%削減） へと劇的に改善し、タイムアウトも解消されました。
どんな問題だったのか
ユーザーの「メールログ」一覧を返す API が、データ件数の増加とともに極端に遅くなっていました。具体的には以下の処理です。
- ユーザーごとのメールログを新着順に返す
- 未読バッジの件数を計算する
その結果、最大約 38 秒という致命的な遅延が発生し、ログイン直後の体験にも悪影響を与えていました。
また、本来は件数だけで十分な未読バッジ表示に「一覧 API」が流用されており、これが全体の遅延を助長していました。
まず行ったこと
ボトルネックを調査するために、EXPLAIN ANALYZE を使用しました。
PostgreSQLなどのデータベースには、実行されたSQLクエリが「なぜ遅いのか」を分析するための EXPLAIN ANALYZE という機能があります。
この機能は、クエリの「実行計画書」のようなものを出力してくれます。計画書を読んでみると、私たちのAPIが遅い原因は主に以下の3つだと分かりました。
- 全件スキャン（Seq Scan）: データを端から端まで全部読んでいた
- 巨大なデータ幅（width）: 不要なデータまで取得し、1行が重くなっていた
- 重い並べ替え（Sort）: 全データを読み込んだ後に並べ替えており、負荷が高かった
この「調査機能」のおかげで、憶測ではなく事実に基づいて改善を進めることができました。
遅延の主因（3 つに集約）
1. 複合インデックスがなく、毎回「総当たり検索」
クエリは「user_id で絞る」「送信日時で並び替える」「未読メールを返す」という典型的な形でしたが、WHERE + ORDER BY に対応した複合インデックスがなかったため、毎回 すべてのデータを1件ずつ確認していました。
これがいわゆる O(N) の状態です。
データ件数に比例して時間が増えるので、件数が100倍になると遅延もほぼ100倍になります。
2. JOIN で行幅が肥大化
一覧 API で「本文（TEXT）」など大きなカラムを持つテーブルをそのまま JOIN。
その結果、1 行あたりのサイズが約 60B → 約 1,130B に膨らみ、転送・ソート・メモリに負担をかけていました。
3. 件数だけ欲しい場面で“一覧を取ってから数えていた”
未読バッジのように「件数だけ必要」な場面でも一覧 API を呼び、クライアントで filter → count する非効率な設計でした。
対策（再現性の高い順）
対策 1： WHERE / ORDER BY に合わせた複合インデックス
CREATE INDEX CONCURRENTLY idx_user_mail_logs_userid_isread_sendat
ON user_mail_logs (user_id, is_read, send_at);
ポイント
- 「絞り込みに使う列」→「並べ替えに使う列」の順で並べると効率的
- 本番環境では
CONCURRENTLYを使うことで、サービスを止めずに作成可能
対策 2： JOIN は軽量化または遅延取得
悪い例（SELECT * と JOIN で全カラムを取得）
-- 悪い例: 不要な本文データなども取得してしまう
SELECT *
FROM user_mail_logs ul
LEFT JOIN newsletters n ON n.id = ul.newsletter_id
WHERE ul.user_id = $1
ORDER BY ul.send_at DESC;
良い例（必要なカラムだけを指定し、クエリを分割）
一覧表示に必要な最小限のカラムだけを取得するようにクエリを修正します。
関連データは、アプリケーション側で別途取得（N+1問題に注意）するか、IN句でまとめて取得するのが効果的です。
-- 1. まず一覧の基本情報を取得
SELECT id, newsletter_id, is_read, send_at
FROM user_mail_logs
WHERE user_id = $1
ORDER BY send_at DESC;
-- 2. 取得した newsletter_id を元に関連情報だけを取得
SELECT id, subject
FROM newsletters
WHERE id = ANY($2); -- $2 は 1. で取得した newsletter_id の配列
この「クエリ分割」により、一覧表示のレスポンスが劇的に改善し、データ転送量も大幅に削減できます。
対策 3： 件数は専用 API で返す
SELECT COUNT(*)
FROM user_mail_logs
WHERE user_id = $1
AND is_read = false
AND send_at <= NOW();
一覧 API を流用せず、専用の「件数 API」を用意することで劇的に軽量化できます。
なぜ速くなったのか
今回の改善は、以下の3つの相乗効果によるものです。
- 計算量の改善: インデックスにより、検索効率が O(N) → O(log N) に向上。データが増えても遅延が伸びにくくなった
- データ量の削減: 取得カラムを厳選したことで行幅（Width）が最小化され、ソート・メモリ・転送のコストが激減
- 処理の最適化: 件数取得を
COUNT(*)に分離したことで、DB → アプリ間の不要なデータ転送やJSONパースがなくなった
複数の改善が掛け算的に効き、体感速度が劇的に変わりました。
実務チェックリスト
- WHERE と ORDER BY に対応する複合インデックスを作成しているか
- 一覧 API で大きなカラムを JOIN していないか
- 件数は専用 API で返す設計になっているか
-
EXPLAIN ANALYZEで Seq Scan / Sort / width を確認したか -
本番適用時に
CREATE INDEX CONCURRENTLYを使用したか
おわりに
今回は「基本の徹底」で 38 秒 → 40 ミリ秒まで短縮しました。
ポイントは DB が得意な仕事（検索・並べ替え・集計）を正しく任せること。困ったときはまず EXPLAIN を確認し、索引と行幅を見直すのが近道です。
再現性の高い改善から順に取り組むことが、最短の解決につながります。