PostgreSQL CTE公用表表达式与物化CTE的性能差异

在编写复杂的SQL查询时，公用表表达式（Common Table Expression，简称CTE）是一个强大的工具，它能将查询逻辑模块化，让代码更清晰。然而，CTE有两种执行策略：普通CTE和物化CTE，选择不当可能导致性能天差地别。本指南将直接解释它们的核心差异，并告诉你在何种场景下选择哪一种。

第一阶段：理解CTE及其两种执行策略

首先，明确基本概念。

1. 定义一个CTE，它是在主查询前定义的临时结果集，使用 WITH 子句。

   WITH my_cte AS (
       SELECT column1, column2
       FROM some_table
       WHERE condition = true
   )
   SELECT * FROM my_cte;

2. 理解普通CTE（非物化）的默认行为。在PostgreSQL中，优化器可能会将CTE的查询逻辑内联到主查询中。这意味着它不是作为一个独立的、预先计算的结果集存在。优化器会将它“拆开”，并与主查询的其它部分合并优化，可能会利用索引，也可能重新调整查询计划。

3. 理解物化CTE的行为。物化CTE会强制数据库引擎先执行并存储CTE的结果集，形成一个临时结果集（通常存储在内存或临时文件中），然后主查询再基于这个临时结果集进行操作。这相当于在查询中间创建了一个临时表。

第二阶段：对比普通CTE与物化CTE的性能差异

性能差异的核心在于优化器能否以及如何利用底层表的索引和统计信息。

关键差异一：内联与物化

• 普通CTE（可内联）：优化器可能将CTE视为一个“视图”，并将其逻辑融合到整个查询计划中。这为更激进的优化（如利用索引、条件下推）创造了条件。
• 物化CTE（MATERIALIZED）：优化器必须先执行CTE并物化其结果。后续查询只能扫描这个物化结果，无法穿透它去访问原始表的索引。

关键差异二：结果集引用次数的影响

这是决定性能最关键的场景之一。

• 当CTE在主查询中只被引用一次时：

  • 普通CTE：通常表现更优或持平，因为优化器有更大的自由度去优化整个查询。
  • 物化CTE：可能会带来不必要的开销（物化成本），性能可能更差。

• 当CTE在主查询中被多次引用时（例如，在主查询的 SELECT、WHERE、JOIN 等多处出现 my_cte）：

  • 普通CTE：如果未被内联，它可能会被执行多次（每次引用都执行一次）。这会导致重复计算，严重降低性能。
  • 物化CTE：只计算一次。所有引用都基于同一份物化结果，避免了重复计算，通常能带来显著的性能提升。

总结表格

第三阶段：实践指南：何时使用哪种CTE

根据上述差异，遵循以下准则做出选择。

1. 优先尝试不使用 MATERIALIZED 关键字，让优化器自行决定。

   • 这适用于绝大多数简单或单次引用的CTE。

2. 强制物化的两种经典场景：

   • 场景A：CTE结果集较小，但被多次引用。

     1. 识别出查询中存在被多次引用的复杂子查询。
     2. 将其重构为一个CTE。
     3. 添加 MATERIALIZED 关键字。

        WITH small_data AS MATERIALIZED (
        -- 这个查询返回少量行，但计算成本高
        SELECT id, complex_calculation(value) AS result
        FROM big_table
        WHERE category = '特定类别'
        )
        SELECT t1.*, sd1.result
        FROM table_a t1
        JOIN small_data sd1 ON t1.ref_id = sd1.id
        JOIN table_b t2 ON t2.ref_id = sd1.id
        WHERE sd1.result > 100;

        在此示例中，small_data 被 table_a 和 table_b 两次连接引用。物化后，complex_calculation 只计算一次。

   • 场景B：需要稳定、可预测的执行计划。
     当你发现普通CTE因优化器内联导致计划变得极其复杂、难以调试，或产生了不理想的性能（如错误的连接顺序）时，可以尝试物化它来“冻结”CTE的执行，简化整体计划。

3. 强制不物化的场景：
   当优化器错误地选择物化一个CTE，而你确信内联会更好时，可以使用 NOT MATERIALIZED 关键字进行干预。

   WITH always_inline AS NOT MATERIALIZED (
       -- 希望优化器能利用这个查询中的索引
       SELECT id FROM indexed_table WHERE date > '2023-01-01'
   )
   SELECT * FROM always_inline;

第四阶段：通过示例观察执行计划

理论最终需要实践检验。使用 EXPLAIN (ANALYZE, BUFFERS) 命令是你的终极工具。

1. 编写一个涉及多次引用CTE的查询，不添加物化提示。

   EXPLAIN (ANALYZE, BUFFERS)
   WITH order_totals AS (
       SELECT customer_id, SUM(amount) AS total
       FROM orders
       GROUP BY customer_id
   )
   SELECT c.name, ot.total, ot.total / c.account_age
   FROM customers c
   JOIN order_totals ot ON c.id = ot.customer_id
   WHERE ot.total > 1000;

2. 分析执行计划。关注计划中是否出现 CTE Scan on order_totals，并且它下方是否出现了对 orders 表的 Aggregate 操作的多个副本。如果是，说明CTE被多次执行。

3. 添加 MATERIALIZED 关键字并重新执行分析。

   EXPLAIN (ANALYZE, BUFFERS)
   WITH order_totals AS MATERIALIZED (
       SELECT customer_id, SUM(amount) AS total
       FROM orders
       GROUP BY customer_id
   )
   SELECT c.name, ot.total, ot.total / c.account_age
   FROM customers c
   JOIN order_totals ot ON c.id = ot.customer_id
   WHERE ot.total > 1000;

4. 对比两次计划。物化后的计划通常会显示一个 CTE Scan，并且它上方的 Aggregate 操作只出现一次。重点观察 Actual Time 和 Buffers 的差异，物化版本在多次引用场景下，总体时间和I/O通常会大幅下降。