嵌套子查询性能瓶颈分析与优化实战
嵌套子查询是否真慢需先看执行计划：若子查询节点Actual Total Time超60%且Rows少，可能是相关子查询反复执行；若Rows Removed by Filter远大于Rows，则缺索引或条件未下推；InitPlan只执行一次，SubPlan每行都执行。
怎么看嵌套子查询是不是真慢了
先别急着动手重写SQL，很多时候性能瓶颈的“元凶”未必是子查询本身。通过EXPLAIN ANALYZE看到的“慢”，很可能只是表象——真正拖累速度的，或许是外层的JOIN操作，或者是那个代价高昂的排序。子查询，很多时候只是被“连带”曝光了而已。
判断的关键，在于执行计划中几个核心指标：
- 时间占比是否异常？ 如果子查询节点的
Actual Total Time占整条语句总耗时的60%以上，同时它返回的Rows却很少（比如只有几行），那就要高度警惕了。这通常是“相关子查询”（correlated subquery）在反复执行的典型信号。 - 过滤效率是否低下？ 观察
Rows Removed by Filter这个值。如果它远大于最终留下的Rows（例如扫描了10万行，最终只留下3行），这几乎就是在明示：子查询缺少有效的索引支持，或者查询条件未能被优化器“下推”到最底层执行。 - 执行模式是哪种？ 留意
Subplan Name字段。如果是InitPlan，通常意味着子查询只执行一次，结果被缓存复用；如果是SubPlan，则意味着它需要为外层查询的每一行数据都执行一遍，这正是性能杀手。
把 correlated subquery 改成 JOIN 为什么常能提速
PostgreSQL优化器在处理形如 WHERE x IN (SELECT y FROM t WHERE t.a = outer.b) 这类“相关子查询”时，往往无法自动将其重写为高效的JOIN操作。尤其是当子查询中包含聚合函数、DISTINCT 或 LIMIT 子句时，自动优化的可能性就更低了。
手动改写的核心思路，其实就是把嵌套的子查询“拉平”，变成一个派生表，然后通过ON条件明确关联关系。这里有个语义细节需要注意：
- 原写法：
SELECT * FROM orders o WHERE o.customer_id IN (SELECT id FROM customers c WHERE c.status = 'active') - 简单改写：
SELECT o.* FROM orders o JOIN customers c ON o.customer_id = c.id AND c.status = 'active' - 语义辨析：如果原意是“找出所有存在活跃客户的订单”，那么用
JOIN是准确的。但如果原意是“找出客户状态为活跃的订单”，并且需要保留那些没有匹配客户的订单（虽然可能不符合业务逻辑），那就必须使用LEFT JOIN并结合WHERE c.id IS NOT NULL来过滤。 - 一个重要提醒：
IN子句会自动去重，而JOIN操作可能会因为一对多关系而导致结果行数膨胀。如果遇到这种情况，可以考虑在JOIN后加DISTINCT，或者评估是否更适合用EXISTS。
EXISTS 比 IN 快，但不是所有场景都适用
普遍认为EXISTS比IN快，这很大程度上得益于它的“短路”特性：一旦在子查询中找到一行匹配的数据，它就立刻返回真，停止继续搜索。而IN则需要完整地计算出子查询的所有结果集。当子查询结果集很小时，IN也可能很快。
但两者一个关键的区别在于对NULL值的处理，这常常是业务逻辑出现静默错误的根源：
- NULL值的陷阱：当子查询可能返回
NULL值时（例如SELECT nullable_col FROM t），IN的整个运算结果会变成UNKNOWN，导致该行被跳过。在这种情况下，使用EXISTS是更安全的选择。 - EXISTS的写法：
EXISTS不关心子查询具体返回什么列值，所以惯例是写SELECT 1。优化器也足够聪明，不会去解析这个字段列表。 - 多列匹配的限制：
EXISTS无法直接替代IN进行多列匹配。例如(a,b) IN (SELECT x,y FROM t)不能直接写成EXISTS。变通方法是使用ROW构造器：ROW(a,b) IN (SELECT ROW(x,y) FROM t)，或者干脆改写为JOIN。 - 版本差异：在某些旧版本的PostgreSQL中，对包含
LIMIT的EXISTS子查询优化不佳。这时可以尝试在子查询末尾添加OFFSET 0，以“欺骗”优化器进行物化，有时能带来性能提升。
临时表 or CTE？别无脑选 CTE
面对复杂的子查询，很多人的第一反应是把它塞进WITH子句（即CTE，公共表表达式）。但在PostgreSQL 12版本之前，CTE有一个重要特性：默认强制物化。这意味着，即使这个CTE只被外层查询引用一次，它也会被完整地执行并写入临时存储（通常是磁盘），带来额外的开销。
那么，到底该怎么选？
- CTE的适用场景：如果同一个子查询在外层被多次引用（例如同时在SELECT列表和WHERE条件中使用），并且它的结果集不大，那么使用CTE可以避免重复计算，是划算的。
- 临时表的优势：如果子查询只使用一次，且本身包含聚合或排序等重操作，优先考虑将其内联（inline），或者显式创建带索引的临时表：
CREATE TEMP TABLE tmp_foo AS SELECT ...; CREATE INDEX ON tmp_foo(col);。临时表可以执行ANALYZE，为优化器提供准确的统计信息，而CTE的统计信息始终是估算值。 - 一个隐藏的坑：临时表在会话或事务结束后会自动销毁，但在一个长事务中创建大量临时表，可能会撑满
pg_temp目录，影响系统稳定性。
最后，分享一个最容易被忽略的经验：实践中遇到的嵌套子查询性能问题，大约有80%的根源，其实在于没有为子查询内部的WHERE条件字段建立合适的索引，而不是SQL写法本身有多糟糕。所以，下次遇到慢查询，不妨先运行一遍 EXPLAIN (ANALYZE, BUFFERS)，紧紧盯住那些出现“Seq Scan”（顺序扫描）的行，再决定是否需要大动干戈地重构SQL结构。很多时候，一个恰当的索引就能让问题迎刃而解。
