PostgreSQL物化CTE与非物化CTE区别在哪里？CTE执行行为深度解析

来源：Python教程作者：冷风头衔：草根站长

导读：本期聚焦于冷风创作的《PostgreSQL物化CTE与非物化CTE区别在哪里？CTE执行行为深度解析》，敬请观看详情。执行计划里同样写着的WITH子查询，为什么有时被优化器展开成内联视图，有时却变成独立临时表？这背后是CTE物化与非物化两种行为的差异。非物化CTE会被查询规划器直接内联到主查询中，相当于子查询折叠，能利用索引且可多次条件裁剪；物化CTE则一次性算出结果落进内存或临时文件，后续引用都读这份快照，避免重复计算却可能膨胀中间数据。从PostgreSQL十二版前后默认规则变化，到使用MATERIALIZED与NOT MATERIALIZEDHint控制，再到递归查询强制物化等场景，理解二者差异能帮我们写出更稳更快的报表与数仓语句，也能解释为何同一条SQL在不同版本耗时悬殊。

在PostgreSQL的查询编写中，公共表表达式也就是CTE，通过WITH子句定义，常用来拆分复杂SQL逻辑。但同样一段WITH代码，在不同语句或版本中表现出的执行方式可能完全不同：有时它像子查询一样被展开进主计划，有时却被独立计算并缓存结果。这种差异正是物化CTE与非物化CTE的区别所在，理解它对于排查慢查询和写出稳定执行计划非常关键。

非物化CTE的内联展开机制

非物化CTE是指查询规划器将CTE的定义直接内联到引用它的主查询里，类似于把子查询折叠平铺。在这种模式下，CTE本身不会单独生成一层算子，优化器可以跨越CTE边界做条件推送、索引选择和连接顺序重排。例如主查询只取CTE结果中的部分列或带有限制条件，这些过滤可能被下推到CTE内部的扫描节点，从而减少读取量。

从PostgreSQL 12开始，对于普通非递归CTE且未被多次引用的场景，优化器默认倾向于非物化，也就是不强制缓存。这带来的好处是执行计划更灵活，统计信息利用更充分。我们可以用一段简单代码观察：定义一个CTE筛选订单，主查询再按状态过滤，EXPLAIN中会看到过滤条件直接出现在底层表扫描上，而非CTE外部。

EXPLAIN
WITH order_cte AS (
  SELECT id, user_id, status, amount
  FROM orders
  WHERE create_date >= '2023-01-01'
)
SELECT user_id, sum(amount)
FROM order_cte
WHERE status = 'paid'
GROUP BY user_id;

上述语句在较新版本中，status = 'paid'很可能被下推到orders扫描，CTE仅作为逻辑命名存在。这种非物化行为在CTE只被引用一次时几乎零额外开销，也避免了中间结果膨胀。但如果同一个CTE在主查询里被join两次，规划器仍可能为了复用而选择物化，这就需要留意。

物化CTE的快照缓存与代价

物化CTE则是规划器为CTE单独建立一个执行节点，把它的全部结果计算出来后放入内存或临时表，后续所有引用都读取这份已生成的快照。它适用于CTE计算非常昂贵、且被多次引用的情形，比如一个聚合了上亿行的统计结果，被主查询中三个不同维度join使用，物化一次显然比算三次好。

不过物化也有明显代价。首先，结果集若很大，会占用大量内存或产生临时文件落盘；其次，物化阻断了条件下推，主查询对CTE的过滤只能在缓存之后进行。我们可以通过MATERIALIZED关键字显式要求物化，或者用NOT MATERIALIZED要求展开。看下面例子，强制物化一个带随机函数的CTE，能保证每行只算一次，避免非物化时函数被反复执行。

EXPLAIN
WITH rand_cte AS MATERIALIZED (
  SELECT id, random() AS r
  FROM users
)
SELECT *
FROM rand_cte a
JOIN rand_cte b ON a.id <> b.id
WHERE a.r > 0.5;

这里若不写MATERIALIZED，非物化展开可能导致random()在join展开时被调用多轮，结果既慢又乱。物化之后，CTE算出固定快照，join基于稳定数据执行。但也要注意，如果users表极大，物化中间集可能比展开更慢，所以需结合EXPLAIN的cost和实际耗时判断。

版本差异与递归CTE的特殊处理

PostgreSQL在12版之前，所有CTE默认都是物化的，这意味着哪怕只引用一次，也会先算完缓存。很多老系统迁移到新版本后，同样SQL变快了，就是因为默认改为非物化内联。但递归CTE（WITH RECURSIVE）无论版本都强制物化，因为它的迭代逻辑依赖上一轮结果，无法展开成纯静态子查询。

我们在做版本升级兼容时，应检查原本依赖物化副作用的语句。例如旧版中利用CTE物化来避免volatile函数重复执行，升级后可能失效，此时要显式加MATERIALIZED。另外，当CTE被多次引用且内部含窗口函数时，规划器通常自动物化，可用EXPLAIN (ANALYZE, BUFFERS)确认是否出现Materialize节点。

EXPLAIN (ANALYZE, BUFFERS)
WITH multi_ref AS (
  SELECT dept_id, avg(salary) AS avg_sal
  FROM employees
  GROUP BY dept_id
)
SELECT a.emp_name, a.salary - b.avg_sal
FROM employees a
JOIN multi_ref b ON a.dept_id = b.dept_id
WHERE a.salary > b.avg_sal;

从输出里若看到CTE Scan或Materialize，说明已物化；若看到被合并进HashAggregate之下，则是非物化。掌握这些观察手段，才能精准控制PostgreSQL的CTE行为，让查询既清晰又高效。

PostgreSQL 物化CTE 非物化CTE修改时间：2026-08-18 00:24:30

免责声明：已尽一切努力确保本网站所含信息的准确性。网站内容多为原创整理与精心编撰，观点力求客观中立。本站旨在免费分享，内容仅供个人学习、研究或参考使用。若引用了第三方作品，版权归原作者所有。如内容涉及您的权益，请联系我们处理。