SQL JOIN关联查询远慢于单查询分开执行的性能优化问题

两个单查询执行速度正常，JOIN后耗时陡增，核心是数据库优化器对集合返回函数f_permission_form(1,2,3)的执行逻辑、返回规模判断出错，通常是两类原因导致：

• 优化器无法准确估算该函数的返回行数，会默认按大结果集生成执行计划，放弃form.key上的索引，转而选择全表扫描+哈希/合并连接的执行路径，当form表数据量较大时耗时会明显上涨。
• 自定义函数默认的易变性等级为VOLATILE，该标记会告诉优化器“函数每次调用返回结果都可能变化”，导致优化器不会提前一次性执行函数拿到完整结果，而是在遍历form表每一行时重复调用一次权限函数，调用次数和form表行数一致，性能会随表数据量上涨线性下跌。

1. 强制物化函数结果，固定执行顺序 #

通过CTE强制数据库先执行权限函数拿到小结果集，再用这个结果集做驱动表关联form表，走key字段的索引做匹配，执行效率和你手动查完函数结果再拼IN查询的速度基本一致。

• PostgreSQL 12及以上版本可以直接用MATERIALIZED关键字显式物化结果：

WITH permission_keys AS MATERIALIZED (
    SELECT * FROM f_permission_form(1,2,3)
)
SELECT f.* FROM form f
JOIN permission_keys pk ON f.key = pk.key;

• 低版本可以用OFFSET 0的写法阻止优化器下推函数逻辑，达到同样的物化效果：

WITH permission_keys AS (
    SELECT * FROM f_permission_form(1,2,3) OFFSET 0
)
SELECT f.* FROM form f
JOIN permission_keys pk ON f.key = pk.key;

2. 修正函数的易变性标记 #

如果f_permission_form在入参固定时，同一次查询周期内返回的权限结果不会发生变化，将函数标记为STABLE级别，优化器就会自动选择提前执行函数、避免逐行调用，同时也能生成更合理的执行计划：

ALTER FUNCTION f_permission_form(int, int, int) STABLE;

改完之后即使不手动加CTE物化，大部分场景下优化器也能选出正确的执行计划。

3. 注意避坑 #

不要直接把JOIN改成WHERE key IN (SELECT key FROM f_permission_form(1,2,3))的写法，如果函数易变性没修正、优化器行数估算偏差的问题没解决，这个写法一样会出现慢查询问题。

内容的提问来源于stack exchange，提问作者kylie.zoltan