Measure and Review Per‑Row Function Cost in Queries

A function called per row can dominate a query even when its single-call cost looks harmless; test cardinality, selectivity, execution placement, and alternatives.

Quantify the variables: rows, selectivity, and placement

Start with three concrete numbers: estimated input rows, selectivity (fraction of rows that reach the function), and where the function runs in the plan (filter, projection, join, ORDER BY). Estimate row count from the query predicates (EXPLAIN’s row estimates, catalog statistics, or a small COUNT run). Multiply estimated rows by selectivity to get rows_executed — that’s the multiplier that turns an innocuous microsecond into seconds or minutes.

Failure mode: trusting planner estimates without sampling. If histograms are stale or predicates are nonstandard, run targeted COUNTs or a sampling query to get real cardinality before trusting a cost model.

Measure the function cost independently

Treat the function like any other component: benchmark it. Run the function alone against representative inputs and measure wall time, CPU, and allocations:

• For SQL-only functions, use EXPLAIN (ANALYZE, BUFFERS) on a small query that calls the function repeatedly.
• For PL/pgSQL or procedural functions, build a microbenchmark SELECT myfn(v) FROM generate_series(1,N) with N in the thousands; time it outside the full query.
• Use pg_stat_statements where available to observe aggregate_time and calls; divide to get average per-call time.

Record median and p95 latencies, and whether the function does I/O, locking, or external calls. If per-call variance is high, plan for worst-case tail behavior. Compute projected_total_cost = avg_call_time * rows_executed. If projected_total_cost dominates observed query time, the function is the suspect.

Inspect the execution plan: confirm it runs per row

Run EXPLAIN ANALYZE (or your engine’s equivalent) and find where the function appears. Look for:

• Function appearing in a Seq Scan, Hash Join, or Nested Loop inner side — nested loops multiply calls.
• Number of actual calls reported by the planner (Postgres reports this for functions in some cases) or infer from node rows and loops.
• Whether the function is evaluated before or after a selective filter — functions in WHERE or JOIN conditions may reduce rows if they short-circuit, or they may be on the inner loop and executed many times.

Acceptance criteria: the plan must show the function invoked approximately rows_executed times. If the plan differs, instrument the database (session logging, pg_stat_statements) to measure actual calls during a test run.

Alternatives: precompute, index, or move work — tradeoffs and costs

Common mitigations, with tradeoffs:

• Precompute (materialized column / persisted computed column / ETL): moves CPU from read to write. Cost: increased write latency, storage, staleness, and a maintenance window or backfill.
• Index the computed value or expression index: great when the function produces a discrete, selective value used in predicates. Cost: index bloat, slower writes, and complexity if the function is non-deterministic.
• Move work to the caller or batch process: shift blast radius away from the database; good when freshness can be relaxed. Cost: added operational complexity and potential client-side inconsistency.
• Cache results (in-process, CDN, Redis, materialized view): reduces repeated computation at the cost of cache invalidation complexity and memory.
• Push logic into the planner-friendly subset: replace complex procedural code with set-based SQL or window functions where the engine can optimize group-level work.

Decision test: if avg_call_time * rows_executed > 20–30% of target query latency or resources, consider an alternative. Tail cases may push this threshold lower.

A practical review checklist (reuse this every time)

1. Estimate row count: run a sampled COUNT with the exact WHERE clause or inspect table stats. Record rows_input.
2. Determine selectivity: measure fraction that reaches the function (small probe query). Compute rows_executed = rows_input * selectivity.
3. Benchmark function: microbenchmark inputs and capture avg, p95, and whether it issues I/O or external calls.
4. Project total cost: projected_total_cost = avg_call_time * rows_executed. Compare to observed query time.
5. Inspect plan: EXPLAIN ANALYZE, confirm calls ≈ rows_executed and check for nested loop multiplication.
6. Run a dry-run mitigation: create a computed column or an index on a test replica and re-run the query.
7. Validate: run under representative load, monitor for changed write latency or storage.
8. Roll out with a rollback plan: feature-flag or phased rollout; have a backout script to drop the index or revert the computed column.

Use this checklist as the acceptance criteria for a change: query latency improved, write penalty acceptable, and data freshness within agreed bounds.

Rollout, validation, and the blast radius you own

Always do an isolated dry run. On a replica or a maintenance window:

• Implement the cheapest mitigation (expression index or persisted column) and re-run the EXPLAIN ANALYZE.
• Run the query under a synthetic load that mimics concurrency and measure end-to-end latency and resource usage (CPU, IO, lock contention).
• Watch write latency and WAL size for persistence-based fixes.

Plan rollback: dropping an index is fast; reversing a persisted computed column or a backfill may not be. If you must backfill, do it in small chunks with progress tracking and a quota so you can stop without leaving the system in a broken state.

Failure modes to document: stale precomputed values, index bloat, increased write latency, planner choosing a different plan under new statistics, or degraded performance for other queries that share the same indexes or I/O path.

Grounded takeaway

A per-row function is only cheap until you multiply it by cardinality and plan shape. Measure the three things that matter — rows, per-call cost, and execution placement — then run the checklist above to choose between precompute, index, caching, or moving work. Each alternative has a clear tradeoff: cost shift (reads → writes), storage, or staleness. Use dry runs and rollout guardrails so the blast radius is reversible. If uncertain, prefer an index or materialized column on a test replica and validate with EXPLAIN ANALYZE before touching production.

For a short, practical aid you can reuse during reviews, copy the checklist and make it part of your change template. If you want a quick peer review checklist adapted to Postgres EXPLAIN outputs, I can draft one for your environment — /contact