Why Does This PostgreSQL Self‑Join Behave Unexpectedly? InitPlan & One‑Time Filter

Background

A self‑join query was observed where the subquery tests the existence of a parent‑child relationship. The query returns a mixture of true and false values, which is counter‑intuitive.

Reproducing the issue

create table test(id1 int, id2 int);
insert into test values (1,3),(2,1),(3,1),(3,3);
select (exists (select 1 from test a where test.id1 = a.id2)) as b from test;

Result:

b
---
t
f
t
t

Explain plan shows a sequential scan with a SubPlan:

Seq Scan on test  (cost=0.00..3.14 rows=4 width=1)
  SubPlan 2
    -> Seq Scan on test a  (cost=0.00..1.04 rows=4 width=4)

Alias ambiguity

Running the subquery alone raises an error because the reference test.id1 is ambiguous – it refers to the outer query, not the alias a:

select 1 from test a where test.id1 = a.id2;
ERROR:  invalid reference to FROM-clause entry for table "test"
HINT:  Perhaps you meant to reference the table alias "a".

Swapping alias positions

Changing the comparison to a.id1 = test.id2 makes every row evaluate to true:

select (exists (select 1 from test a where a.id1 = test.id2)) as b from test;

b
---
t
t
t
t

The plan remains a sequential scan with a SubPlan, but the logical direction of the comparison has changed.

Comparing alias columns directly

Using a.id1 = a.id2 yields true for rows where the two columns are equal. The plan now contains an InitPlan:

select (exists (select 1 from test a where a.id1 = a.id2)) as b from test;

b
---
t
t
t
t

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)

An InitPlan is a special SubPlan that is evaluated once before the main query because it does not depend on any outer rows.

One‑Time Filter

When the condition references the same table on both sides ( test.id1 = test.id2), the planner may generate a SubPlan with a One‑Time Filter:

explain select (exists (select 1 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)

The One‑Time Filter is evaluated once; if it evaluates to false the executor can return an empty result set without further scanning.

Correlated vs. uncorrelated subqueries

A correlated subquery references columns from the outer query, causing it to be re‑executed for each outer row. An uncorrelated subquery is independent of the outer query and can be executed a single time, which is why the planner represents it as an InitPlan or a One‑Time Filter.

Key observations

The placement of table aliases directly influences the semantics of the self‑join. Understanding how PostgreSQL represents constant‑only subqueries ( InitPlan) and one‑time qualifications ( One‑Time Filter) helps explain why a seemingly simple query may produce unexpected true/false patterns.

References

https://www.pgmustard.com/docs/explain/initplan

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