大きなテーブルの遅い集計クエリを改善するには？

Question

IT Interview Practice · Accepted Answer

遅い集計を最適化するには、まず**結果が何を意味し、どの処理に時間がかかるか**を明らかにします。集計は行をまとめて、加盟店別売上などの結果を作る処理です。コストは読み取って組み合わせるデータ量に左右されます。

先月の売上を表示するダッシュボードを例に、クエリを改善するか、集計済みの結果を保存するか、レポート処理を別の場所に移すかを判断します。

## 1. 高速化する前に結果の正しさを確認する

売上が支払い済み注文と作成済み注文のどちらを指すか、返金をどう扱うか、対象通貨は何かを明確にします。ダッシュボードに必要な鮮度も確認します。これらによって、クエリも利用できる最適化も変わります。

よくある誤りは、支払いを複数の注文明細と結合してから、支払い金額を合計することです。

```text
[Payment: 100] -> [Join with 3 items] -> [100, 100, 100]
                                               |
                                          [SUM = 300]
```

数値は説明用です。このクエリは支払いを重複させているため、高速化しても誤った合計を早く返すだけです。性能を評価する前に、業務上意図した単位で集計するか、結合を修正します。

## 2. 実行のどこが高コストかを調べる

実行計画は、結果を得るために使う処理を示します。単にインデックスが足りないと考えず、高コストの段階とその入力サイズを確認します。

| 観測したこと | 調べること |
| --- | --- |
| 無関係な行を大量に読む | フィルターやパーティション除外で避けられるか。 |
| 実際の行数と推定値が大きく違う | 統計情報はデータの実態を表しているか。 |
| 結合が同じ処理を何度も繰り返す | 入力が大きすぎるか、アクセス方法が不適切か。 |
| 集約やソートが一時データをディスクに書く | メモリ上限を変える前に入力を減らせるか。 |

PostgreSQLのEXPLAIN (ANALYZE, BUFFERS)は、実際にクエリを実行して実行時情報を返します。高負荷の調査は管理された環境か、明確に決めた本番負荷の予算内で行います。[PostgreSQL EXPLAIN](https://www.postgresql.org/docs/current/using-explain.html)。

## 3. 現在のデータベースで処理量を減らす

ダッシュボードに必要な月と列だけを選びます。実行計画が無関係なパーティションを本当に飛ばしているか、結合が集計に不要なデータを持ち込んでいないか確認します。

インデックスは十分な読み取りを省ける場合に有効です。レポートが大半の行を必要とするなら、順次走査が適切な場合もあります。インデックスが使われたかだけでなく、実行計画と実測コストで判断します。

ソートがディスクにあふれるなら、入力を減らすことで解決するかもしれません。すべてのクエリのメモリを増やすと、多数のレポートが同時実行されたときにRAMが不足する可能性があります。実態に近い並行負荷で試します。

## 4. 結果を事前に計算するか決める

多くのユーザーが同じ合計を求め、多少の遅れを許容できるなら、生の注文を毎回走査するのは無駄です。日、加盟店、通貨ごとの合計を集計テーブルに保存し、ダッシュボードで小さな結果を組み合わせられます。

```text
[Orders] -> [Scheduled / incremental calculation]
                              |
                       [Daily summaries] -> [Dashboard]
```

利点は、共通の計算をリクエストごとではなく一度行うことです。マテリアライズドビューもクエリ結果を保存する方法ですが、独自の更新動作があります。

削減できる処理と引き換えに、次の責任が生まれます。

- 更新頻度を定め、ユーザーに必要な鮮度の情報を示します。
- 返金、遅れて届いた注文、修正を二重計上せず反映します。
- 繰り返し処理を安全にし、集計を再構築する手段を残します。
- 平均を組み合わせるなら合計と件数を保存します。平均同士の平均は誤ることがあります。

このダッシュボードでは、鮮度と修正のルールが最初の要件を満たす場合に限り、事前集計が候補になります。

## 5. 測定結果が必要性を示したらレポートを分離する

広範囲の分析走査が高コストのままで購入処理を妨げるなら、その負荷に適した分析用ストアを検討します。同期遅延、復旧、別システムを運用するコストも含めます。

読み取りレプリカはレポート負荷を隔離できますが、同じクエリはそこで実行しても高コストかもしれません。分離はコストを負担する場所を変えるもので、必ずしもコストをなくしません。

各変更を、信頼できる結果と同じテスト負荷で比較します。応答時間、資源、鮮度、書き込みへの影響を確認します。

**要点：**正しい結果 → 実測したボトルネック → 不要な処理の削減 → 適切なら事前集計 → それでも必要なら分析の分離。

観測したこと	調べること
無関係な行を大量に読む	フィルターやパーティション除外で避けられるか。
実際の行数と推定値が大きく違う	統計情報はデータの実態を表しているか。
結合が同じ処理を何度も繰り返す	入力が大きすぎるか、アクセス方法が不適切か。
集約やソートが一時データをディスクに書く	メモリ上限を変える前に入力を減らせるか。

1. 高速化する前に結果の正しさを確認する

2. 実行のどこが高コストかを調べる

3. 現在のデータベースで処理量を減らす

4. 結果を事前に計算するか決める

5. 測定結果が必要性を示したらレポートを分離する

関連する質問