PostgreSQL引入LATERAL JOIN后出现严重性能损耗问题排查

1. 优化器的"黑盒"限制 #

PostgreSQL查询优化器可以对原生SQL子查询（比如你的EXISTS语句）做深度优化：比如将EXISTS转换为半连接（Semi Join），利用索引快速判断记录存在性，甚至能根据外部查询的过滤条件裁剪子查询的执行范围。但PL/pgSQL函数对优化器来说是完全的黑盒——优化器无法看穿函数内部的SQL逻辑，也就无法将其与外部查询做联合优化，只能逐行调用函数。

2. 从集合式查询退化为行级循环 #

原EXISTS子查询是集合式操作，优化器可以一次性处理所有匹配逻辑；而用LEFT JOIN LATERAL调用函数时，函数会被外部查询的每一行触发一次调用。如果外部查询返回1000行，函数就会执行1000次，叠加的执行开销直接导致总耗时暴涨。

3. PL/pgSQL函数的固有开销 #

PL/pgSQL作为过程式语言，每次调用都涉及上下文切换、变量初始化等额外开销，单次调用可能只有几毫秒，但多次累加后就会被放大成显著的性能损耗。

• 优先用原生SQL逻辑替代自定义函数：除非业务逻辑极度复杂无法用纯SQL表达，否则尽量用子查询、CTE或视图实现逻辑——这些结构能被优化器充分解析和优化，避免黑盒问题。
• 优先选择SQL函数而非PL/pgSQL函数：如果必须用函数，优先写纯SQL函数（LANGUAGE sql）。PostgreSQL对SQL函数支持内联优化（类似视图），能让优化器看穿内部逻辑，保留集合式优化的可能。
• 提前测试函数的单调用开销：单独调用几次函数，记录单次执行时间，再结合外部查询的预估行数，计算总耗时是否在可接受范围。比如单次函数调用耗时5ms，外部查询返回1000行，总耗时至少5000ms，提前就能预判性能问题。
• 给函数标记正确的稳定性属性：如果函数的返回结果仅依赖输入参数且在事务内不变，标记为STABLE；如果完全不依赖外部状态，标记为IMMUTABLE。优化器会对这类函数做结果缓存，减少重复调用的开销（默认的VOLATILE属性会强制每次调用重新执行）。
• 对比执行计划差异：在改写前，先用EXPLAIN查看原查询和改写后查询的执行计划。如果原计划有Semi Join、Index Scan等高效操作，而新计划出现大量Function Scan或嵌套循环下的反复函数调用，大概率会出现性能问题。

内容的提问来源于stack exchange，提问作者Luca Blanchi