反復処理の排除とサブクエリ最適化の例題

本記事で使うデータ — perf_sales と employee

前回は IN / EXISTS と相関サブクエリの書き換えを見ました。

本記事はその応用で、同じ集計を何度も書かない書き換え（重複する集計を 1 つの中間結果にまとめる）と、派生表のマテリアライズ（FROM 句のサブクエリを 1 回作って一時的に保持すること）を、具体的な例題で練習します。

演習に入る前に、本記事で使う 2 つのテーブル — perf_salesとemployee — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(perf_sales);とPRAGMA table_info(employee);で両テーブルの列定義を確認してください。

② SELECT * FROM perf_sales LIMIT 5;とSELECT * FROM employee LIMIT 5;で先頭 5 行のデータをプレビューしてください。perf_salesは 5 万行あるので、必ずLIMITを付けて確認してください。

同じ集計を何度も書かない — 重複を 1 つの中間結果に

「region ごとの売上合計が、全 region の平均合計より多いか」を出したいとき、合計と平均を別々のサブクエリで 2 回書くと、perf_salesを 2 回走査します。

同じ集計を1 つの CTE にまとめて 1 回だけ計算し、その結果を使い回すと、走査が 1 回で済みます。

書き換えの型は「同じGROUP BYの集計を本体とサブクエリの両方に書いている」のを見つけ、その集計を 1 つの CTE に出して、本体側ではその CTE を参照する形に直すものです。

結果は同じで、計画から重複走査が減ります。

-- 書き換え前: 合計と「全体平均」を別々に集計 (perf_sales を 2 回)
SELECT region, SUM(amount) AS total
FROM perf_sales
GROUP BY region
HAVING SUM(amount) > (
  SELECT AVG(t) FROM (
    SELECT SUM(amount) AS t FROM perf_sales GROUP BY region
  )
);

-- 書き換え後: region 集計を CTE に 1 回だけ
WITH region_total AS (
  SELECT region, SUM(amount) AS total
  FROM perf_sales
  GROUP BY region
)
SELECT region, total
FROM region_total
WHERE total > (SELECT AVG(total) FROM region_total)
ORDER BY region;

「region ごとの売上合計のうち、全 region の平均合計を上回る region」を、重複集計版と CTE 版の 2 通りで実行して計画を比べます。

① EXPLAIN QUERY PLANを先頭に付けて、GROUP BY regionの集計を本体とHAVINGのサブクエリの両方に書いた版の計画を表示してください。

② 同じ region 集計を1 つの CTEにまとめ、平均はその CTE からAVGで取り、上回る region をORDER BY regionで出す版を書き、その計画もEXPLAIN QUERY PLANで表示してください。

③ perf_salesのSCANが、書き換え前は複数回、書き換え後は 1 回に減っていることを見てください。

相関の繰り返しを 1 回の集約に畳む

「各 region の売上合計が、その region 内の社員別最大合計より大きいか」のような条件を、外側の region 行ごとに相関サブクエリで計算すると、region の数だけ内側を評価し直します。

これもキーで 1 回だけ集約した表を作り、JOIN で結ぶ形に畳めます。

書き換えの型は「外側の行ごとに(SELECT 集計 ... WHERE 子.キー = 親.キー)を呼ぶ」を、「GROUP BY キーの集計表を 1 回作る → 親に JOIN する」に変えるものです。

region 数ぶんの再評価が、集計 1 回 + 結合に置き換わります。

-- 書き換え前: region ごとに「社員別最大合計」を相関で
SELECT region,
  (SELECT MAX(s) FROM (
     SELECT SUM(amount) AS s FROM perf_sales p2
     WHERE p2.region = p1.region GROUP BY emp_id
  )) AS max_emp_total
FROM perf_sales p1
GROUP BY region;

-- 書き換え後: (region, emp_id) で 1 回集計し、region で最大を取る
WITH emp_region AS (
  SELECT region, emp_id, SUM(amount) AS s
  FROM perf_sales
  GROUP BY region, emp_id
)
SELECT region, MAX(s) AS max_emp_total
FROM emp_region
GROUP BY region
ORDER BY region;

「region ごとに、その region 内の社員別売上合計の最大値」を、相関版と 1 回集約版の 2 通りで実行して計画を比べます。

① EXPLAIN QUERY PLANを先頭に付けて、外側の region 行ごとに「その region 内の社員別合計の最大」を相関サブクエリで求める版の計画を表示してください。

② (region, emp_id)で1 回だけGROUP BYした CTEを作り、その CTE をregionでGROUP BYして最大を取る版を書き、その計画もEXPLAIN QUERY PLANで表示してください。

③ 相関版に出るCORRELATEDの語が、書き換え版では消えていることを見てください。

派生表のマテリアライズ — FROM 句のサブクエリを 1 回作る

FROM 句に書いたサブクエリ（派生表）を 2 か所で参照すると、最適化器がそれを 2 回計算してしまうことがあります。

重い派生表を1 回だけ作って一時的に保持する（マテリアライズする）と、参照のたびに作り直さずに済みます。

SQLite では CTE を複数回参照するとEXPLAIN QUERY PLANにMATERIALIZEが現れ、その CTE が 1 回だけ実体化されることが分かります。

本記事の最後の演習では、perf_salesをemp_idで集計した重い派生表を 2 回参照する形を、CTE にして 1 回だけマテリアライズさせ、計画にMATERIALIZEが出ることと走査が 1 回になることを確認します。

-- 書き換え前: 同じ派生表を 2 つの FROM で参照
SELECT a.emp_id, a.tot, b.avg_tot
FROM (SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id) a
CROSS JOIN (
  SELECT AVG(tot) AS avg_tot FROM (
    SELECT emp_id, SUM(amount) AS tot FROM perf_sales GROUP BY emp_id
  )
) b
ORDER BY a.emp_id;

-- 書き換え後: 派生表を CTE にして 2 回参照 (1 回だけ実体化)
WITH emp_total AS (
  SELECT emp_id, SUM(amount) AS tot
  FROM perf_sales
  GROUP BY emp_id
)
SELECT e.emp_id, e.tot, (SELECT AVG(tot) FROM emp_total) AS avg_tot
FROM emp_total e
ORDER BY e.emp_id;

「各社員の売上合計と、全社員の平均合計を横に並べた一覧」を、派生表 2 回参照版と CTE 版の 2 通りで実行して計画を比べます。本記事の最後の演習です。

① EXPLAIN QUERY PLANを先頭に付けて、emp_idで集計した派生表を FROM 句で 2 か所参照する版の計画を表示してください。

② 同じ集計を1 つの CTEにまとめ、平均はその CTE から(SELECT AVG(tot) FROM 同じ CTE)で取り、emp_id昇順で出す版を書き、その計画もEXPLAIN QUERY PLANで表示してください。

③ 書き換え版の計画にMATERIALIZEが現れ、SCAN perf_salesが 1 回に減っていることを見てください。