SubPlan & InitPlan

A SubPlan is a sub-SELECT executed in the context of its parent — often once per outer row (e.g. a correlated subquery or EXISTS). An InitPlan is a sub-SELECT with no outer dependency, so it runs once and its result is reused (e.g. an uncorrelated scalar subquery).

For subqueries the planner can't turn into joins: correlated subqueries and some EXISTS/IN become SubPlans; uncorrelated scalar subqueries become InitPlans.

An InitPlan is cheap — computed once. A correlated SubPlan re-run for every outer row can be very expensive; rewriting it as a JOIN or using EXISTS often lets the planner avoid the per-row execution.

Two flavors of sub-SELECT

A sub-SELECT that the planner couldn't merge into the surrounding query appears as one of two related nodes (really, plan attachments):

• InitPlan — uncorrelated. It depends on nothing from the outer query, so it runs once and its result is reused. The poster child is a scalar subquery like WHERE x = (SELECT MAX(x) FROM t).
• SubPlan — correlated. It references the outer query's current row, so it's evaluated per outer row (or per distinct value, with caching). A correlated EXISTS or IN that the planner couldn't transform into a semi-join is the typical case.

EXPLAIN shows them as named blocks attached to the plan ("InitPlan 1 (returns $0)", "SubPlan 2") with their own subtree.

InitPlan: usually cheap

An InitPlan runs once at plan startup. The cost is whatever the subquery costs; the parameter feeds into the outer plan as a constant. Nothing special to tune.

SubPlan: the dangerous one

A correlated SubPlan is the per-row engine that can quietly dominate runtime. A simple SELECT count(*) FROM t WHERE EXISTS (SELECT 1 FROM big WHERE big.id = t.id) can become a SubPlan over big that runs once per row of t — a giant nested loop in disguise.

Two angles to attack it:

1. Let the planner convert it to a join. EXISTS / IN with non-trivial subqueries are often transformable into a semi-join — make sure the subquery isn't fenced by OFFSET, LIMIT, or volatile functions that block the rewrite.
2. Rewrite as an explicit join with EXISTS / LATERAL. Sometimes the easiest cure is to express the subquery as a real LEFT JOIN or a LATERAL clause; the planner has more freedom to pick a hash or merge join.

Our analyzer flags these via the "Nested Loop rescans …" finding when the inner is a re-executed subplan.

• Nested Loop rescans — a correlated subplan re-evaluated many times

Paste a plan into the analyzer →

• Nested Loop Join
• CTE Scan
• Subquery Scan