物化还是内联
CTE（WITH 子句）到底是内联展开还是物化成临时结果，直接影响性能：物化只算一次但会挡住外层条件下推，内联则可能被重复计算。PostgreSQL 12 之前 CTE 一律物化，12 起改为按引用情况决定，并允许用 MATERIALIZED / NOT MATERIALIZED 显式指定；MySQL 没有这两个关键字，由优化器自行决定合并还是物化。
基本信息
| PostgreSQL 12 之前 | CTE 总是物化，等价于加了一层优化屏障 |
|---|---|
| PostgreSQL 12+ | 默认按引用次数决定，可用 MATERIALIZED / NOT MATERIALIZED 控制 |
| MySQL | 无显式关键字，优化器决定合并或物化 |
| 判断方法 | 看 EXPLAIN 里是否出现物化节点或临时表 |
| 取舍原则 | 重量级结果被多次引用用物化，小结果集想下推用内联 |
典型使用场景
- 一个聚合结果被多处引用，希望只计算一次
- 小结果集想和外层条件一起下推，希望内联展开
- 递归查询与报表口径的复用
- 排查「同样的 SQL 在 PG 12 前后性能变差」
- 跨库迁移时确认 CTE 行为差异
PostgreSQL 显式控制
PostgreSQL 显式控制
-- 显式物化：大聚合只算一次，后续引用都复用它
WITH heavy AS MATERIALIZED (
SELECT user_id, COUNT(*) AS cnt
FROM logs
GROUP BY user_id
)
SELECT * FROM heavy WHERE cnt > 100;
-- 显式内联：小表允许被外层条件下推、被多次展开
WITH light AS NOT MATERIALIZED (
SELECT * FROM small_table WHERE flag = 1
)
SELECT * FROM light l JOIN big b ON b.k = l.k;从执行计划确认
从执行计划确认
-- PostgreSQL：看是否出现 CTE Scan 或 Materialize 节点
EXPLAIN (ANALYZE, COSTS OFF)
WITH t AS (SELECT * FROM orders WHERE user_id = 1)
SELECT * FROM t JOIN users u ON u.id = t.user_id;
-- MySQL：看是否出现派生表或临时表节点
EXPLAIN FORMAT=TREE
WITH t AS (SELECT * FROM orders WHERE user_id = 1)
SELECT * FROM t JOIN users u ON u.id = t.user_id;物化与内联的取舍
| 场景 | 物化更合适 | 内联更合适 |
|---|---|---|
| 被引用多次的重量级查询 | 是，只算一次 | 否，会重复计算 |
| 小结果集加外层过滤 | 否，条件下推被挡住 | 是，条件下推进 CTE |
| 含聚合或窗口的 CTE | 通常只能物化 | 一般不可合并 |
| 含 LIMIT 或 DISTINCT 的 CTE | 常被物化 | 视优化器判断 |
把 CTE 当「只算一次的缓存」是常见误解：PostgreSQL 12+ 可能把它内联导致重复计算，MySQL 的合并策略也由优化器决定。想要只算一次，就显式物化或改用临时表。
数据库大版本升级会改变 CTE 默认行为（例如 PG 11 升 12），升级前把关键查询的执行计划留档，升级后对比，避免性能回退时难以定位原因。