はーいー、どもですー！

「CTEで書いた集計が遅いから中間テーブルを作ったら速くなった」という話を聞くことがあります。ただDatabricksでそれが本当にいつも得なのかが気になりすぎて測ってみました！

結論から言うとこれですが、ココから詳しくお話していきます。

先に書いておくと、Databricksの公式見解ではございません！（汗

よくある話ってやつ

CTEで綺麗に書いたクエリが遅くて、中間結果をテーブルに落としたら速くなったのでCTEはやめました、みたいなやつ。

CTEは論理的な名前でしかないので、オプティマイザがどう扱うかは保証されない。3回参照したら3回計算されうる。物理的なテーブルなら1回計算して終わりです。雑に言うと、計算をまとめられるか、テーブル作成のコストがデカいか。

Databricksで中間データを置く方法は4つ。CTE、TEMP VIEW、TEMP TABLE、それとUnity Catalogのマネージドテーブル。TEMP TABLEはセッションスコープのDeltaテーブルで、セッションが終われば勝手に消してくれます。

テストデータの作成

売上テーブルを作りました。24か月分で20億行 / 21.9GB / 976ファイル。それに商品1,000件と顧客10万件です。
対象の中間集計は、ディメンション2つとJOINして4キーでGROUP BYする形です。

SELECT
    v.customer_id, v.product_id, p.category, c.region,
    SUM(v.amount) AS revenue
FROM sales v
INNER JOIN products p ON v.product_id = p.product_id
INNER JOIN customers c ON v.customer_id = c.customer_id
WHERE v.order_date >= DATE'2026-01-01'
GROUP BY v.customer_id, v.product_id, p.category, c.region

入力10億行から中間が5,654万行になる、けっこう重い集計です！ これをbaseと呼んで、顧客別・商品別・カテゴリ別の3本で使い回します。

-- 顧客ごとに、500を超えた商品の本数と最高額
SELECT customer_id, COUNT(*), MAX(revenue)
FROM base WHERE revenue > 500 GROUP BY customer_id;
-- 商品ごとに、顧客1人あたり売上の平均
SELECT product_id, AVG(revenue)
FROM base GROUP BY product_id;
-- カテゴリごとに、顧客×商品の売上の中央値
SELECT category, approx_percentile(revenue, 0.5)
FROM base GROUP BY category;

CTEは3回書いたら3回計算されてました（やっぱり

CTEは1文の中でしか生きないので、3本ぶん同じWITHを書き直すことになります。UNION ALLで1文にまとめたバージョンも測りました！

EXPLAINで物理プランのノードを数えると、salesのスキャンが3回。CTEでまとめられず、3回インライン展開されています。

  PhotonScan parquet ... sales   出現回数: 3
  ReusedExchange                 出現回数: 0
  CTERelationRef                 出現回数: 0

ただAQEで実行中にプランが組み替わる可能性は残るので、Query Historyから実際に読んだ行数も確認しました。

30億行、、、10億行のソースをきっちり3回読んでいます。1文にまとめてもTEMP VIEWでも同じで、名前が付くと再利用できる気がしてくるんですが（しない）毎回展開されていました（そらそう

TEMP TABLEにしたらはやくなった！

ちゅーわけでTEMP TABLEにしてみましょう！

勝利？です！ 読む行数が30億から11.7億になって、実時間も0.57倍。マネージドテーブルのほうが若干速いですが誤差と言えると思います。

じゃあマテリアライズするのが正解？

さっきの3本は、顧客と商品の組み合わせの1行1行を数えたり平均したりしています。だから顧客と商品の組み合わせの集計を省けません。
じゃあ1行ずつ見なくていいクエリ、つまり売上を合計するだけならどうなるんだろう、と。

SELECT customer_id, SUM(revenue) FROM base GROUP BY customer_id;
SELECT product_id,  SUM(revenue) FROM base GROUP BY product_id;
SELECT category,    SUM(revenue) FROM base GROUP BY category;

baseなども変えず、クエリだけ差し替えて測り直したら、こうなりました。

マテリアライズしたほうが2.13倍遅い！ 逆転！

Query Profileでtask時間を足すと、10億行を3回スキャンして36.5秒、中間を1回作って100.9秒。中間を1回作るほうが、ソースを3回読むより2.8倍遅いんです。
なぜかと見てみるとPhotonが2段階の集計を1段階にまとめて、顧客ごとに1回SUMするだけにしていました。マテリアライズすることでその最適化が行われなくなったわけです。

なん、ですが。
これPhotonがいるときだけの話で、同じDBR・同じノード4台でPhotonを無効化するとこうなります。

非Photonだとマテリアライズしたほうが早いんです
EXPLAIN EXTENDEDを見ると、Photonなしでは4キーの集計がプランに残っていました
つまり基本はマテリアライズが得で、Photonが入ると最適化が入るためそこで初めてCTEが早いパターンが発生する、ということです！

はい、みなさんご一緒に、Photonすごい！

境目は「中間の集計値を使うかどうか」

じゃあ何が分けるのか。中間テーブルは同じにして、下流の1文だけを変えて測り直しました。

WHERE revenue > 0。1行足すだけで3.07倍に、、、！
CTEでもTEMP VIEWでもネストしたサブクエリでも同じですが、そのSQLの計算に5,654万行の集計が入るかどうかは下流の書き方で決まります。
中間の集計値を使った場合は5,654万行を実際に作らないと答えが出せなくなります。AVGも同じ。

先ほどもあった通り、Photon環境ではCTEが勝ちます。Databricksにおいては「CTEだから遅い」わけではなかったわけです。

CTEもマテリアライズも適材適所！

見るのは下流のクエリが中間テーブルの集計値を必要とするかどうか。要るならマテリアライズが得で、要らないならPhoton環境においてはCTEのままオプティマイザに任せたほうが速い！ です。

マテリアライズするなら先に列と行を削るのも重要！全列12か月ぶんで12.94GB書くところが、3列1か月なら0.42GB。31分の1です。必要なデータの見極め大事です

そんなこんなで、SQLのチューニングは楽しいですね、、、（震え声
Photonの集計のまとめ方はだいぶ賢くて、ただ書き方によってその良さが消えることもある、というのが今回の収穫です。手元のクエリでもQuery Profileを見ると面白いものが出てくるかもですので、是非やってみてください！

ではではー！