Function Scan

A Function Scan executes a set-returning function used as a table — e.g. FROM generate_series(...) or FROM unnest(array) — and streams its rows.

Whenever a function appears in the FROM clause.

Fine, but the planner often has poor row estimates for functions (it guesses ~1000 rows by default). For big or hot queries, add a ROWS estimate to the function or materialize its output so downstream joins are planned well.

When a function is a table

A Function Scan runs a set-returning function placed in the FROM clause and streams its rows like a table:

SELECT * FROM generate_series(1, 1000) AS g(i);
SELECT * FROM unnest(my_array)         AS u(value);
SELECT * FROM my_function(arg)         AS m;

The function executes when the plan starts (or when its parent first asks for rows) and its output is consumed lazily — there's no full materialization unless the planner adds a Materialize above it.

The estimate problem

The planner has no statistics for ordinary set-returning functions: it falls back to a fixed guess of about 1000 rows. That guess is fine for generate_series(1, 10) (where it's wildly off but the cost is tiny) and catastrophic for my_etl_function(arg) that returns a million rows (where the planner builds a plan tuned for 1000 — usually a Nested Loop that blows up).

Two fixes:

1. Declare row counts on the function:

   CREATE FUNCTION my_func() RETURNS SETOF events AS $$ … $$
     LANGUAGE plpgsql
     ROWS 1000000;
   

2. Materialize the result yourself into a TEMP TABLE, ANALYZE it, and join the table. The planner then has real statistics for downstream choices.

Built-in helpers

generate_series, unnest, jsonb_to_recordset, regexp_split_to_table and friends all appear as Function Scan. They're efficient — the planner issue is the estimate, not the execution.

• Bad row estimate — function output is hard to estimate

Paste a plan into the analyzer →

• ProjectSet
• Values Scan
• Table Function Scan