PostgreSQL WITH句の代替手段：サブクエリ、一時テーブル、ビュー徹底比較

PostgreSQLにおけるWITH句（Common Table Expression、略してCTE）は、複雑なクエリを読みやすく、管理しやすくするために一時的な名前付き結果セットを定義する機能です。このCTEの動作には、「マテリアライズ（Materialization）」という重要な概念が関わってきます。

WITH句（CTE）とは？

WITH句は、メインクエリの前に、独立したサブクエリに名前を付けて定義するものです。これにより、その名前をメインクエリや他のCTEの中で、あたかもテーブルのように参照できます。

基本的な構文

WITH cte_name AS (
    -- CTEの定義クエリ
    SELECT column1, column2 FROM table_a WHERE condition;
)
SELECT * FROM cte_name WHERE another_condition;

マテリアライズとは？

マテリアライズとは、CTEで定義されたクエリの結果が、実際のデータとして一時的にメモリ上やディスク上に計算・保存されることを指します。これにより、メインクエリでそのCTEが複数回参照された場合でも、その都度クエリを再実行するのではなく、保存された結果を再利用できます。

PostgreSQLにおけるマテリアライズの挙動

PostgreSQLは、デフォルトではCTEをマテリアライズしようとします。つまり、CTEのクエリを一度実行し、その結果を一時的なデータとして保持します。この動作には、メリットとデメリットがあります。

マテリアライズのメリット

• 副作用のあるクエリの安定性
  INSERT, UPDATE, DELETEなどの副作用を伴うCTEを使用する場合、マテリアライズによってその副作用が一度だけ実行されることが保証されます。
• クエリプランの安定性
  マテリアライズされることで、CTEの結果が確定するため、オプティマイザがその結果を基に効率的なクエリプランを立てやすくなります。
• 複数回参照されるCTEのパフォーマンス向上
  もし同じCTEがメインクエリ内で複数回参照される場合、一度計算してマテリアライズしておけば、その後の参照では再計算が不要になり、全体のクエリ実行時間を短縮できます。特に、CTEのクエリが非常に複雑でコストが高い場合に効果的です。

マテリアライズのデメリット

• 不要な計算とストレージ消費
  もしCTEの結果が非常に大きく、かつメインクエリで一度しか参照されない場合、マテリアライズは不要な計算とメモリ/ディスク消費を引き起こす可能性があります。本来であれば、CTEをメインクエリにインライン展開（サブクエリとして直接組み込む）した方が、オプティマイザが全体のクエリを最適化しやすくなることがあります。

マテリアライズの制御（PostgreSQL 12以降）

PostgreSQL 12からは、CTEのマテリアライズを明示的に制御するためのMATERIALIZEDとNOT MATERIALIZEDキーワードが導入されました。

• NOT MATERIALIZED
  CTEの結果をマテリアライズしないよう指示します。PostgreSQLのオプティマイザは、CTEをメインクエリにインライン展開する（つまり、サブクエリのように扱う）ことを検討します。これにより、全体のクエリをより広範囲に最適化できる可能性があります。

  WITH my_cte AS NOT MATERIALIZED (
      SELECT column1, column2 FROM table_a WHERE condition;
  )
  SELECT * FROM my_cte;
  

  これは、CTEが一度しか使用されない、または非常に単純な場合に、不要なマテリアライズを避けて最適化を促進したいときに有用です。

• MATERIALIZED
  CTEの結果を明示的にマテリアライズするよう指示します。

  WITH my_cte AS MATERIALIZED (
      SELECT column1, column2 FROM table_a WHERE condition;
  )
  SELECT * FROM my_cte;
  

  これは、CTEが複数回使用される、または計算コストが高い場合に、明示的に性能を改善したいときに有用です。

---

CTEはクエリの可読性を高め、複雑なロジックを整理するのに役立ちますが、マテリアライズの挙動が原因で予期せぬパフォーマンス問題を引き起こすことがあります。

パフォーマンスの低下

よくある問題
CTEを使用すると、サブクエリとして直接記述した場合よりもクエリが遅くなることがあります。特に、CTEのデータ量が非常に大きい場合や、CTEの結果がメインクエリで一度しか使われない場合に顕著です。これは、PostgreSQLがCTEをデフォルトでマテリアライズしようとするため、不必要な一時テーブルの作成とデータ書き込みが発生するためです。

原因

• オプティマイザの「最適化の壁（Optimization Fence）」
  マテリアライズされたCTEは、その内部の処理とメインクエリの処理が分断されるため、オプティマイザが全体を統合的に最適化（例えば、フィルター条件のプッシュダウンなど）しにくくなる。
• 不必要なマテリアライズ
  オプティマイザがCTEをマテリアライズすべきではないと判断できず、一時テーブルを作成してデータ全体をキャッシュしてしまう。これにより、インデックスが利用されにくくなったり、余分なI/Oが発生したりする。

トラブルシューティング

• 統計情報の更新
  テーブルの統計情報が古くなっていると、オプティマイザが不適切な実行計画を立てることがあります。ANALYZEコマンドを実行して統計情報を最新に保ちます。
• CTE内部でのインデックス利用
  CTE内部のクエリが大量のデータを処理する場合、そのクエリが適切なインデックスを使用しているか確認します。マテリアライズされる場合でも、CTEの内部処理が効率的であることは重要です。
• CTEをサブクエリに書き換え
  NOT MATERIALIZEDが効かない場合や、古いPostgreSQLバージョンを使用している場合は、CTEを通常のサブクエリ（派生テーブル）として書き換えることで、マテリアライズを回避できることがあります。

  SELECT * FROM (
      SELECT column1, column2 FROM large_table WHERE some_condition
  ) AS subquery_name
  WHERE another_condition;
  

  ただし、これによりクエリの可読性が低下する可能性があるため、バランスを考慮する必要があります。
• NOT MATERIALIZEDの試行 (PostgreSQL 12以降)
  CTEが一度しか参照されず、マテリアライズが不要と思われる場合は、NOT MATERIALIZEDキーワードを明示的に指定して、マテリアライズを抑制します。これにより、オプティマイザがCTEの内容をメインクエリにインライン展開し、より広範な最適化を適用できる可能性があります。

  WITH my_cte AS NOT MATERIALIZED (
      SELECT column1, column2 FROM large_table WHERE some_condition;
  )
  SELECT * FROM my_cte WHERE another_condition;
  

  これで改善しない場合や、複雑なCTEの場合は、MATERIALIZEDを指定してみることも検討します。
• EXPLAIN ANALYZEの活用
  まずはEXPLAIN ANALYZEコマンドを使って、クエリの実行計画を確認します。CTEがマテリアライズされている場合、その部分に「CTE Scan on cte_name」のような表示とともに、実行時間や行数が示されます。
  • EXPLAIN ANALYZE WITH my_cte AS (SELECT ... FROM ...) SELECT * FROM my_cte WHERE ...;

メモリ/ディスク使用量の増加

よくある問題
非常に大きな結果セットを返すCTEがマテリアライズされると、一時的に大量のメモリやディスク領域を消費し、システムのボトルネックになることがあります。

原因

• 不要なカラムの選択
  CTE内で必要のないカラムまで選択している場合、結果セットが不必要に大きくなる。
• 巨大なCTE結果
  マテリアライズされるデータが、PostgreSQLの作業メモリ (work_mem) に収まらないほど大きい場合、ディスクへのスピル（一時ファイルの書き込み）が発生し、I/Oが増大する。

トラブルシューティング

• CTE内のフィルターの強化
  CTE内でできるだけ早期にデータを絞り込むように、WHERE句などを活用します。これにより、マテリアライズされるデータ量を減らすことができます。
• CTEで必要なカラムのみを選択
  CTEの定義内で、メインクエリで実際に必要となるカラムのみを選択するようにします。不要なカラムを含めると、一時テーブルのサイズが増加し、処理コストも増大します。
• work_memの調整
  一時ソートやハッシュなどの操作で使用されるメモリ量を示すwork_memパラメータが小さすぎる場合、ディスクへのスピルが発生しやすくなります。ただし、この値を大きくしすぎると、同時に実行される多数のクエリがそれぞれ大量のメモリを消費し、システム全体のメモリ不足を引き起こす可能性があるため、慎重に調整する必要があります。

再帰CTEにおける無限ループ

よくある問題
再帰CTE（WITH RECURSIVE）を使用している場合、停止条件が正しく設定されていないと、無限ループに陥り、クエリが終了しなくなったり、システムリソースを大量に消費したりします。

原因

• 循環参照
  データに循環参照が含まれており、再帰クエリがそれを延々と辿ってしまう。
• 停止条件の欠如
  再帰クエリのベースケース（非再帰部分）または再帰部分の終了条件が不足している。

トラブルシューティング

• max_recursion_depth (PostgreSQL 14以降)
  PostgreSQL 14からは、再帰CTEの最大深さを制限するmax_recursion_depthという設定が追加されました。これにより、無限ループが発生した場合でも、システムリソースの枯渇を防ぐことができます。
• パスのトラッキング
  pathやlevelのようなカラムを追加して、再帰の深さや辿ったパスを記録することで、無限ループの原因を特定しやすくなります。
• UNION vs UNION ALL
  再帰CTEでUNIONを使用すると、重複行が排除されるため、無限ループを避けるのに役立つ場合があります。ただし、パフォーマンスが低下する可能性もあるため、データの特性と要件に応じて選択します。UNION ALLは重複を排除しないため、パフォーマンスは良いですが、無限ループのリスクが高まります。
• 停止条件の確認
  再帰CTEには、必ず再帰が終了する条件をWHERE句などで明示的に指定する必要があります。

WITH句のスコープと参照に関するエラー

よくある問題
CTEの名前の参照ミスや、CTEが定義されたスコープ外から参照しようとする。

原因

• スコープ外からの参照
  CTEは、それを定義したクエリのスコープ内でのみ有効です。別のクエリブロックやセッションからは参照できません。
• スペルミス
  CTEの名前を間違って参照している。

• スコープの理解
  CTEが一時的なものであり、その定義されたクエリ文全体でのみ利用可能であることを理解する。もし複数のクエリで同じロジックを再利用したい場合は、ビューや関数として定義することを検討します。
• 名前の正確な確認
  CTEの名前は、定義時と参照時で完全に一致している必要があります（大文字・小文字も含む）。

---

ここでは、CTEの基本的な使い方から、MATERIALIZEDとNOT MATERIALIZEDキーワードを使ったマテリアライズの制御まで、具体的なコード例を挙げて説明します。

まず、例で使用するための簡単なテーブルとデータを準備します。

-- 準備: 商品テーブル
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT NOT NULL
);

-- 準備: サンプルデータ
INSERT INTO products (product_name, category, price, stock_quantity) VALUES
('Laptop', 'Electronics', 1200.00, 50),
('Smartphone', 'Electronics', 800.00, 120),
('Headphones', 'Electronics', 150.00, 200),
('Desk Chair', 'Furniture', 250.00, 80),
('Dining Table', 'Furniture', 600.00, 30),
('Coffee Maker', 'Appliances', 100.00, 150),
('Blender', 'Appliances', 70.00, 90),
('Novel Book', 'Books', 20.00, 500),
('Textbook', 'Books', 80.00, 100),
('Keyboard', 'Electronics', 75.00, 180);

-- 準備: 販売履歴テーブル
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    product_id INT REFERENCES products(product_id),
    sale_date DATE NOT NULL,
    quantity_sold INT NOT NULL,
    total_price DECIMAL(10, 2) NOT NULL
);

-- 準備: サンプル販売データ
INSERT INTO sales (product_id, sale_date, quantity_sold, total_price) VALUES
(1, '2023-01-15', 2, 2400.00),
(2, '2023-01-16', 3, 2400.00),
(3, '2023-01-17', 5, 750.00),
(4, '2023-01-18', 1, 250.00),
(1, '2023-02-01', 1, 1200.00),
(2, '2023-02-05', 2, 1600.00),
(5, '2023-02-10', 1, 600.00),
(8, '2023-03-01', 10, 200.00),
(9, '2023-03-02', 2, 160.00);

CTEの基本的な使用例

最も一般的なCTEの使い方です。この場合、PostgreSQLはCTEhigh_value_productsをデフォルトでマテリアライズしようとします。

WITH high_value_products AS (
    SELECT
        product_id,
        product_name,
        price
    FROM
        products
    WHERE
        price >= 100.00
)
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    high_value_products p
JOIN
    sales s ON p.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2023-02-01' AND '2023-02-28'
ORDER BY
    s.sale_date;

EXPLAIN ANALYZEでの確認

上記のクエリの前にEXPLAIN ANALYZEを付けると、実行計画に「CTE Scan on high_value_products」といった行が表示され、CTEが一時的にマテリアライズされていることが確認できます。

EXPLAIN ANALYZE
WITH high_value_products AS (
    SELECT
        product_id,
        product_name,
        price
    FROM
        products
    WHERE
        price >= 100.00
)
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    high_value_products p
JOIN
    sales s ON p.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2023-02-01' AND '2023-02-28'
ORDER BY
    s.sale_date;

MATERIALIZEDキーワードを使った明示的なマテリアライズ

CTEの結果を必ずマテリアライズしたい場合にMATERIALIZEDキーワードを使用します。これは、CTEが複数回参照される場合や、CTEの計算コストが高く、一度だけ計算して再利用したい場合に有効です。

-- カテゴリ別の平均価格を計算し、それを複数回参照する
WITH category_avg_price AS MATERIALIZED (
    SELECT
        category,
        AVG(price) AS avg_price_per_category
    FROM
        products
    GROUP BY
        category
)
SELECT
    p.product_name,
    p.category,
    p.price,
    cap.avg_price_per_category
FROM
    products p
JOIN
    category_avg_price cap ON p.category = cap.category
WHERE
    p.price > cap.avg_price_per_category -- 平均価格より高い商品
ORDER BY
    p.category, p.price DESC;

この例では、category_avg_priceが一度計算され、その結果がproductsテーブルとの結合で再利用されます。EXPLAIN ANALYZEで見ると、CTE Scan on category_avg_priceが明確に表示されます。

NOT MATERIALIZEDキーワードを使ったマテリアライズの抑制

CTEの結果をマテリアライズしてほしくない場合にNOT MATERIALIZEDキーワードを使用します。これは、CTEが一度しか参照されず、かつその内部のクエリが非常にシンプルで、メインクエリと統合して最適化してほしい場合に有効です。

-- 在庫が少ない商品リストをCTEで作成し、一度だけ参照
WITH low_stock_products AS NOT MATERIALIZED (
    SELECT
        product_id,
        product_name,
        stock_quantity
    FROM
        products
    WHERE
        stock_quantity < 100
)
SELECT
    lsp.product_name,
    lsp.stock_quantity,
    s.sale_date,
    s.quantity_sold
FROM
    low_stock_products lsp
LEFT JOIN
    sales s ON lsp.product_id = s.product_id
WHERE
    s.sale_date >= '2023-02-01' OR s.sale_id IS NULL -- 在庫が少ない商品の販売履歴（2023-02-01以降）または販売履歴がないもの
ORDER BY
    lsp.product_name;

このクエリのEXPLAIN ANALYZEを見ると、low_stock_productsに対する明示的な「CTE Scan」が消え、productsテーブルへの直接的なアクセスや、より統合された実行計画が表示される可能性が高くなります。これは、オプティマイザがCTEの内容をメインクエリに「インライン展開」したためです。

複数CTEの連携とマテリアライズの考慮

複数のCTEをチェーンすることも可能です。それぞれのCTEに対して、マテリアライズの挙動を考慮できます。

-- 1. カテゴリごとの総売上を計算するCTE (マテリアライズを明示)
WITH category_total_sales AS MATERIALIZED (
    SELECT
        p.category,
        SUM(s.total_price) AS total_sales_amount
    FROM
        sales s
    JOIN
        products p ON s.product_id = p.product_id
    GROUP BY
        p.category
),
-- 2. 在庫が少ない商品の情報を取得するCTE (マテリアライズを抑制)
low_stock_summary AS NOT MATERIALIZED (
    SELECT
        product_id,
        product_name,
        category,
        stock_quantity
    FROM
        products
    WHERE
        stock_quantity < 50
)
-- 最終的なクエリ
SELECT
    lss.product_name,
    lss.stock_quantity,
    lss.category,
    cts.total_sales_amount AS category_sales
FROM
    low_stock_summary lss
JOIN
    category_total_sales cts ON lss.category = cts.category
ORDER BY
    lss.category, lss.stock_quantity DESC;

この例では、category_total_salesは一度計算され、その結果がlow_stock_summaryとの結合で再利用されるように指示しています。一方、low_stock_summaryは直接productsテーブルにアクセスするような最適化が試みられます。

---

PostgreSQLのWITH句（CTE）は非常に便利ですが、そのマテリアライズの特性が常に最適なわけではありません。CTEの代替となるプログラミング手法を理解しておくことで、クエリのパフォーマンスを向上させたり、異なる状況に合わせた柔軟な設計が可能になります。

サブクエリ（派生テーブル）

最も直接的なCTEの代替手段です。CTEが導入される前から広く使われている手法で、クエリをインラインで記述します。

特徴

• 可読性
  複雑なサブクエリが何重にもネストされると、クエリの可読性が低下しやすいです。
• プッシュダウン最適化
  メインクエリのWHERE句などがサブクエリの内部に「プッシュダウン」され、より早期にデータを絞り込むことで、処理対象の行数を減らせる可能性があります。
• インライン展開
  オプティマイザは通常、サブクエリをメインクエリの一部としてインライン展開しようとします。これにより、マテリアライズが発生せず、全体のクエリを統合的に最適化する機会が増えます。

使用例

SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    ( -- high_value_products_subquery が派生テーブル
        SELECT
            product_id,
            product_name,
            price
        FROM
            products
        WHERE
            price >= 100.00
    ) AS high_value_products_subquery
JOIN
    sales s ON high_value_products_subquery.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2023-02-01' AND '2023-02-28'
ORDER BY
    s.sale_date;

CTEとの比較
CTEのNOT MATERIALIZEDと似た挙動を期待できます。CTEの方が名前付けがしやすく、複数のCTEをチェーンできるため、複雑なロジックの整理には優れます。しかし、単純なサブクエリで済む場合は、あえてCTEを使わない方がオプティマイザが最適なプランを選択しやすいことがあります。

ビュー（VIEW）

よく使う複雑なクエリや計算ロジックを、データベースオブジェクトとして永続的に保存したい場合に有効です。

特徴

• マテリアライズド・ビュー
  PostgreSQLにはマテリアライズド・ビューという機能もあり、これはビューの結果を物理的にディスクに保存し、定期的に更新（REFRESH MATERIALIZED VIEW）することで、高速なクエリを可能にします。通常のビューは参照時に毎回クエリが実行されますが、マテリアライズド・ビューは物理的にマテリアライズされるため、パフォーマンスが向上します。
• 論理的な抽象化
  基盤となるテーブル構造を隠蔽し、ユーザーやアプリケーションにシンプルなデータビューを提供できます。
• データベースオブジェクト
  一度定義すれば、テーブルのように繰り返し参照できます。

使用例（通常のビュー）

-- ビューの定義
CREATE VIEW high_value_products_view AS
SELECT
    product_id,
    product_name,
    price
FROM
    products
WHERE
    price >= 100.00;

-- ビューの使用
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    high_value_products_view p
JOIN
    sales s ON p.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2023-02-01' AND '2023-02-28'
ORDER BY
    s.sale_date;

使用例（マテリアライズド・ビュー）

-- マテリアライズド・ビューの定義
CREATE MATERIALIZED VIEW category_avg_price_mv AS
SELECT
    category,
    AVG(price) AS avg_price_per_category
FROM
    products
GROUP BY
    category
WITH DATA; -- データの初期ロード

-- マテリアライズド・ビューの使用
SELECT * FROM category_avg_price_mv;

-- データの更新（手動またはスケジューラで定期的に実行）
REFRESH MATERIALIZED VIEW category_avg_price_mv;

CTEとの比較
ビューはCTEのようにその場限りのものではなく、データベーススキーマの一部となります。特に、複数のクエリやアプリケーションで同じ複雑なロジックを共有したい場合に適しています。マテリアライズド・ビューは、CTEのMATERIALIZEDキーワードが持つ一時的なキャッシュよりも、より大規模かつ永続的なデータキャッシュを提供します。

一時テーブル（TEMPORARY TABLE）

クエリ実行中に一時的にデータを保存したい場合に作成するテーブルです。セッションの終了時に自動的に破棄されます。

特徴

• トランザクションスコープ
  通常、現在のセッションまたはトランザクションにスコープされます。
• 複数ステップの処理
  非常に複雑なクエリを複数のステップに分割し、中間結果を保存するのに適しています。
• インデックスの作成
  一時テーブルにインデックスを作成できるため、その後のクエリパフォーマンスを向上させられます。
• 明示的なマテリアライズ
  データが物理的にディスクまたはメモリに書き込まれます。

使用例

-- 一時テーブルの作成とデータの挿入
CREATE TEMPORARY TABLE temp_high_value_products AS
SELECT
    product_id,
    product_name,
    price
FROM
    products
WHERE
    price >= 100.00;

-- 必要であれば一時テーブルにインデックスを追加
CREATE INDEX ON temp_high_value_products (product_id);

-- 一時テーブルの使用
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    temp_high_value_products p
JOIN
    sales s ON p.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2023-02-01' AND '2023-02-28'
ORDER BY
    s.sale_date;

-- セッション終了時または明示的にDROP TABLE temp_high_value_products; で削除

CTEとの比較
一時テーブルは、CTEのMATERIALIZEDキーワードよりもさらに明示的に、かつ強力にデータをマテリアライズします。特に、CTEの複雑な中間結果を複数回再利用し、かつその中間結果に対してさらに複雑な結合やソート、インデックス付けが必要な場合に非常に有効です。ただし、一時テーブルの作成とデータ挿入にはオーバーヘッドが発生します。

どの方法を選択すべきか？

| 手法 | マテリアライズ特性 | 可読性・保守性 | パフォーマンス（一般的な傾向） | 用途 | | CTE (デフォルト) | 一時的：デフォルトでマ内部的にデータをキャッシュする傾向あり。 | コード分割による可読性向上。再帰クエリに必須。 | 複雑なCTEの場合、マテリアライズのオーバーヘッドがある。 | 一時的な中間結果を定義し、読みやすくしたい複雑なクエリ。再帰クエリ。一度だけ計算して複数回利用したい場合（ただし、MATERIALIZED推奨）。 | | サブクエリ（派生テーブル） | 非マテリアライズ：メインクエリにインライン展開される傾向。 | ネストが深くなると可読性が低下する可能性。 | オプティマイザによる広範な最適化が期待できる。 | 単純な中間結果で、メインクエリと統合して最適化したい場合。 |</code>

PostgreSQL WITH クエリ: 一般的なエラーとトラブルシューシューティング

PostgreSQLのWITH句 (Common Table Expressions: CTEs) は、複雑なクエリをより読みやすく、管理しやすくするための強力な機能です。しかし、その内部的な振る舞い、特にマテリアライズ (Materialization) という概念が原因で、予期せぬパフォーマンス問題やエラーが発生することがあります。

最も頻繁に遭遇する問題の一つです。CTEを使用することで、むしろクエリが遅くなることがあります。

よくある原因

1. 不必要なマテリアライズ
   PostgreSQLはデフォルトでCTEをマテリアライズ（一時的な結果セットを物理的に作成）しようとします。これは、CTEが何度も参照される場合には効果的ですが、一度しか参照されないCTEの場合、その中間結果を一時テーブルに書き出すオーバーヘッドが無駄になり、かえって遅くなることがあります。本来なら、CTEの内容がメインクエリに直接「インライン展開」される方が効率的な場合があります。
2. 統計情報の欠如または古さ
   CTEが参照するテーブルの統計情報が不足している、あるいは古いために、オプティマイザが不適切な実行計画を選択してしまうことがあります。

トラブルシューティング

• 統計情報の更新
  テーブルのデータ分布が大きく変わった場合、ANALYZE <table_name>;またはVACUUM ANALYZE <table_name>;を実行して、データベースの統計情報を最新に保ちます。これにより、オプティマイザがより正確な実行計画を選択できるようになります。
• インデックスの確認と作成
  CTEの内部クエリや、その結果を結合するメインクエリで利用されるカラムに適切なインデックスが貼られているか確認します。インデックスがないためにフルスキャンが発生している場合は、インデックスを追加することで大幅に改善することがあります。
• CTEをサブクエリに書き換え
  NOT MATERIALIZEDが期待通りに機能しない場合や、古いPostgreSQLバージョンを使用している場合、CTEを通常の**サブクエリ（派生テーブル）**として書き換えることで、マテリアライズを回避し、オプティマイザに統合的な最適化を促すことができます。

  SELECT * FROM (
      SELECT column1, column2 FROM large_table WHERE condition_a
  ) AS subquery_alias
  WHERE condition_b;
  

  ただし、これによりクエリの可読性が低下する可能性があるため、バランスを考慮が必要です。
• MATERIALIZEDキーワードの試行 (PostgreSQL 12以降)
  逆に、CTEが複数回参照される場合や、CTEの計算が非常に重く、一度だけ計算して結果を再利用したい場合は、**MATERIALIZED**を明示的に指定します。

  WITH my_expensive_cte AS MATERIALIZED (
      SELECT expensive_calculation(...) FROM complex_join_of_tables
  )
  SELECT * FROM my_expensive_cte JOIN another_table ON ...;
  

  PostgreSQLはデフォルトでマテリアライズを試みますが、明示することで意図が明確になり、期待通りの動作を強制できます。
• NOT MATERIALIZEDキーワードの試行 (PostgreSQL 12以降)
  CTEが一度しか参照されず、かつマテリアライズが不要と思われる場合、WITH句の定義で**NOT MATERIALIZED**キーワードを明示的に指定します。

  WITH my_cte AS NOT MATERIALIZED (
      SELECT column1, column2 FROM large_table WHERE condition_a
  )
  SELECT * FROM my_cte WHERE condition_b;
  

  これにより、オプティマイザはCTEの内容をメインクエリにインライン展開することを強く検討し、より広範な最適化（例: condition_bがcondition_aよりも先に適用される可能性など）を適用できるようになります。EXPLAIN ANALYZEでCTE Scanが消え、より効率的なプランになっているかを確認してください。
• EXPLAIN ANALYZEの活用
  何よりもまず、問題のクエリの実行計画を**EXPLAIN ANALYZE**で確認します。
  • CTEがマテリアライズされている場合、実行計画に**CTE Scan on <cte_name>**のようなノードが見られます。そのノードのrows、actual time、loopsなどを確認し、予想外に多くの行が処理されていたり、時間がかかっていたりしないかチェックします。
  • 例えば、CTEが1度しか使われていないのにCTE Scanで大量のI/Oが発生している場合、マテリアライズが原因の可能性があります。

よくある原因

1. 巨大なCTE結果のキャッシュ
   マテリアライズされたCTEの結果が非常に大きい場合、それがwork_mem（PostgreSQLの作業メモリ設定）に収まりきらず、ディスク上に一時ファイルとして書き出される（スピルアウトする）ことがあります。これはI/O負荷を増大させ、パフォーマンスを低下させます。
2. 不要なカラムの選択
   CTEの定義で、最終的なクエリで必要ないカラムまで選択している場合、マテリアライズされる一時結果セットのサイズが不必要に大きくなります。

トラブルシューティング

• 一時テーブルの活用
  非常に大きな中間結果で、かつその結果に対してさらに複雑な操作（複数のインデックスを貼るなど）が必要な場合は、一時テーブル（CREATE TEMPORARY TABLE）の使用を検討します。一時テーブルはCTEよりも明示的にデータを保存し、永続的なインデックスを付与できるため、より制御されたパフォーマンスチューニングが可能です。
• work_memの調整
  もしディスクへのスピルが頻繁に発生しているとEXPLAIN ANALYZEで示される場合（例: Sort Method: external merge Disk: ...）、システム全体のリソースと相談しながらwork_memの値を増やしてみることを検討します。ただし、work_memはセッションごとに割り当てられるため、多数の並行クエリが存在するシステムでは、総メモリ消費量が増大するリスクがあります。
• CTE内部でのデータ絞り込み
  CTEの内部クエリでWHERE句などを使い、できるだけ早期に不要な行を排除し、マテリアライズされるデータ量を減らします。
• CTEで必要なカラムのみを選択
  CTEの定義において、最終的に必要となるカラムのみを選択するように心がけます。

WITH RECURSIVE句を使った再帰CTEでは、停止条件が適切でない場合に無限ループに陥ることがあります。

よくある原因

1. 停止条件の欠如または誤り
   再帰クエリのベースケース（再帰の開始点）や、再帰部分の終了条件が正しく定義されていない。
2. 循環参照
   辿っているデータに循環参照（例: 親が子を参照し、その子が再び親を参照するなど）が含まれており、再帰が永久に終了しない。

トラブルシューティング

• max_recursion_depth (PostgreSQL 14以降)
  PostgreSQL 14以降では、再帰CTEの最大深さを制限するmax_recursion_depthという設定が導入されました。これにより、無限ループが発生した場合でも、システムリソースの枯渇を防ぐことができます。本番環境ではこの設定を適切に調整することが推奨されます。
• パスの追跡
  デバッグのため、再帰CTEにpathやdepth（深さ）のようなカラムを追加し、再帰がどのように進行しているかを追跡できるようにします。
• UNIONとUNION ALLの選択
  再帰CTEでUNION（重複行を排除）を使うと、無限ループを避けるのに役立つ場合があります。UNION ALLは重複を排除しないためパフォーマンスは良いですが、無限ループのリスクが高まります。データの特性を理解して選択します。
• 停止条件の厳密な確認
  再帰CTEの再帰部分には、必ずWHERE句などで再帰が終了する条件を明示的に指定する必要があります。

  WITH RECURSIVE organization_tree AS (
      -- ベースケース (非再帰部分)
      SELECT employee_id, manager_id, employee_name, 0 AS level
      FROM employees
      WHERE manager_id IS NULL -- 最上位の従業員
  
      UNION ALL
  
      -- 再帰部分
      SELECT e.employee_id, e.manager_id, e.employee_name, ot.level + 1
      FROM employees e
      JOIN organization_tree ot ON e.manager_id = ot.employee_id
      WHERE ot.level < 100 -- 無限ループ防止のための深さ制限を設ける（推奨）
  )
  SELECT * FROM organization_tree;
  

CTEは非常に強力なツールですが、その裏にあるマテリアライズの挙動を理解し、EXPLAIN ANALYZEを積極的に活用することで、パフォーマンスの問題を効率的にトラブルシューティングし、最適なクエリ設計を行うことができます。 PostgreSQLのWITH句（CTE）は、複雑なクエリの可読性を高める強力なツールですが、そのマテリアライズの挙動がパフォーマンスに影響を与えることがあります。CTEの代替手段を理解し、適切に使い分けることで、より効率的なクエリを作成できます。

CTEは非常に便利ですが、特定のシナリオでは他のSQL構造の方が適している場合があります。ここでは、主な代替手段とその特徴について説明します。

サブクエリ（Derived Tables / 派生テーブル）

CTEの最も直接的な代替手段は、FROM句内で使用するサブクエリです。これを「派生テーブル (Derived Table)」と呼ぶこともあります。

特徴

• 再利用性
  1つのサブクエリを同じクエリ内で複数回参照することはできません。
• 可読性
  ネストが深くなると、可読性が低下する傾向があります。特に複数のサブクエリを組み合わせる場合、WITH句の方がコードの構造が分かりやすくなります。
• マテリアライズの制御が難しい
  CTEとは異なり、PostgreSQLは通常、サブクエリをマテリアライズしない傾向にあります。つまり、サブクエリの内部のフィルター条件などが外側のクエリに「プッシュダウン」され、全体として最適化される可能性が高まります。ただし、この挙動はオプティマイザの判断に委ねられるため、常に保証されるわけではありません。

例

-- CTEの例
WITH high_value_products AS (
    SELECT
        product_id,
        product_name,
        price
    FROM
        products
    WHERE
        price >= 100.00
)
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    high_value_products p
JOIN
    sales s ON p.product_id = s.product_id;

---
-- サブクエリの例 (同じロジック)
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    (
        SELECT
            product_id,
            product_name,
            price
        FROM
            products
        WHERE
            price >= 100.00
    ) AS p -- サブクエリには必ずエイリアスが必要です
JOIN
    sales s ON p.product_id = s.product_id;

使い分けのヒント

• クエリの可読性が最優先される場合、特に複雑なロジックを段階的に構築する場合はCTEの方が優れています。
• CTEを一度しか参照しない場合や、オプティマイザによる広範な最適化を期待したい場合に、NOT MATERIALIZED CTEの代わりにサブクエリを検討できます。

一時テーブル (Temporary Tables)

一時テーブルは、現在のセッションでのみ存在する物理的なテーブルです。CTEがクエリ実行中のみ利用可能な論理的な構造であるのに対し、一時テーブルはデータを一時的に保存します。

特徴

• デバッグのしやすさ
  中間結果を一時テーブルに保存することで、各ステップのデータを簡単に確認でき、デバッグが容易になります。
• 複数クエリでの利用
  同じセッション内であれば、複数の独立したクエリから一時テーブルを参照できます。CTEは単一のSQL文のスコープに限定されます。
• インデックスの作成
  一時テーブルには、通常のテーブルと同様にインデックスを作成できます。これにより、後続のクエリでのアクセス性能を大幅に向上させることが可能です。
• 明示的なマテリアライズ
  一時テーブルは必ずデータを物理的に保存します。これにより、大規模な中間結果を一度計算して、その後の複数のステップで効率的に再利用できます。

例

-- 一時テーブルの作成と利用
CREATE TEMPORARY TABLE temp_high_value_products AS
SELECT
    product_id,
    product_name,
    price
FROM
    products
WHERE
    price >= 100.00;

-- 必要であればインデックスを作成 (パフォーマンス向上に寄与)
CREATE INDEX ON temp_high_value_products (product_id);

SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold
FROM
    temp_high_value_products p
JOIN
    sales s ON p.product_id = s.product_id
WHERE
    s.sale_date BETWEEN '2023-02-01' AND '2023-02-28';

-- セッション終了時または明示的に削除
DROP TABLE temp_high_value_products;

使い分けのヒント

• CTEのMATERIALIZEDでパフォーマンスが改善しない場合に、一時テーブルを試してみる価値があります。
• クエリが非常に長く、複数の論理ステップに分割し、それぞれの結果を独立して検証・デバッグしたい場合。
• 中間結果に対して独自のインデックスを適用して、パフォーマンスを大幅に改善したい場合。
• 中間結果が非常に大きく、それが複数のステップや複雑な結合で何度も参照される場合。

ビュー (Views)

ビューは、保存されたクエリ定義であり、仮想テーブルとして扱われます。ビュー自体はデータを持ちませんが、参照されるたびに基になるクエリが実行されます。

特徴

• マテリアライズド・ビュー (Materialized Views)
  PostgreSQLには、ビューのクエリ結果を物理的にディスクに保存する「マテリアライズド・ビュー」という機能もあります。これは、定期的にデータを更新することで、常に最新のデータを高速に参照したい場合に非常に強力です。
• 抽象化とセキュリティ
  複雑なクエリロジックをユーザーから隠蔽し、特定のデータセットのみへのアクセスを許可するなどのセキュリティ目的にも利用できます。
• 再利用性
  複数のクエリやアプリケーションから同じロジックを再利用したい場合に最適です。CTEは単一クエリ内でのみ利用可能ですが、ビューはデータベースオブジェクトとして永続化されます。

例 (通常のビュー)

-- ビューの作成
CREATE VIEW recent_high_value_sales AS
SELECT
    p.product_name,
    p.price,
    s.sale_date,
    s.quantity_sold,
    s.total_price
FROM
    products p
JOIN
    sales s ON p.product_id = s.product_id
WHERE
    p.price >= 100.00
    AND s.sale_date >= '2023-01-01';

-- ビューの利用
SELECT
    product_name,
    SUM(total_price) AS total_revenue
FROM
    recent_high_value_sales
WHERE
    sale_date BETWEEN '2023-02-01' AND '2023-02-28'
GROUP BY
    product_name
ORDER BY
    total_revenue DESC;

例 (マテリアライズド・ビュー)

-- マテリアライズド・ビューの作成
CREATE MATERIALIZED VIEW daily_product_sales_summary AS
SELECT
    p.product_name,
    s.sale_date,
    SUM(s.quantity_sold) AS total_quantity,
    SUM(s.total_price) AS daily_revenue
FROM
    sales s
JOIN
    products p ON s.product_id = p.product_id
GROUP BY
    p.product_name, s.sale_date
ORDER BY
    s.sale_date, p.product_name;

-- マテリアライズド・ビューの利用 (高速)
SELECT * FROM daily_product_sales_summary WHERE sale_date = '2023-02-05';

-- データが更新された場合は手動で更新する必要がある
REFRESH MATERIALIZED VIEW daily_product_sales_summary;

使い分けのヒント

• 定期的に更新される静的なレポートデータなど、更新頻度は低いが高速な参照が必要な場合はマテリアライズド・ビューが適しています。
• データ抽象化やセキュリティのために特定のデータセットへのアクセスを制限したい場合。
• 同じ複雑なクエリロジックを複数の場所（他のクエリ、アプリケーションコードなど）で頻繁に再利用したい場合。

CTEは、クエリの可読性と構造化において優れていますが、マテリアライズの挙動を理解し、それがパフォーマンスに与える影響を考慮することが重要です。

• ビュー/マテリアライズド・ビュー
  永続的なロジックの再利用、データ抽象化、セキュリティに優れる。マテリアライズド・ビューは、高速な読み込みのために結果を物理的に保存する。
• 一時テーブル
  大規模な中間結果を物理的に保存し、その上にインデックスを張ることで高いパフォーマンスを発揮。複数のクエリステップで中間結果を再利用する場合や、デバッグが重要な場合に適している。
• サブクエリ
  CTEの最も直接的な代替。シンプルな中間結果や、オプティマイザによるインライン最適化を期待する場合に有効。ネストが深くなると可読性が低下しやすい。
• CTE
  複雑なクエリを分割し、可読性を高める。同じクエリ内で複数回参照される中間結果や、再帰クエリに最適。MATERIALIZED / NOT MATERIALIZEDでマテリアライズを制御。