PostgreSQL Custom Functions: SQL vs PL/pgSQL, Inlining, and Why Your Function Might Be Killing Performance

A custom function that looks trivial can be the single biggest performance problem in a query, while an identical-looking one costs nothing. The difference is invisible in the call site — minha_funcao(id, data) reads the same either way — but very visible in EXPLAIN. This post covers the two decisions that actually matter: the function's LANGUAGE, and its volatility category.

Two Ways to Define a Function

-- SQL language: a single statement, no procedural logic
CREATE FUNCTION localizacao_atual(p_animal_id bigint, p_data date)
RETURNS bigint
LANGUAGE sql
STABLE
AS $$
  SELECT localidade_id
  FROM movimentacoes
  WHERE animal_id = p_animal_id AND data_movimentacao <= p_data
  ORDER BY data_movimentacao DESC
  LIMIT 1
$$;

-- PL/pgSQL: procedural, supports IF/LOOP/exceptions, multiple statements
CREATE FUNCTION localizacao_atual(p_animal_id bigint, p_data date)
RETURNS bigint
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN (
    SELECT localidade_id
    FROM movimentacoes
    WHERE animal_id = p_animal_id AND data_movimentacao <= p_data
    ORDER BY data_movimentacao DESC
    LIMIT 1
  );
END;
$$;

Both return the same value for the same input. Their execution paths are not remotely the same.

Inlining: The Feature That Changes Everything

When a LANGUAGE sql function contains exactly one statement and is marked IMMUTABLE or STABLE, PostgreSQL's planner can inline it — literally substitute the function body into the surrounding query as if you had written it as a subquery or join by hand. Once inlined, the planner sees straight through it: it can push down predicates, choose to use an index on movimentacoes(animal_id, data_movimentacao), and even fold the call into a LATERAL join in the execution plan.

LANGUAGE plpgsql functions are never inlined, regardless of volatility. They are opaque to the planner — a black box that gets called once per input tuple, with no visibility into what it reads or how selective it is. Even a one-line PL/pgSQL wrapper around a single SQL statement pays this cost.

Volatility: STABLE, IMMUTABLE, VOLATILE

Volatility is a promise you make to the planner about the function's behavior, and it determines what optimizations are legal:

• VOLATILE (the default if unspecified) — may return different results for the same input even within one statement, and may have side effects (writes, sequence advances). The planner must call it for every row, every time, in the exact order rows are produced. No caching, no reordering, not parallel-safe by default.
• STABLE — same result for the same input within a single statement (it may still depend on the current transaction snapshot or session settings). Safe to evaluate once per statement, safe to use in index conditions on a plain scan, safe for parallel workers.
• IMMUTABLE — same result for the same input, always, regardless of any database state. Can be used in a functional index, can be constant-folded at parse time if the arguments are literals.

Mislabeling matters both ways: marking a function STABLE when it actually depends on mutable state produces wrong results after the planner caches or reorders it. Leaving a genuinely read-only function VOLATILE (the default) needlessly blocks inlining eligibility, parallel query, and per-statement caching.

Turning a Per-Row Function Call Into a LATERAL Join

Even an inlinable LANGUAGE sql STABLE function called once per row of a large set is still, functionally, a nested loop. Making that explicit with LATERAL gives you a plan you can read and index against directly, with or without the function wrapper:

-- Opaque: works, but hides the join shape from you
SELECT e.animal_id, localizacao_atual(e.animal_id, p.data_estoque) AS localidade_id
FROM parametros p
JOIN animais_do_contrato(p.contrato_id, p.data_estoque) e ON TRUE;

-- Explicit LATERAL: same result, visible index scan per animal
SELECT e.animal_id, m.localidade_id
FROM parametros p
JOIN animais_do_contrato(p.contrato_id, p.data_estoque) e ON TRUE
CROSS JOIN LATERAL (
  SELECT localidade_id
  FROM movimentacoes
  WHERE animal_id = e.animal_id AND data_movimentacao <= p.data_estoque
  ORDER BY data_movimentacao DESC
  LIMIT 1
) m;

The second form is what an inlined LANGUAGE sql function effectively becomes internally — writing it explicitly just makes the plan legible without needing \df+ to remember what the function does.

The Supporting Index

None of the above helps without the right index for the per-animal lookup:

CREATE INDEX idx_movimentacoes_animal_data
  ON movimentacoes (animal_id, data_movimentacao DESC);

Confirm it is actually used with EXPLAIN (ANALYZE, BUFFERS) — you want an Index Scan feeding the LATERAL, not a Seq Scan re-reading movimentacoes for every animal.

Checklist Before Shipping a New Function

• Single statement, no procedural control flow? Use LANGUAGE sql, not plpgsql.
• Read-only and deterministic within a statement? Mark STABLE.
• Read-only and deterministic against any database state? Mark IMMUTABLE, and consider it for a functional index.
• Called once per row of a large set? Verify whether it gets inlined (EXPLAIN should show the underlying table, not an opaque Function Scan) and check that a matching index exists on the table it queries internally.

Catching This in Production

Function-call performance regressions are especially sneaky because the calling query's text never changes — only the function body, or a missing statistics refresh, does. PG Monitoring fingerprints queries by their normalized text and tracks execution plan and timing history per fingerprint, so a function that quietly stops being inlined (say, after someone "helpfully" rewrote it in PL/pgSQL) shows up as a plan and latency regression on the same query, not as a mystery.