子查询重复执行是性能杀手，典型信号为执行计划中多次出现相同subplan节点、actual total time与buffers成倍增长；应通过explain analyze验证，并在结果集>10k行且引用≥2次或单次耗时>50ms时优先物化为临时表并执行analyze。
子查询重复执行导致性能陡增的典型信号
当嵌套子查询在 SELECT、JOIN 或 WHERE 中被多次引用（尤其在多表关联或聚合后再次过滤时），数据库往往无法自动物化结果，每次都需要重算。典型表现是执行计划里出现多个相同的 SubPlan 或 InitPlan 节点，且子查询的估算行数远大于实际需要——这说明优化器“不敢信”子查询结果可复用。
实操判断方式：
- 用
EXPLAIN (ANALYZE, BUFFERS)查看子查询是否被反复执行（关注Actual Total Time和Buffers是否成倍增长） - 把子查询单独拎出来跑一次，记下返回行数和耗时；再对比它在主查询中“被调用次数 × 单次耗时”是否显著高于临时表方案
- PostgreSQL 中若子查询含
LIMIT、OFFSET或非确定性函数（如random()），基本无法安全物化，必须重构
临时表 vs CTE 的物化行为差异（以 PostgreSQL 为例）
很多人误以为 WITH 子句（CTE）天然物化，其实 PostgreSQL 12+ 默认仅在 MATERIALIZED 显式声明或满足特定条件（如递归、被多次引用、含副作用函数）时才真正落地到临时空间。不加修饰的 CTE 更像语法糖，优化器仍可能内联展开。
关键操作建议：
- 强制物化：写成
WITH xxx AS MATERIALIZED (SELECT ...) - 更可控的选择：用
CREATE TEMP TABLE tmp_xxx AS SELECT ...，明确控制生命周期和索引 - 注意
TEMP表在事务结束时自动销毁，若需跨事务复用，得用UNLOGGED普通表 + 手动清理 - MySQL 用户注意：
WITH在 8.0+ 才支持，且默认不物化；替代方案是CREATE TEMPORARY TABLE或派生表（FROM (SELECT ...) AS t）
决定是否重构的核心阈值：三类成本交叉点
没有统一“行数阈值”，真正起作用的是三类开销的平衡点：子查询单次执行成本、子查询被引用次数、临时表构建+索引+读取的总开销。实践中可按以下条件快速决策：
- 子查询结果集 > 10k 行 且 被引用 ≥ 2 次 → 倾向建临时表
- 子查询含复杂 JOIN / 窗口函数 / 多层嵌套 → 即使只用 1 次，若单次耗时 > 50ms，也值得物化并加索引
- 主查询本身已很慢（> 1s），而子查询占其中 30%+ 时间 → 优先提取，哪怕只有几百行，因为减少计划复杂度本身就有收益
- 避免踩坑：别给临时表建过多索引；
CREATE INDEX ON tmp_xxx (col)只在后续有WHERE或JOIN用到该列时才有效
临时表索引与统计信息的隐形依赖
建完 CREATE TEMP TABLE 后直接 JOIN，有时反而更慢——因为 PostgreSQL 不会自动收集临时表的统计信息，优化器只能瞎猜行数，导致错误选择嵌套循环而非哈希连接。
必须补的操作：
- 执行
ANALYZE tmp_xxx;（不是VACUUM，后者对临时表无效） - 若临时表字段参与 JOIN 或过滤，至少在对应列上建索引，例如
CREATE INDEX ON tmp_xxx (user_id); - SQL Server 用户注意：
#temp表支持统计信息自动更新，但首次执行前需显式UPDATE STATISTICS #tmp;；Oracle 则需DBMS_STATS.GATHER_TABLE_STATS手动触发
临时表不是一劳永逸的银弹，它的优势完全建立在“准确的行数估计”和“恰当的访问路径”之上。漏掉 ANALYZE 或索引，很可能让性能比原嵌套子查询还差。
