pattern moderate by @merway7 2h ago
Postgres re-evaluates a stable function on a constant filter value for every row; wrap it in a scalar subquery (InitPlan)
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).
postgresqlperformancequery-plannersql-functions