速くなった集計が、金額を二倍にした

架空事例：青葉機材の予約と配送を集計する。金額、行数、問い合わせは本書の作例である。SQLの小例はSQLiteで計算結果を照合し、製品仕様の説明はPostgreSQL 18の公式文書に照合した。PostgreSQL上で性能を実測したという意味ではない。

明細をつなぐと、予約が増えたように見える

予約10の確定金額は一万二千円だった。二つの倉庫から機材を届けるため、配送記録が二件ある。予約と配送を結合し、配送情報も含む一覧を作った。

一覧は正しく二行になった。各行には、予約10の金額一万二千円が表示される。その金額列をSUMすると、二万四千円になる。

計算した関数は正しい。集計する行の意味が間違っていた。

一つの予約に複数の配送が対応するなら、結合後の一行は、もとの予約一件ではなく、予約と配送の一組になる。予約単位の値をそのまま合計すれば、配送の件数だけ繰り返して数える。DBMSが高速に実行したことと、業務上ほしい集計を得たことは別である。1

この問題は、索引を追加しても直らない。むしろ間違った数字が速く出るようになる。問い合わせを最適化する前に、各段階で一行が何を表し、何件へ増減するかを追う必要がある。

DISTINCTを付ければよいとは限らない

金額の重複を除こうとして、SUM(DISTINCT amount) と書く案が出た。予約10だけなら、一万二千円となって正しく見える。

しかし、同額一万二千円の別の予約11も存在したらどうなるか。DISTINCTが除くのは、金額の値の重複であって、同じ予約を二度数えることだけではない。二件の別予約の合計まで一万二千円へ減ってしまう。

本事例で集計したい単位は、金額ではなく予約である。予約単位で集計した結果を配送の集計へつなぐ、配送が存在するかだけを条件として調べる、必要な予約を先に一意に特定するなど、目的に応じた方法を選ぶ。

どの方法でもよいわけではない。配送先ごとの売上を知りたいなら、一つの予約金額を複数の配送先へどう配分するかという業務規則が必要になる。SQLの書き方を変えるだけで、存在しなかった配分ルールを作ってはいけない。

ゼロ件の顧客を、一覧から消さない

顧客1には予約が一件、顧客2には予約がないとする。顧客ごとの予約件数を一覧にし、予約のない顧客もゼロと表示したい。

顧客を左側にしたLEFT JOINなら、対応する予約がない顧客にも、予約側の値がNULLとなる行が残る。この結果に COUNT(*) を使うと、その一行も数えるため、顧客2の件数が一になる。NULLを許さない予約IDを対象に COUNT(booking.id) とすれば、対応する予約IDがない顧客2はゼロとなる。23

COUNTが数える対象を言葉にすると、違いが見える。COUNT(*)は行を数え、COUNT(式)は式がNULLでないものを数える。式がたまたま同じ値でも、DISTINCTを指定しなければ一つにまとめるわけではない。3

さらに、確定した予約だけを数えたい場合、条件の位置が結果を変える。

SELECT c.id, COUNT(b.id)
FROM customer AS c
LEFT JOIN booking AS b
  ON b.customer_id = c.id
 AND b.status = 'confirmed'
GROUP BY c.id;

この例では、顧客ごとに確定予約を対応付け、該当する予約がない顧客も残す。一方、確定状態の条件をWHEREにだけ置くと、予約側がNULLの行がその条件を満たさず、予約のない顧客が結果から消える。2

「同じ条件式だから、どこへ移してもよい」と考えてはいけない。外部結合では、どの行を対応付けるかの条件と、結合後のどの行を残すかの条件が異なる。

未取得、対象なし、金額ゼロを同じ値にしない

売上がゼロであることと、集計対象が一件もないことは、業務上同じ意味とは限らない。データが未取得であることも違う。

PostgreSQL 18では、SUMの入力行がない場合、結果はゼロではなくNULLになる。必要ならCOALESCEでゼロへ置き換えられるが、その変換を業務上行ってよいかは別に考える。3

青葉機材で、まだ倉庫から届いていない出庫データをゼロへ変換すると、「出庫がなかった」と誤って報告する可能性がある。未到着なのか、到着した結果がゼロ件なのかを識別できる記録が必要になる。

集計結果だけを保存すると、その分母や対象期間、未取得の範囲が失われる場合がある。数字の計算方法だけでなく、何を含み、何を除外した値なのかを残す。第1巻の業務指標と同じ問題が、データ処理の中にも現れる。

索引は、探し方を増やす代わりに更新を増やす

過去三年分の予約から、ある営業所の今月の確定予約を毎回探す。全件を読むより、条件に合う場所へ絞り込める索引が有用な場合がある。

ただし、索引は無料ではない。保存領域が必要で、対象のデータを追加・更新・削除すると、その索引も維持する仕事が生じる。すべての列へ索引を付ければ、すべての処理が速くなるわけではない。PostgreSQLの索引の解説も、検索を助ける一方で更新時の負担を増やすことを説明している。4

条件が大量の行に一致するなら、索引から多数の行へたどるより、表を順に読む方が有利な場合もある。小さな表なら、索引を利用する準備の費用が見合わないかもしれない。「索引が使われていない」を、それだけで不具合と判断しない。

複数列の索引では、列の順序と検索条件の組合せが効く。B-treeの複合索引を、列を並べた順序付きの探索構造として考えると、先頭からどの範囲を絞れるかを理解しやすい。ただし、「先頭列に条件がなければ絶対に使えない」という断定は避ける。PostgreSQL 18には、条件や値の分布によって後続列を利用するskip scanの説明もある。5

原則を学びつつ、実装と版の機能を確認する。試験の一般的なモデルと、実際のDBMSが選ぶ計画は、同じ粒度の説明ではない。

EXPLAINの数字は、秒とは限らない

実行計画のcostが百から二百へ増えた。だから実行時間が二倍になった、と判断してよいだろうか。

PostgreSQLのEXPLAINに表示されるcostは、計画を比較するための推定値であって、そのまま秒を表す数値ではない。推定行数と実際の行数、読み込んだ範囲、繰返し回数、絞込みで捨てた行などを分けて読む。統計やデータの偏りが、計画の選択へ影響する。6

EXPLAIN ANALYZEでは問い合わせを実際に実行する。更新文を対象とすれば更新も行われるため、単なる閲覧コマンドと考えて本番へ流してはいけない。また、その計測は通常の利用者への通信や表示を含む全体の応答時間と同じではなく、計測自体の負担もある。6

青葉機材では、対象となる期間や営業所を変え、実データに近い件数と偏りの下で比較する。一件しかない小さな試験表の結果を、数百万件の本番へそのまま外挿しない。条件を変えれば、選ばれる計画も変わり得る。

実行計画は、どこで仕事が増えているかを説明する証拠になる。一方、見たことのある計画名を使わせること自体が目的ではない。元の業務に必要な結果を、目標の条件で得られるかを評価する。

結果を先に保存すると、今度は鮮度を管理する

月ごとの予約金額を毎回集計すると時間がかかる。そこで、集計結果をあらかじめ保存し、画面はその結果を読む案を考える。

通常のビューは、問い合わせを仮想的な表として扱うための仕組みであり、単に定義しただけで結果のコピーが常に保存されるわけではない。マテリアライズドビューは結果を保持するため、参照を速くできる場合があるが、元の更新と結果の更新の時点を考える必要がある。PostgreSQL 18でも、保存した結果が必ず最新とは限らない点が説明されている。17

月次の傾向分析なら、昨夜までの確定値で十分かもしれない。今この予約を確定してよいかを判断するなら、同じ鮮度では足りない可能性がある。参照用に速くした値を、すべての更新判断の正本へ使うべきではない。

青葉機材では、一覧に集計対象の最終反映時点を示し、確定処理には必要な更新側の条件を使う案を検討する。ただし、時刻を表示すればどんな遅延も許されるわけではない。業務で許せる古さ、更新失敗の検知、再構築の方法を定める。

正しさ、速さ、運用を別々に確かめる

問い合わせの結果が正しいかは、期待する行と値で確認する。性能は、合意した負荷とデータで測る。運用は、索引や集計結果が更新・復旧でき、必要な鮮度を維持できるかを確かめる。

この三つを一つの「改善できた」という評価へまとめると、間違った集計が速くなっただけでも成功に見える。逆に、正しい結果が出ても、毎日の再集計が業務時間まで終わらなければ使えない。

データアーキテクチャの出発点は、何を事実として保存するかだった。最後の照会まで、その問いは残る。結合や索引やキャッシュを通した先で、利用者が読む数字が、最初に合意した意味を保っているかを確認する。

知識を答案へ戻す

午前Ⅱ。 キーと結合後の行数、COUNTの対象、NULL、ビューとマテリアライズドビューを区別する。物理的なアクセス方法の改善は、論理的な集計の誤りを修正するものではない。

午後Ⅰ。 何を一件として数えるか、結合で何倍に増えるか、対象のない行を残すかを読む。設問が予約件数を求めるのに、配送件数や値の種類を数えない。

午後Ⅱ。 性能を改善した方式、維持した結果の意味、鮮度と更新負担の扱いを説明する。索引を追加した、集計表を作ったという実施事項だけでなく、適用する条件と検証結果を示す。

立ち止まって考える：結合後の金額が重複した。SUM(DISTINCT 金額)でよいか

別の予約が同額である場合まで一つにまとめるため、一般には解決にならない。集計したい単位は金額の値か、予約か、配送かを特定し、その単位を保った状態で集計する。SQLの修正前に、結果の一行が表す意味を確認する。

出典と照合範囲

[1] コンプリートテキスト第14・16・17・18章、IPA「SAシラバス Ver.5.2」10-3、11-3、18-1：論理設計、参照、性能、運用評価を基礎とした。予約と配送の件数・金額の例は本書独自のデータである。公式シラバス

[2] PostgreSQL 18, Table Expressions：結合の行の生成、LEFT JOINとON・WHEREの違いを照合した。公式文書

[3] PostgreSQL 18, Aggregate Functions：COUNTの対象、SUMの空集合とNULLの扱いを照合した。公式文書

[4] PostgreSQL 18, Indexes — Introduction：探索の改善と、索引維持・容量の負担を参照した。公式文書

[5] PostgreSQL 18, Multicolumn Indexes：複合索引の条件と、skip scanを含む実装上の注意を照合した。公式文書

[6] PostgreSQL 18, Using EXPLAIN：推定cost・行数、実測との区別、ANALYZEの実行、副作用、計測条件の限界を参照した。公式文書

[7] PostgreSQL 18, Materialized Views：結果の保持と鮮度の関係を照合した。公式文書