I have created the following custom SQL function on a PostgreSQL 16.1 server:
CREATE OR REPLACE FUNCTION public.generate_series_monthly(a date, b date)
RETURNS SETOF date
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
ROWS 10
AS
$function$
select generate_series(date_trunc('month', a), date_trunc('month', b), '1 month')
$function$;
Specifically, I have added the row estimate parameter, and as expected, I am seeing this estimate in some simple queries:
# explain select generate_series_monthly('2024-01-01', '2024-05-01'); QUERY PLAN
------------------------------------------------
ProjectSet (cost=0.00..0.09 rows=10 width=4)
-> Result (cost=0.00..0.01 rows=1 width=0)
(2 rows)
However, in some uses in queries, I see it falling back to the default of 1000:
# explain select * from generate_series_monthly('2024-01-01', '2024-05-01');
QUERY PLAN
-------------------------------------------------------
Result (cost=0.00..30.03 rows=1000 width=4)
-> ProjectSet (cost=0.00..5.03 rows=1000 width=8)
-> Result (cost=0.00..0.01 rows=1 width=0)
(3 rows)
I would expect this second query to also use the 10 row estimate. Why is it resorting to 1000?
It’s because of inlining.
Scalar and table functions have different criteria for inlining – calling your table function as a table function (your second example) meets all of them. Explain verbose can show how the query got rewritten – generate_series_monthly() is gone, replaced by its internal generate_series() so the planner only uses estimates attached to generate_series(), not the _monthly wrapper:
demo at db<>fiddle
explain verbose select *
from generate_series_monthly('2024-01-01', '2024-05-01');
| -> ProjectSet (cost=0.00..5.03 rows=1000 width=8) |
| Output: generate_series(date_trunc(‘month’::text, (‘2024-01-01’::date)::timestamp with time zone), date_trunc(‘month’::text, (‘2024-05-01’::date)::timestamp with time zone), ‘1 mon’::interval) |
According to the db, you’re just calling generate_series() directly. You can check its associated row estimate in pg_catalog.pg_proc:
prorowsfloat4
Estimated number of result rows (zero if not proretset)
select prorows
from pg_proc
where oid='generate_series(timestamptz,timestamptz,interval)'::regprocedure;
| prorows |
|---|
| 1000 |
Which is the default:
ROWSresult_rows
A positive number giving the estimated number of rows that the planner should expect the function to return. This is only allowed when the function is declared to return a set. The default assumption is 1000 rows.
The select generate_series_monthly(...); example is a scalar function call, which fails to meet the 3rd condition on the list:
A scalar function call will be inlined if all of the following conditions are met (Source code):
- the function is
LANGUAGE SQL- the function is not
SECURITY DEFINER- the function is not
RETURNS SETOF(orRETURNS TABLE)
…
Explain verbose shows the call is left unaltered and you get to keep your custom estimate:
explain verbose select generate_series_monthly('2024-01-01', '2024-05-01');
| ProjectSet (cost=0.00..0.32 rows=10 width=4) |
| Output: generate_series_monthly(‘2024-01-01’::date, ‘2024-05-01’::date) |