skip to content

In PostgreSQL, which CREATE FUNCTION attributes stop a LANGUAGE sql set-returning function from being inlined, and what do ROWS, COST and PARALLEL change when it is not?

level: seniorimportance: nice to knowfreq 24%

answer

  1. the planner can splice the body in
  2. Function Scan is a black box
  3. default 1000 rows, cost 100
  4. STRICT, VOLATILE, SECURITY DEFINER, SET
  5. PARALLEL UNSAFE forces a serial plan

basics

~20 s

A LANGUAGE sql set-returning function is inlined only if its body is one SELECT and it is not STRICT, VOLATILE, SECURITY DEFINER or given a SET clause; otherwise it is an opaque Function Scan costed from ROWS (default 1000) and COST.

solid answer

~50 s

When a `LANGUAGE sql` function returning `SETOF` or `TABLE` is called in `FROM`, the planner can replace the call with the function's query, so outer conditions are pushed in, indexes on the underlying table are used and estimates come from real statistics. It refuses when the function is `STRICT`, `VOLATILE`, `SECURITY DEFINER` or has a `SET` clause, when the body is not a single `SELECT`, when an argument contains a volatile function or a subquery, or when `WITH ORDINALITY` is used; PL/pgSQL functions are never inlined. A non-inlined call becomes a `Function Scan`: the planner assumes `ROWS` rows, 1000 by default, and `COST` per row, 100 units of `cpu_operator_cost` for non-C languages. Realistic `ROWS` and `COST` values fix join choices, and `PARALLEL SAFE` lets the surrounding query use parallel workers, which the default `PARALLEL UNSAFE` forbids.

code

sql · 20 lines
sql
-- Inlinable: SQL, STABLE, not STRICT, single SELECT
CREATE FUNCTION account_statement(p_account_id bigint)
RETURNS SETOF ledger_entry
LANGUAGE sql STABLE PARALLEL SAFE
AS $$
  SELECT * FROM ledger_entry WHERE account_id = p_account_id
$$;

-- The outer filter merges with the body, so an index on
-- ledger_entry (account_id, booked_on) can serve both conditions
EXPLAIN SELECT * FROM account_statement(42)
 WHERE booked_on >= date '2026-09-01';

-- STRICT blocks inlining: give the planner honest numbers instead
CREATE FUNCTION account_statement_strict(p_account_id bigint)
RETURNS SETOF ledger_entry
LANGUAGE sql STABLE STRICT PARALLEL SAFE ROWS 200
AS $$
  SELECT * FROM ledger_entry WHERE account_id = p_account_id
$$;

go deeper

for a junior

Recall that EXPLAIN shows a Function Scan when the planner cannot see into a function, and that ROWS defaults to 1000.

for a middle

Explain what inlining does for a LANGUAGE sql function and list the attributes that prevent it: STRICT, VOLATILE, SECURITY DEFINER and a SET clause.

for a senior

Show how you fix a bad plan around a set-returning function: make it inlinable, or set honest ROWS, COST and PARALLEL labels, then confirm with EXPLAIN.

for a principal

Discuss when wrapping queries in functions is worth the planner opacity compared with views or plain SQL, and how a team keeps function labels honest as bodies change.

## Inlining: the planner reads the body **Inlining** means the planner substitutes a `LANGUAGE sql` function's body into the calling query, as if the caller had written the SQL directly. The function call disappears from the plan; its query becomes a subquery that the planner can flatten, push conditions into, and plan with the underlying tables' statistics and indexes. Only SQL-language functions qualify, because only their bodies are queries the planner can read. PL/pgSQL, PL/Python and C functions always run as opaque calls. ## Conditions for a set-returning function A function returning `SETOF ledger_entry` or `TABLE (...)`, called in `FROM`, is inlined only when **all** of these hold: 1. It is `LANGUAGE sql`, a plain function (not a procedure or aggregate), declared to return a set, and not `SETOF void`. 2. It is **not `STRICT`**: the planner could not enforce return-nothing-on-NULL once the body is spliced in. 3. It is **not `VOLATILE`**: a volatile function must run with its own snapshot. `STABLE` or `IMMUTABLE` is required, and because `VOLATILE` is the default, forgetting the label is enough to block inlining. 4. It is **not `SECURITY DEFINER`** and has **no `SET` clause**, because both change the execution environment of the body. 5. The body is exactly **one `SELECT`** after rewriting. 6. The call's arguments contain no volatile functions and no subqueries, the call is not `WITH ORDINALITY`, and the caller has `EXECUTE` on the function. Scalar `LANGUAGE sql` functions have a parallel rule set: the body must be a single expression, the function must not be `SECURITY DEFINER`, carry a `SET` clause or return `record`, the body must be no more volatile than the declared label, and a `STRICT` function inlines only when its body is strict too. ## When inlining fails: Function Scan and its labels A non-inlined set-returning function appears in `EXPLAIN` as a `Function Scan`. The planner cannot see what it does, so it relies on labels declared with the function: | Attribute | Default | What the planner does with it | |---|---|---| | `ROWS n` | 1000 | assumes the function returns `n` rows; allowed only on set-returning functions | | `COST n` | 1 for C and internal functions, 100 for other languages | per-call cost (per row for set-returning functions) in units of `cpu_operator_cost` | | `PARALLEL` | `UNSAFE` | `UNSAFE` forces a serial plan for the whole statement; `RESTRICTED` keeps the call in the leader; `SAFE` lets workers run it | A statement for a single account that really returns 40 rows but is estimated at 1000 can drive the planner to a hash join or a sequential scan where a nested loop over an index was right. Setting `ROWS` near reality is a cheap fix when the function cannot be inlined. `COST` matters for scalar functions in `WHERE`: the planner orders filter conditions by cost, so a high `COST` on an expensive check makes it run only on rows that passed the cheap tests. `PARALLEL SAFE` is a promise as strong as a volatility label. A function that writes data, uses sequences or changes settings must stay `UNSAFE`; one that touches temporary tables, cursors or other backend-local state is at most `RESTRICTED`. A wrong `SAFE` label can raise errors or produce wrong answers in a parallel plan. How parallel plans are chosen belongs to the planner-settings topic. ## Detecting it - `EXPLAIN` shows `Function Scan on account_statement` when the call was not inlined, and scans of `ledger_entry` when it was. - With `track_functions` set to `all`, `pg_stat_user_functions` counts calls of SQL functions, but inlined calls are not tracked at all, so a function that never shows up there is being inlined. ## Making a function inlinable - Declare the honest volatility explicitly; a read-only lookup is usually `STABLE`, and leaving the default `VOLATILE` blocks inlining. - Drop `STRICT` from set-returning SQL functions and handle `NULL` arguments in the `WHERE` clause instead, where a comparison with `NULL` already yields no rows. - Move privilege needs out: a set-returning function that must be `SECURITY DEFINER` or carry `SET search_path` will never be inlined, so give it honest `ROWS` and `COST` instead. - Keep the body a single `SELECT`; a body with several statements, or a data-modifying one, stays a black box. ## The PostgreSQL 18 note PostgreSQL 18 improved plan caching for SQL-language functions, which helps the ones that cannot be inlined. Inlining remains the bigger win, because it lets the planner optimise the function's query together with the caller's.

  • Why are PL/pgSQL set-returning functions never inlined, and what does that mean for a filter written outside the call?
    Inlining substitutes a query tree, and only `LANGUAGE sql` bodies are queries the planner can read; a PL/pgSQL body is procedural code run by its interpreter. A `WHERE` clause outside the call is applied only after the function has produced its rows, so a selective filter belongs in the function's parameters.
  • When is raising COST on a scalar function useful?
    `COST` is in units of `cpu_operator_cost` and defaults to 100 for non-C functions. The planner orders filter conditions by estimated cost, so raising `COST` on an expensive check, such as a fraud-score function, makes it run after cheaper conditions have already discarded most rows.

  • Marking a SQL set-returning function STRICT helps the planner inline it.
  • A PL/pgSQL function with a single RETURN QUERY is inlined like SQL.
  • ROWS changes how many rows the function actually returns.
  • New functions are PARALLEL SAFE unless they write to a table.
  • COST is measured in milliseconds of execution time.

Assumes PostgreSQL 18, whose release notes list improved plan caching for SQL-language functions; that helps non-inlined calls but does not replace inlining.