PostgreSQLのCTEが現場で少ない理由を実務経験から考える

はじめに

バッチ処理で大量のデータ変換を行う際、PostgreSQLのCTE（Common Table Expression、WITH句）を多用していた時期がありました。複雑な変換処理を段階的に分割できて、コードの見通しも良くなる便利な機能です。

しかし、実際の現場でCTEを使っているコードは意外と少ない。サブクエリや一時テーブルが使われているケースの方が圧倒的に多い印象です。

この記事では、実務でCTEを使って感じた強み・弱みと、「なぜ現場ではCTEが少ないのか」を考察します。特にPostgreSQL 12で大きく改善された最適化の仕組みについても解説します。

CTEの基本おさらい

CTEはWITH句を使って一時的な結果セットを定義し、メインクエリから参照できる機能です。

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 10000;

サブクエリと似ていますが、名前をつけて再利用できる点が特徴です。変数のように扱えて、複雑なクエリを段階的に構築できます。

CTEの強みと弱み

強み

1. 可読性・保守性の向上

ネストしたサブクエリ地獄を回避できます。処理を論理的なステップに分割して、各ステップに名前をつけられるため、コードレビューやメンテナンスが格段に楽になります。

2. 再帰クエリが書ける

WITH RECURSIVEを使えば、階層構造（組織図、カテゴリツリー）を扱えます。これはCTE独自の強みで、サブクエリでは実現できません。

3. 複数箇所から参照できる

同じCTEを複数回参照できます（ただし最適化の観点で注意が必要、後述）。サブクエリだと同じ処理を重複して書く必要があります。

4. デバッグしやすい

各CTEを個別に実行して中間結果を確認できます。サブクエリだと抜き出して実行するのが面倒です。

5. 変換処理の分離

SELECT句での複雑な計算を先にCTEで処理しておけます。WHERE句で使いたいけど計算が複雑な場合に便利です。

弱み

1. 親クエリのパラメータを参照できない

サブクエリなら外側の列を参照できる（相関サブクエリ）のに対し、CTEは独立しているため参照できません。

-- サブクエリなら可能
SELECT *
FROM orders o
WHERE amount > (SELECT AVG(amount) FROM orders WHERE region = o.region);

-- CTEでは不可能（外側のo.regionを参照できない）

2. 大量データ・長時間処理には不向き

メモリ上に保持されるため、巨大データだと辛い。一時テーブルならインデックスを作成したり統計情報を活用できます。

実際、バッチ処理で数百万行のデータを扱う際、CTEよりも一時テーブルの方がパフォーマンスが良いケースが多かったです。

3. PostgreSQL 11以前は「最適化バリア」になる

これが最大の問題でした。次のセクションで詳しく解説します。

PostgreSQL 11以前の「最適化バリア」問題

PostgreSQL 11以前では、CTEを使うと必ずマテリアライズ（結果の実体化）が発生しました。

参考: CTE(With句) vs View in Postgres

何が問題だったのか

-- huge_tableに (col, id) の複合インデックスがあるとして
WITH cte AS (
  SELECT * FROM huge_table WHERE col < 100
)
SELECT * FROM cte WHERE id = 1;

PostgreSQL 11以前の挙動:

1. WHERE col < 100でインデックスは使える
2. しかしその結果がマテリアライズされた時点で「ただのデータ」になる
3. 次のWHERE id = 1は、マテリアライズされた結果に対するフィルタ
4. 元テーブルの複合インデックス(col, id)が活かせない！

これが「最適化バリア」です。外側のWHERE条件が元のテーブルにプッシュダウンされず、複合インデックスが効かなくなります。

理想的には以下のように最適化されるべきですが、PostgreSQL 11以前ではこれができませんでした：

-- こう最適化されるべき
SELECT * FROM huge_table WHERE col < 100 AND id = 1;
-- 複合インデックス (col, id) がバッチリ効く

即座評価 vs 遅延評価

PostgreSQL 11以前のCTEは即座評価（eager evaluation）でした。CTEを定義した時点で結果を計算して保持します。変数に代入するイメージです。

一方、サブクエリは遅延評価（lazy evaluation）で、外側の条件と統合して最適化できました。この違いが、「CTEは遅い」という評判の原因でした。

PostgreSQL 12以降の進化

PostgreSQL 12（2019年10月リリース）で、CTEの挙動が大きく改善されました。

参考: PostgreSQL 12以降のCTE最適化について

デフォルトで遅延評価に

PostgreSQL 12以降では、デフォルトでNOT MATERIALIZED（遅延評価）になりました。つまり、サブクエリのようにインライン展開されて最適化されます。

WITH cte AS (
  SELECT * FROM huge_table WHERE col < 100
)
SELECT * FROM cte WHERE id = 1;

-- PostgreSQL 12以降は自動的にこう最適化される
SELECT * FROM huge_table WHERE col < 100 AND id = 1;
-- 複合インデックスが効く！

賢い自動判断

オプティマイザが状況に応じて自動的にマテリアライズの要否を判断します。

マテリアライズ「しない」条件:

1. 同じCTEが1回しか使われていない
2. 非immutable関数が使われていない

逆に言えば、以下の場合は自動的にマテリアライズされます：

複数回参照される場合

WITH cte AS (
  SELECT expensive_calculation(id) FROM huge_table WHERE col < 100
)
SELECT * FROM cte WHERE id = 1
UNION ALL
SELECT * FROM cte WHERE id = 2
UNION ALL
SELECT * FROM cte WHERE id = 3;

同じ重い計算を3回やるより、1回計算して使い回す方が効率的です。オプティマイザが賢く判断してマテリアライズしてくれます。

非immutable関数がある場合

WITH cte AS (
  SELECT *, now() AS created_at FROM huge_table
)
SELECT * FROM cte WHERE id = 1
UNION ALL
SELECT * FROM cte WHERE id = 2;

now()のような非immutable関数（呼び出すたびに結果が変わる可能性がある関数）を含む場合、必ずマテリアライズされます。

明示的な指定も可能

必要に応じてMATERIALIZED/NOT MATERIALIZEDを明示的に指定できます。

-- 明示的にマテリアライズ（PostgreSQL 11以前の挙動）
WITH cte AS MATERIALIZED (
  SELECT * FROM huge_table WHERE col < 100
)
SELECT * FROM cte WHERE id = 1;

-- 明示的に遅延評価（複数回参照でもインライン展開）
WITH cte AS NOT MATERIALIZED (
  SELECT * FROM huge_table WHERE col < 100
)
SELECT * FROM cte WHERE id = 1
UNION ALL
SELECT * FROM cte WHERE id = 2;

実務での使い分け

PostgreSQL 12以降の改善で、CTEの性能問題は大幅に解決されました。それでも、適材適所は存在します。

CTEを使うべき場合

中間的な変換処理 一時テーブルを作るほどではないが、複雑な変換を分割したい場合。データの抽出を先にしておきたいが、一時テーブルにするほどではないケース。

WITH cleaned_data AS (
  SELECT 
    id,
    CASE WHEN status = 'draft' THEN 'pending' ELSE status END AS normalized_status,
    COALESCE(amount, 0) AS amount
  FROM raw_orders
),
filtered_data AS (
  SELECT * FROM cleaned_data WHERE normalized_status = 'pending'
)
SELECT * FROM filtered_data WHERE amount > 1000;

再帰クエリ 階層構造を扱う場合、CTEの独壇場です。

複雑なクエリの可読性向上 処理を論理的なステップに分割することで、コードレビューやメンテナンスが楽になります。

サブクエリの方が良い場合

親のパラメータを参照したい 相関サブクエリが必要な場合は、CTEでは実現できません。

SELECT *
FROM orders o
WHERE amount > (
  SELECT AVG(amount) 
  FROM orders 
  WHERE region = o.region  -- 外側のo.regionを参照
);

単純な絞り込み 単純なフィルタリングなら、わざわざCTEを使う必要はありません。

一時テーブルの方が良い場合

大量データ・長時間処理 数百万行以上のデータを扱う場合、一時テーブルの方が安定します。

インデックスを張りたい 一時テーブルならインデックスを作成して、後続の処理を高速化できます。

CREATE TEMP TABLE tmp_orders AS
SELECT * FROM orders WHERE created_at > '2025-01-01';

CREATE INDEX idx_tmp_orders_region ON tmp_orders(region);

-- 後続処理でインデックスが効く
SELECT * FROM tmp_orders WHERE region = 'Asia';

複数回の参照で異なる条件が必要 CTEだとマテリアライズされて最適化の余地がなくなる場合、一時テーブルの方が柔軟です。

統計情報を活用したい 一時テーブルならANALYZEで統計情報を収集して、より良い実行計画を立てられます。

まとめ

CTEは便利な機能ですが、PostgreSQL 11以前の「最適化バリア」問題が、「CTEは遅い」という評判を生んだ一因でしょう。現場でCTEが少ないのは、この過去の評判を引きずっている可能性があります。

PostgreSQL 12以降（2019年10月〜）では、遅延評価がデフォルトになり、オプティマイザが賢く判断してくれるようになりました。複合インデックスも効くようになり、性能問題は大幅に改善されています。

ただし、それでも適材適所は存在します：

• CTE: 可読性重視、中間的な変換処理、再帰クエリ
• サブクエリ: 親パラメータの参照、単純なフィルタ
• 一時テーブル: 大量データ、インデックス活用、複雑な後続処理

結局のところ、PostgreSQL 12以降なら性能面での差は小さくなったので、好みと場面次第という側面が強くなりました。ただし、PostgreSQL 11以前の環境や、数百万行を超える大規模バッチ処理では、まだ注意が必要です。

個人的には、CTEを使う場面は増えましたが、「本当に重い処理」では今でも一時テーブルを使っています。厳密にやるなら実行計画（EXPLAIN ANALYZE）を確認するのがベストですが、多くの場合は直感と経験で判断しても問題ないでしょう。

参考リンク

• CTE(With句) vs View in Postgres - .NETで作る！
• PostgreSQL 12以降のCTE最適化について - SRA OSS Tech Blog