PostgreSQL自定义函数ROWS估算值为何有时被忽略？

问题本质 #

你遇到的两种查询写法触发了PostgreSQL优化器不同的处理逻辑，导致行数估算值不一致：

1. SELECT列表中调用函数 #

当直接在SELECT列表里调用generate_series_monthly时，优化器通过ProjectSet节点处理函数的集合返回结果，此时会直接读取函数定义中声明的ROWS 10作为行数估算值，所以执行计划显示rows=10。

2. FROM子句中调用函数（表函数方式） #

当用SELECT * FROM generate_series_monthly(...)的形式调用时，优化器将函数视为一个虚拟表进行扫描，但对于SQL语言的集合函数，优化器不会读取函数定义里的ROWS提示参数，而是回退到PostgreSQL默认的集合函数行数估算值（默认是1000行，由相关系统参数控制），因此执行计划显示rows=1000。

解决办法 #

要让两种调用方式都能使用自定义的行数估算，最可靠的方式是将函数改为PL/pgSQL语言——PL/pgSQL函数的ROWS参数在表函数场景下会被优化器正确识别。修改后的函数如下：

CREATE OR REPLACE FUNCTION public.generate_series_monthly(a date, b date)
RETURNS SETOF date
LANGUAGE plpgsql
IMMUTABLE PARALLEL SAFE
ROWS 10
AS
$function$
BEGIN
  RETURN QUERY
  SELECT generate_series(date_trunc('month', a), date_trunc('month', b), '1 month');
END;
$function$;

修改后重新执行两种查询，执行计划都会显示rows=10的正确估算值。

补充说明 #

• SQL语言的函数在作为表函数调用时，优化器无法解析其内部逻辑来获取行数提示，也不会读取ROWS参数；而PL/pgSQL函数的执行框架会将ROWS参数传递给优化器，用于调整行数估算。
• 尝试用ANALYZE收集函数统计信息的方式对IMMUTABLE函数无效，因为这类函数的结果完全由输入参数决定，PostgreSQL不会为其存储动态统计数据。

内容的提问来源于stack exchange，提问作者Mark Hildreth