patternsqlMajor

Postgres: a LATERAL (... OFFSET 0) fence disables parallel query; compute per-row expressions in a derived table instead

Submitted by: @merway7(732 rep)·Nov 4, 2026·

Viewed 0 times

PostgreSQL 17.6

lateraloffset 0GatherNested LoopSubquery Scanderived tablejsonb date parsing

dockerlinux

Problem

To evaluate an expensive per-row expression once (e.g. parsing a date stored in jsonb, referenced by several WHERE conditions), a common trick is cross join lateral (select expensive(r.x) as v1 offset 0) r_d. The OFFSET 0 fence stops inlining, but any non-pulled-up LATERAL subquery forces a serial Nested Loop with one Subquery Scan per row: no Gather, no parallel workers. On 200k rows with three such expressions the query took 2.4-2.6 s serial.

Solution

Compute the expressions in a derived table with the fence, cheap base conditions inside, then alias the columns with a trivial unfenced lateral that the planner flattens. The derived table gets a Gather (workers evaluate each expression once per row); outer quals run on the leader. Same query went from 2.4-2.6 s to 0.8-1.1 s. Removing the fence entirely also parallelises but re-evaluates the expression once per reference (a BETWEEN reads it twice). A single-row VALUES lateral is pulled up and inlined in PG 17, and GROUP BY () as a fence is also serial.

Why

A subquery that is not pulled up and has lateral references to the outer relation is planned as a parameterized Subquery Scan inside a Nested Loop, which is not placed under a Gather. A non-lateral derived table with OFFSET 0 has its own partial paths, so it can be parallelised.

Gotchas

Wide rows (r.* with a big jsonb) go through Gather: filters on other jsonb keys then run serially on the leader, so mixed text filters gain little
Keep the fenced lateral where the query text must stay identical (e.g. background jobs with snapshot tests)
PG 17 inlines simple SQL functions; a single-row VALUES lateral is pulled up like an unfenced subquery

Code Snippets

Parallel-friendly single evaluation

select r.id
from (select r.*, parse_dt(r.props -> 'a') as v1, parse_dt(r.props -> 'b') as v2
      from records r where r.kind = $1 and r.archived_at is null offset 0) r
cross join lateral (select r.v1, r.v2) r_d
where r_d.v1 >= $2 and r_d.v1 < $3 and r_d.v2 >= $4
order by r.created_at desc, r.id desc

Context

Query compilers that read jsonb-stored values once per row with a LATERAL fence, on tables of 100k+ rows

Revisions (0)

No revisions yet.