PostgreSQLのCTE（WITH句）の使い方｜再帰クエリ・更新系・MATERIALIZEDまで

PostgreSQL の CTE（共通テーブル式、WITH 句）は、クエリの途中結果に名前を付けて、後続の SELECT から表のように参照できる書き方です。入れ子のサブクエリを上から順に読める形へ整理できるほか、WITH RECURSIVE による階層データのたどり方や、DELETE … RETURNING と組み合わせた「移動」処理など、CTE でなければ書きにくい処理もあります。この記事では、基本形、再帰 CTE、更新系の CTE、そして性能に関わる MATERIALIZED 指定までを、つまずきやすい点と合わせて説明します。

WordPress 本体は MySQL / MariaDB を前提としており、PostgreSQL は公式にはサポートされていません（2026年10月現在）。ここでは WordPress と並べて運用する分析用データベースや、別システムで PostgreSQL を使う場面を想定しています。MySQL も 8.0 以降で WITH 句と再帰 CTE に対応していますが、MATERIALIZED 指定や更新系の CTE など、この記事で扱う一部の機能は PostgreSQL 固有です。

基本形：途中結果に名前を付ける

次の例は、直近30日の注文を顧客ごとに集計し、その中から合計金額の大きい顧客を取り出すものです。orders(id, customer_id, ordered_at, amount) と customers(id, name) というテーブルを前提にしています。

WITH recent AS (
  SELECT customer_id, sum(amount) AS total, count(*) AS cnt
  FROM orders
  WHERE ordered_at >= now() - interval '30 days'
  GROUP BY customer_id
),
top_customers AS (
  SELECT * FROM recent WHERE total >= 50000
)
SELECT c.name, t.total, t.cnt
FROM top_customers t
JOIN customers c ON c.id = t.customer_id
ORDER BY t.total DESC;

ポイントは三つです。

• 複数の CTE はカンマで区切って並べ、後の CTE から前の CTE を参照できます（この例では top_customers が recent を使っています）。
• CTE の名前は、その WITH を含む一つの文の中でだけ有効です。一時テーブルのように後の文から使うことはできません。
• 処理の段階ごとに名前が付くので、レビューやデバッグのときに「どの段階で行数がおかしくなったか」を切り分けやすくなります。途中の CTE だけを SELECT * FROM recent のように取り出して確かめる、という使い方ができます。

同じ結果はサブクエリでも書けます。CTE を使う主な理由は読みやすさで、性能は基本的に変わりません（例外は後述の MATERIALIZED の項で説明します）。

再帰CTE：階層データをたどる

WITH RECURSIVE を使うと、親子関係を持つデータを深さを問わずにたどれます。カテゴリーの木構造を例にします。

-- categories(id, parent_id, name)  parent_id が NULL なら最上位
WITH RECURSIVE tree AS (
  -- 起点（非再帰部分）
  SELECT id, parent_id, name, 1 AS depth, name::text AS path
  FROM categories
  WHERE parent_id IS NULL

  UNION ALL

  -- 再帰部分：直前に得た行の子を探す
  SELECT c.id, c.parent_id, c.name, t.depth + 1, t.path || ' > ' || c.name
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
  WHERE t.depth < 20
)
SELECT * FROM tree ORDER BY path;

再帰 CTE は「起点の行を作る」→「直前に得た行を使って次の行を作る」を、新しい行が出なくなるまで繰り返します。書くときの注意点は次のとおりです。

• 起点と再帰部分は UNION ALL（または UNION）でつなぎます。UNION は重複行を取り除くため、同じ行に戻ってくるような単純な循環なら止まりますが、depth や path のように行ごとに値が変わる列があると重複にならず止まりません。
• データに循環（Aの親がB、Bの親がA）があると、終わらないクエリになります。上の例の WHERE t.depth < 20 のように深さの上限を入れておくと、想定外のデータでも暴走を防げます。
• 列の型は起点側で決まります。path を name::text としているのは、再帰部分で文字列を連結したときに型が合わずエラーになるのを避けるためです。

CYCLE句で循環を検出する（PostgreSQL 14以降）

PostgreSQL 14 以降では、標準 SQL の CYCLE 句で循環を検出できます。

WITH RECURSIVE tree AS (
  SELECT id, parent_id, name FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.parent_id, c.name
  FROM categories c JOIN tree t ON c.parent_id = t.id
) CYCLE id SET is_cycle USING visited
SELECT * FROM tree WHERE NOT is_cycle;

CYCLE id は「id が同じ経路の中で再び現れたら循環とみなす」という意味で、その行の is_cycle が true になり、そこから先の再帰は止まります。古いバージョンでは、経路を配列に積み上げて NOT c.id = ANY(t.visited) のように自分で判定します。使える構文はバージョンで違うため、本番の PostgreSQL のバージョンを SELECT version(); で確かめてから選んでください。

更新系のCTE：消して移す処理を1文で

PostgreSQL では、WITH の中に INSERT・UPDATE・DELETE を書き、RETURNING で返した行を後続の処理に渡せます。典型例は、古い行をアーカイブ用のテーブルへ移す処理です。

WITH moved AS (
  DELETE FROM orders
  WHERE ordered_at < now() - interval '3 years'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

削除と挿入が一つの文になるため、両方が同じトランザクションで実行され、途中で失敗すれば両方とも取り消されます。ただし、仕様上の注意があります。

• WITH 内の更新系の文と本体の文は、同じスナップショットで同時に実行されます。本体の SELECT から対象テーブルを読んでも、CTE で行った変更は見えません。変更後の値は RETURNING の出力から受け取ります。
• 更新系の CTE は、本体から参照されなくても1回だけ実行されます。「使っていないから実行されないだろう」と考えて残しておくと、意図しない更新が走ります。
• 同じ行を一つの文の中で2回更新するような書き方は、結果が予測しにくいため避けます。

大量の行を一度に動かすと、ロックの時間や WAL（変更ログ）の量が増え、レプリカの遅延にもつながります。件数が多い場合は WHERE id IN (SELECT id FROM orders WHERE … LIMIT 10000) のように小分けにして繰り返す方法が安全です。実行前にバックアップを取り、検証環境で件数と所要時間を確認してください。

MATERIALIZEDとNOT MATERIALIZED：性能への影響

CTE の性能でよく話題になるのが「最適化の壁」です。PostgreSQL 11 までは、CTE はいったん結果をすべて作ってから本体で使う方式だったため、本体側の WHERE 条件が CTE の中に伝わらず、インデックスが使われないことがありました。

PostgreSQL 12 以降は、副作用が無く（更新系でなく）、再帰でもなく、本体から1回だけ参照される CTE は、自動的にサブクエリと同じように展開（インライン化）されます。2回以上参照される CTE は、従来どおり一度だけ計算して結果を使い回します。この動きは明示的に指定することもできます。

-- 重い集計を1回だけ計算させたい
WITH stats AS MATERIALIZED (
  SELECT customer_id, avg(amount) AS avg_amount FROM orders GROUP BY customer_id
)
SELECT ... ;

-- 2回参照していても、条件を中に押し込んでインデックスを使わせたい
WITH recent AS NOT MATERIALIZED (
  SELECT * FROM orders WHERE ordered_at >= now() - interval '7 days'
)
SELECT ... ;

どちらが速いかはデータ量と条件次第なので、EXPLAIN (ANALYZE, BUFFERS) で比べて決めます。実行計画に CTE Scan が出ていれば結果を作ってから使っている状態、出ていなければ展開されている状態です。古いバージョンから移行したクエリが、インライン化によって速くなることもあれば、逆に重い計算が繰り返されて遅くなることもあります。メジャーバージョンを上げた後は、主要なクエリの計画を見直しておくと安心です。

まとめ

• CTE は途中結果に名前を付ける書き方で、主な効果は読みやすさとデバッグのしやすさです。名前はその文の中だけで有効です。
• WITH RECURSIVE で階層データをたどれます。深さの上限や CYCLE 句（14以降）で循環に備えます。
• 更新系の CTE は削除と挿入を1文で行えますが、同じスナップショットで動くこと、参照されなくても実行されることに注意します。
• 12 以降は単純な CTE が自動で展開されます。性能が気になるときは MATERIALIZED / NOT MATERIALIZED を EXPLAIN で比べて選びます。

• PostgreSQLのウィンドウ関数の使い方
• WordPressのMySQLクエリをEXPLAINで分析して最適化する方法