PostgreSQL CTE 会被物化吗？WITH 查询对性能有什么影响？

简化版

PostgreSQL 早期版本中 CTE 常被当作优化屏障并物化；新版本会在合适场景内联，也支持 MATERIALIZED 和 NOT MATERIALIZED 提示。CTE 能提升可读性，但复杂查询里要用执行计划确认是否导致重复扫描或阻止谓词下推。

详细版

CTE 用 WITH 定义临时结果，适合拆分复杂 SQL、递归查询、复用中间结果。问题在于物化会先把 CTE 结果算出来，再由外层查询使用，可能导致外层过滤条件无法下推。

例如 CTE 先扫描 100 万行，外层只要其中 100 行，如果无法内联或下推，就会浪费大量工作。

PostgreSQL 12 以后对非递归、无副作用、单次引用的 CTE 更可能内联；多次引用时物化可能反而有利。真正判断要看 EXPLAIN ANALYZE。

完整版教学

一、CTE 的基本作用

CTE 是 Common Table Expression，用 WITH 给子查询起名，让复杂 SQL 更清晰。

WITH recent_orders AS (
  SELECT *
  FROM orders
  WHERE created_at >= CURRENT_DATE - INTERVAL '7 days'
)
SELECT user_id, count(*)
FROM recent_orders
GROUP BY user_id;

它的优势是可读性和复用，尤其是多步聚合、递归查询和复杂报表。

二、什么叫物化

物化可以理解为先把 CTE 的结果算出来，形成一个临时结果，再给外层查询使用。它像先做一张临时表。

执行 CTE -> 得到中间结果 -> 外层查询读取中间结果

如果中间结果很小，物化没问题；如果中间结果很大，而外层还会过滤掉大部分数据，物化就可能浪费。

三、优化屏障为什么影响性能

假设订单表 1000 万行，CTE 里只筛了时间，外层再筛 user_id=1001。如果外层条件不能下推，CTE 可能先处理大量近 7 天订单，再过滤用户。

如果优化器能内联，条件可以合并成：

WHERE created_at >= ...
  AND user_id = 1001

这样就可能使用 (user_id, created_at) 索引，扫描量从几十万降到几十行。

四、新版本行为和显式控制

PostgreSQL 12 以后，很多非递归 CTE 不再天然是优化屏障，优化器可以选择内联。你也可以用：

WITH recent_orders AS NOT MATERIALIZED (...)
SELECT ...

或：

WITH recent_orders AS MATERIALIZED (...)
SELECT ...

NOT MATERIALIZED 更偏向让优化器把 CTE 展开；MATERIALIZED 则强制先算中间结果。

五、什么时候物化反而有利

如果 CTE 被多次引用，物化一次再复用可能比重复计算更好。比如一个复杂聚合结果同时被 join 两次，物化能避免重复扫描。

场景	可能选择
CTE 只引用一次，外层过滤强	倾向内联
CTE 结果很小	物化影响小
CTE 被多次引用	物化可能有利
递归 CTE	必须按递归语义处理

所以 CTE 性能不是简单好坏，而是看引用次数、结果规模和过滤条件。

六、常见误区与追问

误区：CTE 一定比子查询慢。 新版本可能内联，具体看执行计划。
误区：WITH 只是语法糖。 某些版本和场景下它会影响优化器选择。
误区：物化一定不好。 多次复用或中间结果很小时，物化可能更稳。
追问：如何强制物化？ 使用 MATERIALIZED。
追问：如何避免优化屏障？ 可用 NOT MATERIALIZED，或改写为子查询并验证计划。

七、加强记忆

记忆钩子：CTE 像把 SQL 拆成草稿纸；草稿纸有时只是帮你看清步骤，有时真的会先算一大张中间表。

回答这题要讲版本差异、物化含义、谓词下推、显式提示和执行计划。这样既准确，又不会把 CTE 简化成“WITH 很慢”。

简化版

详细版