WITH による共通テーブル式と再帰クエリ

概要

WITH name AS (SELECT ...) は、主クエリ向けの一時的な名前付き結果を定義します。一つの文で複数を定義でき、それぞれが先に定義されたものを参照できます。PostgreSQL のドキュメントは、再帰形式を次のように説明しています。WITH RECURSIVE t AS (non-recursive term UNION [ALL] recursive term) は非再帰項を評価し、その行を作業テーブルに入れます。その後、自己参照の代わりに作業テーブルを使って再帰項を繰り返し評価し、行が返らなくなったところで終了します。UNION は重複行を除き、UNION ALL は残します。同じページには、出力順序を指定する SEARCH DEPTH FIRST | BREADTH FIRST BY ... SET col と、循環を検出して停止する CYCLE col SET is_cycle USING path も説明されています。また、一度だけ参照される非再帰かつ副作用のない WITH クエリは、デフォルトで親クエリに統合されます。MATERIALIZED は別個の評価を、NOT MATERIALIZED は統合を強制します。

なぜ重要か

カテゴリ、組織図、スレッド形式のコメント、部品構成の展開といった階層は、固定回数の結合では表せません。再帰 CTE は任意の深さを一つの文で扱えます。非再帰 CTE は長いクエリを名前付きの手順に分け、個別に読んだりテストしたりできるようにします。

適用方法

• 祖先をたどる場合は、対象ノード（SELECT id, parent_id, 1 AS depth FROM nodes WHERE id = $1）から始め、再帰項を parent_id で結合します。子孫をたどる場合は逆方向です。depth を引き継ぎ、上限を設けて（WHERE depth < 50）、予期しない循環への備えにします。
• 循環を含む可能性があるグラフには、UNION または CYCLE 句を使います。循環に対する UNION ALL は、上限がなければ終了しません。
• 木を入れ子の順序で表示するには、SEARCH DEPTH FIRST BY name SET ord と ORDER BY ord を組み合わせます。
• データ変更を行う CTE は、一つの文で行を移動できます。WITH moved AS (DELETE FROM live WHERE ... RETURNING *) INSERT INTO archive SELECT * FROM moved。
• 複数回参照する CTE はデフォルトでマテリアライズされます。一度だけ参照する CTE に MATERIALIZED を付けるのは、高コストの関数を一度だけ計算するなど、別個の評価を強制したい場合に限ります。複数回参照する CTE でも、それぞれの利用がインデックスを使いやすい小さな部分だけを必要とする場合は、NOT MATERIALIZED を指定します。ドキュメントの big_table の例がこれに当たります。

注意点

マテリアライズされた CTE は記述どおりに評価されるため、外側の WHERE を内部に押し込めず、基になるテーブルのインデックスは外側の述語に使われません。再帰項が生成した各行は結果に残るので、大きな木で幅の広い行を扱うとコストが高くなります。ドキュメントは、幅優先の出力順序は依存すべきでない実装上の詳細であり、同じ階層内の順序は未定義だとしています。SEARCH または明示的な ORDER BY を使ってください。