public inbox for [email protected]  

help / color / mirror / Atom feed

From: solaimurugan vellaipandiyan <[email protected]>
To: Alena Rybakina <[email protected]>
Cc: Ilia Evdokimov <[email protected]>
Cc: David Rowley <[email protected]>
Cc: Ranier Vilela <[email protected]>
Cc: PostgreSQL Hackers <[email protected]>
Cc: Peter Petrov <[email protected]>
Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
Date: Thu, 26 Jun 2025 21:10:53 +0530
Message-ID: <CAHEL7KS2euWWoXYUL_NnaFc5j7QggobmfYFWDm-eTJFYONdRQA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<CAEudQAoD707uh5Pjpg5NMyF-QO=fzajA+BmtcoqQAeXN1C+TkQ@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<[email protected]>

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

view thread (22+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
  In-Reply-To: <CAHEL7KS2euWWoXYUL_NnaFc5j7QggobmfYFWDm-eTJFYONdRQA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox