CPU Cost Parameters and Function COST Attributes #

A query filters on a cheap integer comparison and on is_eligible(customer_id), a PL/pgSQL function that runs a lookup of its own. The plan evaluates the function first, for every row, and only then applies the integer test that would have discarded 99% of them. Nothing is wrong with the SQL: the planner simply believes the function is as cheap as the comparison, because nobody told it otherwise.

Page costs get most of the attention in the cost estimation model, but three CPU parameters and two per-function attributes decide a surprising number of plan details: which filter runs first, whether an expression is worth an index, and how many rows a set-returning function is assumed to produce.

The Cost-Model Condition #

The CPU side of the model has three global parameters, all relative to seq_page_cost = 1.0:

Parameter Default Charged for
cpu_tuple_cost 0.01 each row processed by a node
cpu_index_tuple_cost 0.005 each index entry processed
cpu_operator_cost 0.0025 each operator or function call evaluated per row

The third one is scaled per function. Every function has a COST attribute, expressed in units of cpu_operator_cost. Built-in and C-language functions default to COST 1; functions written in SQL, PL/pgSQL or other procedural languages default to COST 100. Operators inherit the cost of the function that implements them. A predicate calling a function with COST 10000 is therefore charged 10000 × 0.0025 = 25 cost units per row — the same as reading 25 pages.

That per-row price feeds three decisions:

Set-returning functions have a second attribute, ROWS, which defaults to 1000. A function in FROM returning 5 rows is estimated at 1000, which inflates every join above it — a row estimate explosion with a one-line fix.

The planner runs the cheapest filter first A stack of three filter clauses as evaluated top to bottom. First the integer comparison region equals EU at cost one. Then the text match at cost one. Last the PL/pgSQL function is_eligible at declared cost ten thousand. When the function is left at a misleading low cost, it sorts to the top and runs on every row. region_id = 4 COST 1 · evaluated first, removes 99% status = 'active' COST 1 · sees the 1% that remain is_eligible(customer_id) declared COST 10000 · runs last a function left at COST 1 would jump to the top of this list

Annotated EXPLAIN Evidence #

The function’s cost is visible only indirectly — through filter order in VERBOSE output and through total cost:

CREATE FUNCTION is_eligible(bigint) RETURNS boolean
LANGUAGE plpgsql STABLE COST 1 AS $$
BEGIN
  RETURN EXISTS (SELECT 1 FROM eligibility WHERE customer_id = $1 AND active);
END $$;

EXPLAIN (ANALYZE, VERBOSE)
SELECT id FROM orders WHERE is_eligible(customer_id) AND region_id = 4;
Seq Scan on public.orders  (cost=0.00..224120.00 rows=4100 width=8)
                           (actual time=0.09..48210.4 rows=3920 loops=1)
  Output: id
  Filter: (is_eligible(orders.customer_id) AND (orders.region_id = 4))
  Rows Removed by Filter: 8196080
Execution Time: 48211.6 ms
-- function listed first in Filter → evaluated first, 8.2M times
-- COST 1 told the planner it was as cheap as an integer comparison

After declaring a realistic cost:

ALTER FUNCTION is_eligible(bigint) COST 10000;
Seq Scan on public.orders  (cost=0.00..1238120.00 rows=4100 width=8)
                           (actual time=0.07..1410.2 rows=3920 loops=1)
  Output: id
  Filter: ((orders.region_id = 4) AND is_eligible(orders.customer_id))
  Rows Removed by Filter: 8196080
Execution Time: 1410.9 ms
-- region_id test now first; the function runs only for ~82,000 region-4 rows
-- total cost rose sharply, which also makes an index on region_id far more attractive
What to read when a function filter is slow Three plan lines are annotated. The Filter line lists the function first, meaning it runs before the cheap test. Rows Removed by Filter shows how many times the function ran for nothing. The low total cost shows the planner priced the function as trivial. Filter: (is_eligible(...) AND region_id = 4) function ordered first Rows Removed by Filter: 8196080 calls wasted on discarded rows cost=0.00..224120.00 function priced as trivial VERBOSE output shows the evaluation order the planner chose

Step-by-Step Resolution #

  1. List the functions in the slow predicate and their declared costs.

    SELECT p.proname, l.lanname, p.procost, p.prorows, p.provolatile
FROM pg_proc p JOIN pg_language l ON l.oid = p.prolang
WHERE p.proname IN ('is_eligible', 'expand_tags');

  2. Measure a function’s real per-call time in isolation:

    EXPLAIN (ANALYZE)
SELECT is_eligible(customer_id) FROM orders LIMIT 10000;
-- divide the function-attributable time by 10000 calls

  3. Convert to cost units. Compare with a known operator: if the function takes roughly 1,000 times longer than an integer comparison, COST 1000 is a fair order of magnitude. Precision is unnecessary; the order of magnitude drives the decisions.

  4. Declare it.

    ALTER FUNCTION is_eligible(bigint) COST 10000;
ALTER FUNCTION expand_tags(text) ROWS 5;   -- set-returning function, typically 5 rows

  5. Consider replacing the function call with a join. A per-row lookup function hides its query from the planner entirely; rewriting it as EXISTS or a join lets the planner see semi-join options and statistics instead of a guessed cost.

  6. Leave the global CPU parameters alone unless many plans show the same distortion. They are relative weights, and changing them shifts every plan on the server at once.

Declared cost changes evaluation order and runtime With COST 1 the function runs first and the query takes 48 seconds. With COST 100, the procedural-language default, it still takes 1.5 seconds because order changes. With COST 10000 it takes 1.4 seconds and makes an index path on region_id the cheapest plan. COST 1 48.2 s — function evaluated first COST 100 (PL default) 1.5 s — cheap test first COST 10000 1.4 s — index path now attractive ALTER FUNCTION … COST changes plans without touching the SQL

Before and After #

-- BEFORE: is_eligible declared COST 1
Filter: (is_eligible(orders.customer_id) AND (orders.region_id = 4))     Execution Time: 48211.6 ms

-- AFTER: ALTER FUNCTION is_eligible(bigint) COST 10000
Filter: ((orders.region_id = 4) AND is_eligible(orders.customer_id))     Execution Time: 1410.9 ms

Why the function was declared COST 1 #

PL/pgSQL functions default to COST 100, so a COST 1 declaration was an explicit choice — usually copied from a template or written when the function was a simple expression that later grew a query inside it. The attribute is never revisited automatically, and nothing warns when a function’s real cost drifts away from its declaration. Search your schema for procedural-language functions declared below the default:

SELECT n.nspname, p.proname, p.procost
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE l.lanname IN ('plpgsql', 'sql') AND p.procost < 100
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

The same audit is worth running for prorows on set-returning functions. Many return a handful of rows but keep the default estimate of 1000, and every join against them inherits a thousand-fold overestimate. SQL-language set-returning functions that are simple enough to be inlined avoid the problem entirely, because the planner plans their body instead of treating them as opaque.

Common Pitfalls #

Blaming the planner for filter order. The order follows declared costs. Diagnostic signal: an expensive function listed first in Filter under VERBOSE. Fix: declare an honest COST.

Tuning cpu_operator_cost globally for one function. It rescales every operator in every plan. Diagnostic signal: widespread plan changes after a config edit. Fix: change the one function’s COST.

Ignoring ROWS on set-returning functions. The default of 1000 distorts joins above the function. Diagnostic signal: Function Scan … rows=1000 with actual rows in single digits. Fix: ALTER FUNCTION … ROWS n, or a planner support function for functions whose output size varies.

Hiding queries inside functions. A function that runs a query per row can never be planned as a join. Diagnostic signal: a filter function whose runtime is dominated by lookups. Fix: express the lookup in SQL as EXISTS or a join.

Frequently Asked Questions #

What is the default COST of a function in PostgreSQL? #

Functions implemented in C and internal built-ins default to COST 1. Functions in SQL, PL/pgSQL and other procedural languages default to COST 100. The value is in units of cpu_operator_cost per call.

Does function COST affect the order filters run in? #

Yes. Within a scan’s filter, the planner orders restriction clauses by estimated per-row cost, so cheaper clauses run first. An under-declared expensive function can be evaluated before cheap tests that would have eliminated most rows.

What does the ROWS attribute of a function do? #

For set-returning functions it sets the estimated number of rows per call, 1000 by default. The estimate feeds joins and aggregates above the function scan, so a wrong value distorts the whole plan above it.

Up: Cost Estimation Models