PostgreSQL 12+ 默认不物化 CTE，仅作语法糖，可能内联、重排或多次执行；需强制物化时用 MATERIALIZED 关键字。
CTE 在 PostgreSQL 里到底会不会物化？
PostgreSQL 12+ 默认不物化 CTE，它只是语法糖，优化器可能内联展开、重排甚至多次执行——这点和 SQL Server 或 Oracle 完全不同。所以 WITH cte AS (SELECT * FROM huge_table WHERE ...) 被引用两次，很可能触发两次全表扫描。
实操建议：
- 用
EXPLAIN (ANALYZE, BUFFERS)看执行计划，确认是否真的复用了中间结果 - 需要强制物化（比如避免重复计算或控制执行顺序），加
MATERIALIZED关键字：WITH cte AS MATERIALIZED (SELECT ...) - 如果 CTE 只被引用一次，且逻辑简单，基本等价于子查询；但可读性更好，命名清晰
子查询嵌套深了为什么执行变慢？
MySQL 5.7 和早期 PostgreSQL 版本对多层嵌套子查询优化乏力，尤其 WHERE x IN (SELECT ...) 或 SELECT * FROM t1 WHERE EXISTS (SELECT ...) 容易触发“相关子查询”逐行求值，性能断崖式下跌。
常见错误现象：
-
EXPLAIN显示DEPENDENT SUBQUERY或SubPlan且 rows 值很大 - 外层返回 10 行，子查询却被执行了 10 次，每次扫描上万行
优化方向：
- 能改写成 JOIN 的尽量改写，尤其是
IN/EXISTS场景 - MySQL 8.0+ 支持
LATERAL，可让子查询“感知”外层变量，避免全量重复计算 - PostgreSQL 中，
NOT IN (NULL)会意外返回空结果，比子查询逻辑 bug 更隐蔽
临时表什么时候真能提升性能？
临时表（CREATE TEMP TABLE）在需要多次读取、中间结果大、或涉及排序/聚合后再过滤时，往往比 CTE 或子查询更稳——因为数据真实落盘（或内存），统计信息可用，索引可建。
但代价明确：
- 显式创建 + 手动清理（
DROP TEMP TABLE），出错容易残留 - 事务内自动清理，跨会话不可见，不适合长流程调度
- PG 中临时表默认无统计信息，首次查询可能选错执行计划，需手动
ANALYZE
适用场景举例：
- ETL 中先
INSERT INTO temp_x SELECT ... FROM big_log WHERE dt = '2024-01-01'，再对temp_x做多轮聚合与关联 - 复杂报表中，中间结果 > 10 万行且被引用 ≥ 3 次
- 需要在中间结果上建索引加速后续
JOIN或WHERE
可读性不是主观感受，是协作成本
CTE 最大优势不在性能，而在把“查用户订单数 → 筛高价值用户 → 关联最近地址”这种逻辑链拆成命名步骤。但过度拆分（比如每个 SELECT 1 都塞进 CTE）反而增加跳转成本。
真实协作中容易忽略的点：
- 子查询无法被其他查询复用，CTE 至少在同一语句内可复用，临时表还能被多个语句共用
- ORM（如 Django ORM、SQLAlchemy）生成的子查询通常难读难调，而原生 SQL 里 CTE 更易与业务术语对齐
- 临时表名带
temp_前缀是约定，但 CTE 名没约束，起成t1/t2就等于放弃可读性红利
复杂查询里，物化时机、执行路径依赖、以及团队对 MATERIALIZED 的认知差异，才是比语法选择更常引发线上问题的地方。
