Re: pull-up subquery if JOIN-ON contains refs to upper-query - Mailing list pgsql-hackers

Hi Alena,

I tested the latest patch on current master and verified the planner
behavior for the EXISTS pull-up cases described in the thread.

Before applying the patch, the following query produced an EXISTS(SubPlan) plan:

EXPLAIN (ANALYZE, COSTS OFF, SUMMARY OFF, TIMING OFF)
SELECT *
FROM ta
WHERE EXISTS (
    SELECT *
    FROM tb
    JOIN tc ON ta.id = tb.id
);

Plan before patch:
Seq Scan on ta
  Filter: EXISTS(SubPlan exists_1)

After applying the patch, the same query is transformed into:

Nested Loop Semi Join

with Index Only Scan on tb_pkey, so the pull-up optimization is now
applied correctly for JOIN ON clauses referencing outer Vars.

I also tested some additional cases mentioned in the discussion:

1. NOT EXISTS case

     planner generated Nested Loop Anti Join as expected.

2 . Constant qual case (WHERE tc.aid = 1)

     planner produced an optimized Hash Join plan and avoided EXISTS(SubPlan).

3. ANY array condition

     tb.id = ANY('{1}'::int[])

     planner successfully used Index Only Scan and generated an
optimized join plan.

In all tested cases, query results remained correct and I did not
observe incorrect transformations during my testing.

Overall the patch behavior looks good from my side and the planner now
behaves consistently for these EXISTS pull-up scenarios.

Regards,
Solaimurugan V