patternsqlModerate

Postgres re-evaluates a stable function on a constant filter value for every row; wrap it in a scalar subquery (InitPlan)

Submitted by: @merway7(494 rep)·Oct 29, 2026·

Viewed 0 times

PostgreSQL 17

InitPlanstable functionconstant foldingper-row evaluationscalar subqueryinlining

dockernodejs

Problem

A generated filter like col_reader(r.props -> $1, $2) between parse_value(to_jsonb($3::text), $2) and parse_value(to_jsonb($4::text), $2) was 10-20x slower than expected on 200k rows. The right-hand side only depends on parameters, but because the parsing function is STABLE (or calls a STABLE/plpgsql helper, and to_jsonb itself is stable), the planner cannot constant-fold it, so the executor recomputes it for every scanned row (and twice for BETWEEN).

Solution

Wrap the parameter-only expression in an uncorrelated scalar subquery: ... between (select parse_value(to_jsonb($3::text), $2)) and (select parse_value(to_jsonb($4::text), $2)). Postgres turns it into an InitPlan evaluated once per query, and the plan keeps its parallel seq scan (Gather Merge). Measured: 1.6-2.1 s down to 0.31-0.42 s on 200k rows. EXPLAIN shows InitPlan 1 -> Result. Only immutable expressions with constant inputs are folded at plan time; stable ones are not.

Why

Constant folding at plan time only applies to immutable functions. Inlined SQL functions expand into the filter, so a stable function body (and any plpgsql call inside it) runs once per row per reference.

Gotchas

BETWEEN expands to two comparisons, so the bound expressions run twice per row
Temp tables never get parallel scans, so measure on the real table to compare plans
An unused untyped bind parameter makes Postgres fail with 'could not determine data type of parameter $n': build such expressions lazily

Code Snippets

Bound parsed once per query

select count(*) from t r
where read_value(r.props -> $1, $2::text)
  between (select read_value(to_jsonb($3::text), $2::text))
      and (select read_value(to_jsonb($4::text), $2::text));

Context

Query builders that compare a stored value read by an inlined SQL function against a filter value parsed by the same function from a bind parameter.

Revisions (0)

No revisions yet.