PostgreSQL 12+ 默认物化CTE导致嵌套子查询变慢，尤其被多次引用或结果较大时；NOT MATERIALIZED仅为优化器提示，仅在简单逻辑和强过滤条件下生效，否则静默失效。
CTE默认物化会让嵌套子查询变慢，尤其当它被多次引用或中间结果很大时
PostgreSQL 12+ 默认把 CTE 当作“物化临时表”处理——哪怕你只写一次 WITH cte AS (...)，只要主查询里引用它两次（比如 JOIN cte t1 ON ... JOIN cte t2 ON ...），优化器就会完整执行它两次。这不是 bug，是设计行为。它带来的直接后果是：本可复用的计算被重复做，I/O 和 CPU 成倍上涨。
- 常见错误现象：
EXPLAIN ANALYZE显示同一 CTE 出现在执行计划里两次，且都带CTE Scan on cte，下面还跟着相同的Seq Scan on big_table - 典型场景：三层嵌套改写为 CTE 后反而更慢，比如
shipped_users被WHERE IN和JOIN各用一次，但没加任何提示 - 性能影响：物化本身要写入 work_mem 或磁盘临时文件；若中间结果超
work_mem，还会触发外部排序甚至 spill 到磁盘，延迟飙升
NOT MATERIALIZED 不是开关，而是优化器提示，只在特定条件下生效
NOT MATERIALIZED 是 PostgreSQL 12 引入的隐式 hint，不是语法强制指令。它告诉优化器：“请尝试内联展开这个 CTE，不要物化”。但它是否起效，取决于优化器成本估算。
- 起效条件：CTE 逻辑简单（无窗口函数、无递归、无不可下推的聚合）、外层有强过滤条件（如
WHERE cte.id = ?）、物化代价远高于重复扫描 - 静默失效场景：CTE 包含
ROW_NUMBER()或GROUP BY ... HAVING；或者物化是唯一能保证语义正确性的路径（例如多次引用需严格一致的结果） - 参数差异：
MATERIALIZED是显式声明物化（PG 12+ 支持），NOT MATERIALIZED是反向提示，两者不能共存；不写任一关键字时，由优化器自动决策（PG 12+ 默认倾向物化，PG 11- 默认强制物化）
嵌套子查询 vs CTE：物化机制让它们的执行路径根本不同
很多人以为 WITH cte AS (...) SELECT * FROM cte 和等价子查询只是写法差异，其实底层执行模型完全割裂：子查询可能被优化器“拉平”进主查询做 join reorder 或 index condition pushdown；而 CTE 默认被隔离成黑盒，外层 WHERE 条件无法下推到 CTE 内部。
- 调试方法：对同一个逻辑，分别跑
EXPLAIN (ANALYZE, VERBOSE)，对比执行计划中是否出现CTE ScanvsSubquery Scan，以及 filter 是否落在 CTE 内部还是外部 - 容易踩的坑：把
WHERE id IN (SELECT user_id FROM orders WHERE status='shipped')直接改成WITH shipped AS (SELECT user_id FROM orders WHERE status='shipped') ... WHERE u.id IN (SELECT user_id FROM shipped)—— 看似等价，但后者无法利用orders(status, user_id)复合索引，因为条件被锁死在 CTE 边界内 - 替代方案优先级：想避免物化开销，先考虑改用子查询（尤其非相关子查询）；再考虑
NOT MATERIALIZED；最后才是调大work_mem或加索引
什么时候该坚持用 CTE，而不是退回子查询？
CTE 的价值不在性能，而在可控性和语义清晰度。当你需要确保中间结果一致性、支持递归、或必须配合 DML（如 WITH upsert AS (INSERT ... RETURNING *)），CTE 就不可替代。此时物化不是缺陷，而是保障。
- 必须用 CTE 的场景：可写 CTE（
INSERT/UPDATE/DELETE ... RETURNING）、递归查询（WITH RECURSIVE）、中间结果需被多个不同上下文引用且语义必须严格一致（如风控规则中“过去7天高危行为用户”不能因两次计算时间点微差而结果不同） - 性能兜底建议：对关键 CTE 加
NOT MATERIALIZED后，务必验证EXPLAIN是否真去掉了 CTE Scan；若未生效，立刻检查是否含窗口函数或聚合，或换用子查询+LATERAL模拟 - 容易被忽略的点：
NOT MATERIALIZED只作用于单个 CTE，多个 CTE 并列时需每个都显式标注；且它不改变统计信息收集行为，如果 CTE 内部表数据分布突变，仍可能因过时的pg_stats导致优化器误判
