Why PostgreSQL Shows InitPlan and One‑Time Filter in Self‑Join Queries

Problem description

A table test with two integer columns is created and populated:

CREATE TABLE test(id1 int, id2 int);
INSERT INTO test VALUES (1,3),(2,1),(3,1),(3,3);
SELECT * FROM test;

The original business query checks, for each row, whether a related row exists using a correlated EXISTS sub‑query:

SELECT (EXISTS (SELECT 1 AS one FROM test a WHERE (test.id1 = a.id2))) AS b FROM test;

Running EXPLAIN shows a sequential scan with a SubPlan. Executing the inner sub‑query alone fails because the outer reference test.id1 is missing.

Rewriting the query

Changing the alias order so that the outer table is referenced inside the sub‑query:

SELECT (EXISTS (SELECT 1 AS one FROM test a WHERE (a.id1 = test.id2))) AS b FROM test;

This returns all true values and still produces a Seq Scan with a SubPlan.

When the sub‑query compares columns of the same alias ( a.id1 = a.id2), the planner switches to an InitPlan:

SELECT (EXISTS (SELECT 1 AS one FROM test a WHERE (a.id1 = a.id2))) AS b FROM test;
EXPLAIN SELECT (EXISTS (SELECT 1 AS one FROM test a WHERE (a.id1 = a.id2))) AS b FROM test;

Resulting plan:

Seq Scan on test (cost=1.05..2.09 rows=4 width=1)
  InitPlan 1 (returns $0)
    -> Seq Scan on test a (cost=0.00..1.05 rows=1 width=0)
         Filter: (id1 = id2)

InitPlan

An InitPlan is a special kind of sub‑plan that is evaluated exactly once because it does not reference any columns from the outer query. It behaves like a non‑correlated sub‑query.

One‑Time Filter

A One‑Time Filter is a constant qualification evaluated by a Result node. If the qualification is false, PostgreSQL can return an empty result set without executing the rest of the plan.

Experimental observations

With the original correlated query ( test.id1 = a.id2) the planner uses a SubPlan, executing the sub‑query for each outer row.

When the sub‑query is rewritten to reference only its own alias ( a.id1 = a.id2), the planner recognises that the sub‑query is independent and replaces it with an InitPlan, which runs once.

If the condition becomes a constant (e.g., test.id1 = test.id2 evaluated on the same row), the plan includes a One‑Time Filter under a Result node.

Changing the data in test demonstrates the effect:

# truncate test;
# insert into test values (1,3),(2,1),(3,1),(3,3);
# explain select (exists (select 1 as one from test a where (test.id1 = test.id2))) as b from test;
Seq Scan on test (cost=0.00..2.09 rows=4 width=1)
  SubPlan 1
    -> Result (cost=0.00..1.04 rows=4 width=0)
         One-Time Filter: (test.id1 = test.id2)
         -> Seq Scan on test a (cost=0.00..1.04 rows=4 width=0)

When the table contains no rows satisfying id1 = id2, the One‑Time Filter evaluates to false and the outer query returns all false values.

Conclusion

PostgreSQL’s planner distinguishes three cases for EXISTS sub‑queries:

Correlated sub‑query (references outer columns) → SubPlan executed per outer row.

Non‑correlated sub‑query (no outer references) → InitPlan executed once.

Constant qualification → One‑Time Filter evaluated by a Result node, allowing early termination.

Understanding these plan nodes helps predict execution cost and optimise self‑join queries.

References

PostgreSQL InitPlan documentation: https://www.pgmustard.com/docs/explain/initplan

Depesz blog on InitPlan: https://www.depesz.com/2013/05/19/explaining-the-unexplainable-part-4/