MIME-Version: 1.0
References: <0b1f670d-b39d-4966-bf32-f0d502ebc564@postgrespro.ru>
 <CAEudQAoD707uh5Pjpg5NMyF-QO=fzajA+BmtcoqQAeXN1C+TkQ@mail.gmail.com>
 <46bc4eaf-58c5-42ab-8041-d3380a0768de@postgrespro.ru>
 <9b041978-06e3-4a50-8a5d-dacbb054f23e@tantorlabs.com>
 <8d7cbbf6-ddc0-4696-8af2-a68d740e14f2@postgrespro.ru>
 <6078f7e1-4b29-4b31-bd28-b84149e404e4@postgrespro.ru>
 <975a3736-a8b5-49b3-8009-4d4e86867aa1@postgrespro.ru>
 <0adbf75a-2059-4bb7-b878-c33892dbc1f0@tantorlabs.com>
 <300d7bb0-345c-4c0b-a0ef-4de573fcc94b@postgrespro.ru>
 <e172de23-7acb-4330-a40a-58ead7a47944@postgrespro.ru>
 <32446f52-1b18-499a-a77b-d6b66ad0f4cf@tantorlabs.com>
 <87a6fcbf-929a-408a-9471-28be3080254a@postgrespro.ru>
 <4e9d279c-83c1-42d3-b74d-7366ecd7f085@tantorlabs.com>
 <386165c3-3c01-4a85-b618-7919e64a52de@postgrespro.ru>
 <a4089881-aeec-46c8-bf8a-74ae795e17da@postgrespro.ru>
In-Reply-To: <a4089881-aeec-46c8-bf8a-74ae795e17da@postgrespro.ru>
From: solaimurugan vellaipandiyan <drsolaimurugan.v@gmail.com>
Date: Thu, 26 Jun 2025 21:10:53 +0530
Message-ID: 
 <CAHEL7KS2euWWoXYUL_NnaFc5j7QggobmfYFWDm-eTJFYONdRQA@mail.gmail.com>
Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
To: Alena Rybakina <a.rybakina@postgrespro.ru>
Cc: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 David Rowley <dgrowleyml@gmail.com>,
	Ranier Vilela <ranier.vf@gmail.com>,
	PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Peter Petrov <p.petrov@postgrespro.ru>
Content-Type: text/plain; charset="UTF-8"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHEL7KS2euWWoXYUL_NnaFc5j7QggobmfYFWDm-eTJFYONdRQA%40mail.gmail.com>
Precedence: bulk

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