PostgreSQL函数如何自动感知外层查询的LIMIT限制优化性能

外层LIMIT无法对PL/pgSQL表函数生效的核心原因是：PL/pgSQL编写的表函数默认采用全量物化执行逻辑——执行RETURN QUERY时，PostgreSQL会把内部查询的所有结果全部计算完成、缓存到内存中，之后才会开始向外层查询返回数据。此时外层的LIMIT只能在所有结果计算完成后做截断，完全无法提前终止内部的高开销查询。
PostgreSQL执行器本身确实会向被调用的表函数传递外层期望返回的行数（即外层LIMIT设置的值），但PL/pgSQL默认的执行逻辑不会利用这个参数做优化。

如果函数逻辑不需要PL/pgSQL的过程化特性（比如中间变量、多步条件分支、写操作等），直接将函数改写为SQL语言函数即可，优化器会自动对函数做内联处理，外层的LIMIT会直接下推到函数内部查询，不需要额外传入limit参数：

CREATE OR REPLACE FUNCTION 
  myschema.expensive_fn(search_term text) 
  RETURNS TABLE(
    objectid integer, 
    geom geometry
  ) 
AS 
$$ 
  -- 直接写入原慢查询即可，不需要套PL/pgSQL的BEGIN/END块
  SELECT [... slow query here...];
$$ 
LANGUAGE sql STABLE PARALLEL SAFE;

这种写法的优势：

• 外层添加的任何LIMIT n都会自动作用到内部查询，内部查询拿到n行结果就会立刻终止执行，性能和在查询内部直接写LIMIT完全一致
• 兼容自动追加LIMIT的服务，不会出现重复写LIMIT的冗余问题
• 优化器可以做更多全局优化：比如外层的WHERE过滤条件、ORDER BY规则也会被下推到内部查询，而不是全量返回结果后再做处理

内联优化生效需要满足两个前提：

• 函数易变性标记为STABLE或IMMUTABLE（现有配置已经满足）
• 函数体为单个SELECT查询，不包含多步过程化逻辑

改写完成后可以用EXPLAIN ANALYZE验证：执行计划中会直接出现Limit节点，下方挂载函数内的原始查询，实际执行耗时和直接给慢查询加对应LIMIT的耗时一致。

如果函数逻辑复杂，必须依赖PL/pgSQL的过程化能力，可以通过游标+增量返回的方式实现流式输出，外层LIMIT取到足够行数后会自动终止函数执行，不需要额外传入limit参数：

CREATE OR REPLACE FUNCTION 
  myschema.expensive_fn(search_term text) 
  RETURNS TABLE(
    objectid integer, 
    geom geometry
  ) 
AS 
$$ 
DECLARE
  cur CURSOR FOR SELECT [... slow query here...];
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO objectid, geom;
    EXIT WHEN NOT FOUND;
    RETURN NEXT; -- 每计算出一行就立刻向外返回，不缓存全量结果
  END LOOP;
  CLOSE cur;
END; 
$$ 
LANGUAGE plpgsql STABLE PARALLEL SAFE;

这种写法下，外层查询每拉取一行，函数才会执行游标取下一行的逻辑，当外层LIMIT取够需要的行数后，会直接关闭游标、终止函数执行，不会跑完整个慢查询。注意这种写法的性能比SQL内联函数略差，仅在必须使用PL/pgSQL的场景下选用。

目前通过入参传递limit值的写法存在明显问题：

• 写法冗余，和外层自动追加的LIMIT重复
• 容易出现入参值和外层LIMIT不一致的问题，要么多计算数据浪费性能，要么返回行数不足
• 无法适配动态变化的LIMIT场景

内容的提问来源于stack exchange，提问作者Ian Dickinson