CTE

どのようなものか

CTEはcommon table
expressionの略で、日本語では共通テーブル式と呼びます。文の先頭にある
WITH句でクエリーに名前を付け、文の残りから、その行をテーブルのように
読めるようにします。一つの文で複数のCTEを定義でき、各CTEは、それより
前に名前を付けたものを読めます。最後のクエリーで、それらを組み合わせ
ます。PostgreSQLのマニュアルは、「一つの問い合わせの間だけ存在する一
時テーブル」と説明しています。この捉え方は適切ですが、注意点が一つあ
ります。その名前で何かを保存するわけではなく、次の文からは見えませ
ん。各CTEはSELECTか、PostgreSQLではINSERT、UPDATE、DELETE、MERGEにも
できます。WITH句を付ける文も、そのいずれかにできます。

「直近30日間のある日に、そのメーター自身の普段の使用量の三倍を超える
使用量を記録したのは、どのメーターか」という問いは、自然に段階へ分け
られます。

```sql
WITH daily AS (
  SELECT meter_id, read_at::date AS day, sum(kwh) AS kwh
  FROM readings
  WHERE read_at >= current_date - 30
  GROUP BY meter_id, read_at::date
),
typical AS (
  SELECT meter_id, avg(kwh) AS avg_kwh
  FROM daily
  GROUP BY meter_id
)
SELECT d.meter_id, d.day, d.kwh, t.avg_kwh
FROM daily AS d
JOIN typical AS t USING (meter_id)
WHERE d.kwh > 3 * t.avg_kwh
ORDER BY d.day, d.meter_id;
```

最初の段階は、各メーターの読み取り値を日ごとに合計します。二つ目は、
日別の合計をメーターごとに平均し、最後に、それぞれの日を、そのメー
ター自身の平均と比較します。同じ答えは、入れ子のサブクエリーでも書け
ます。ただし、日別合計が、片方をもう片方の内側に置く形で二回現れ、読
む人は内側から外側へ追う必要があります。WITHなら、上から下へ順に論理
を読め、検査中は各段階だけを実行できます。

RECURSIVEを加えると、CTEが自分の出力を読めます。通常のSQLでは、ほか
の方法で表せない部分です。再帰CTEは、最初のクエリー、UNIONかUNION
ALL、CTE自身を参照するクエリーという順で構成します。PostgreSQLは、最
初のクエリーを実行し、その行を作業テーブルに保持します。その後、最新
の行を使って二つ目のクエリーを繰り返し、新しい行が加わらなくなったら
止まります。一つの見出しの下のカテゴリーなど、深さが分からない木構造
を、一つの文でたどる仕組みです。

```sql
WITH RECURSIVE subtree AS (
  SELECT id, parent_id, name, 1 AS depth
  FROM categories
  WHERE id = 42
  UNION ALL
  SELECT c.id, c.parent_id, c.name, s.depth + 1
  FROM categories AS c
  JOIN subtree AS s ON c.parent_id = s.id
)
SELECT id, name, depth FROM subtree ORDER BY depth, name;
```

なぜ重要なのか

第一の理由は、読みやすさです。四段の入れ子を持つ百行の分析クエリー
は、レビューしにくく、変更はさらに困難です。同じ論理を名前付きの段階
につなぎ直せば、レビューする人は一段階を一つの文として捉えられ、書く
人にはコメントを置く場所ができます。誰でも、次の段階を信用する前に、
一つの段階だけを実行し、行が正しそうか確かめられます。

第二の理由は、データベースが段階をどう評価するかです。この詳細は、時
代とともに変わりました。PostgreSQLは通常、一つの文の実行につき、WITH
のクエリーを一回だけ評価します。その文が複数回参照していても同じなの
で、二か所で必要な高コストの計算も、一回で済みます。その保証の代償
は、複数回参照するCTEの中へ、外側のクエリーのフィルターをプランナー
が押し込めないことです。そのフィルターは、一つの利用箇所には適用して
も、ほかには適用すべきでないためです。CTE全体を計算してから、不要な
行を捨てます。

PostgreSQL
12より前は、すべてのCTEがこの扱いでした。クエリーの残りより先に評価
し、その中へ統合しませんでした。バージョン12で既定の動作が変わり、再
帰ではなく、副作用がなく、一度だけ参照するCTEは、外側のクエリーへ組
み込むようになりました。プランナーが二つの階層をまとめて最適化し、外
側のフィルターを、元のテーブルのインデックスまで押し込めます。
MATERIALIZEDは以前の振る舞いを強制します。NOT
MATERIALIZEDは、複数回使うCTEでも組み込みを強制し、処理を二回行う危
険を伴います。SQLiteも、同じ二つのキーワードを、拘束力のないヒントと
して受け入れ、PostgreSQLから着想を得たとしています。ほかのエンジン
は、それぞれに判断するため、実行計画で確かめます。

第三の理由は、CTEでデータを変更できることです。WITH句内のDELETEが
RETURNINGで出力し、それを主文のINSERTへ渡せば、一つのテーブルから別
のテーブルへ、一つの文で行を移せます。全体を実行するか、一切実行しな
いかのどちらかです。データを変更するCTEは、出力を何かが読むかどうか
に関係なく、必ず一回、最後まで実行します。そのため、結果を記述するた
めというより、複数の書き込みをまとめて行うための道具になります。

実際の活用例

PostgreSQLの性能を10倍にした仕事の対象は、利用者アカウント、テナン
ト、場所、日ごとの電力読み取り値を持つデータベースでした。データが増
えるにつれて遅くなっていました。対策は三つです。対象を絞ったインデッ
クスとGiSTインデックス、読み取り値テーブルの時間ベースのパーティショ
ン分割、クエリーの書き直しを行いました。よくアクセスするデータには
CTEとマテリアライズドビューを使い、テーブル全体の走査を避けました。
組み替える価値のある結合やサブクエリーは、EXPLAIN
ANALYZEで見つけました。応答時間が元の十分の一になり、リソース使用量
が40%減ったのは、CTEだけではなく、この三つを組み合わせた成果です。二
つを組み合わせた点にも意味があります。CTEは、一つの文の間だけ中間結
果に名前を付けますが、マテリアライズドビューは、更新するまで、多数の
文から読める結果を保存します。

CTEのもう一つの面である読みやすさは、電力とGISデータの500本の分析レ
ポートを支えました。すべてのレポートを、PostgreSQLとBigQueryに対する
純粋なSQLで書きました。座標、メーターID、時刻付きの使用量を持つス
マートメーターのデータを対象とし、スクリプトでは、CTE、ウィンドウ関
数、サブクエリー、地理空間の結合を組み合わせました。各レポートには、
同僚が分析を再現し、監査できるよう、注記付きのSQLと、GPS座標の欠落や
メーター値の破損など、よくあるデータ品質の問題についての説明を付けま
した。段階を追って読めるSQLは、共有しやすいものです。レポートは部門
をまたいだ標準の参照資料となり、運用計画の効率を10–15%高めるのに役立
ちました。この種のSQLへ注記を付けられるのも、段階に名前があるためで
す。各段階の上に、何を取り除き、何を前提にするかを書けます。

問題が起きるところ

すべてを処理してから、フィルターを適用します。
二回参照するCTEや、MATERIALIZEDを指定したCTEは、外側のクエリーが絞り
込む前に、全体を計算します。そのコピーには、インデックスがありませ
ん。少数の行に触れるはずのクエリーが、テーブル全体を読みます。実行計
画を見ます。CTEが独立した段階として現れ、行数が最終的に返す行数を大
きく上回るなら、NOT
MATERIALIZEDを試すか、CTEを一回だけ参照する形に書き直します。高コス
トの関数を呼ぶCTEでは、実体化する形を保ちます。参照のたびではなく、
一行につき一回だけ関数を実行するためです。

再帰クエリーが、止まりません。 互いを親として登録した二つのカテゴ
リーなど、循環のあるデータでは、UNION
ALLを使う再帰CTEが、サーバーの許す限り巡回し続けます。UNIONが役立つ
のは、行全体が重複する場合だけです。深さの列を加えると、その条件はほ
とんど満たされません。訪問済みのキーの経路を配列で追い、同じキーが再
び現れたら止めるか、PostgreSQL
14で追加したCYCLE句を使います。外側のクエリーのLIMITも、テスト用の工
夫にはなりますが、マニュアルは、本番で頼るべきではないとしています。
SQL
Serverは、既定で再帰を100階層までに制限し、超えると文を取り消しま
す。同じバグでも、動き続けずに止まります。

文の残りから、変更が見えません。
データを変更するCTEと主クエリーは、同じスナップショット上で並行して
実行します。そのため、対象テーブルを直接読む主クエリーには、変更前の
行が見えます。両者の間で情報を渡す唯一の経路は、RETURNINGです。一つ
の文の二つの部分が、同じ行を変更する場合は、さらに問題になります。有
効になる変更は一つだけで、どちらになるかは予測しにくいためです。変更
した行は、CTEのRETURNINGの出力から読み、一つの文の中では、各行を書き
込む部分を一つに限ります。

アップグレードで、実行計画が変わります。 PostgreSQL
11以前に書いたコードは、CTEが先に評価されることを、望ましくない計画
への意図的な障壁として使っている場合があります。12以降へ更新すると、
同じCTEを外側のクエリーへ組み込み、プランナーが別の計画を選びます。
通常は速くなりますが、ときには大幅に遅くなります。遅い文のEXPLAIN出
力を、更新前後で比較し、障壁を意図した箇所にはMATERIALIZEDを加えま
す。

参考資料

  * 7.8. WITHクエリー（共通テーブル式） —
CTEのマニュアルの章です。再帰、循環の検出、実体化、データを変更する
文を説明します。

  * E.23. リリース12 — 一度だけ使うCTEを、外側のクエリーへ組み込むよ
うになったリリースです。

  * E.25. リリース14 — SQL標準のSEARCH句とCYCLE句を追加したリリース
です。

  * WITH句 — SQLiteでの実装です。実体化のヒントと、その着想元も説明
します。

  * WITH common_table_expression（Transact-SQL） — SQL
ServerのCTEの規則です。再帰のMAXRECURSION上限も含みます。

hこのページのウェブ版	URL:https://engineer.company/ja/glossary/cte/	engineer.company	70
