Message-ID: <3c077371-bdcb-4537-aabe-eb944d8e1d07@yandex.ru>
Date: Tue, 12 May 2026 20:19:27 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: pull-up subquery if JOIN-ON contains refs to upper-query
To: solaimurugan vellaipandiyan <drsolaimurugan.v@gmail.com>
Cc: Alena Rybakina <a.rybakina@postgrespro.ru>,
 Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>,
 David Rowley <dgrowleyml@gmail.com>, Ranier Vilela <ranier.vf@gmail.com>,
 PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>,
 Petr Petrov <p.petrov@postgrespro.ru>
References: <0b1f670d-b39d-4966-bf32-f0d502ebc564@postgrespro.ru>
 <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>
 <CAHEL7KS2euWWoXYUL_NnaFc5j7QggobmfYFWDm-eTJFYONdRQA@mail.gmail.com>
 <6b0da757-c63e-4abd-8fda-4f38e7dfd222@postgrespro.ru>
 <CAHEL7KTFh+W6HSEETpv_=P8weLKNrWPAu6y6GLwrKhnhENOTWg@mail.gmail.com>
Content-Language: en-US
From: Alena Rybakina <lena.ribackina@yandex.ru>
In-Reply-To: 
 <CAHEL7KTFh+W6HSEETpv_=P8weLKNrWPAu6y6GLwrKhnhENOTWg@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: 
 <https://www.postgresql.org/message-id/3c077371-bdcb-4537-aabe-eb944d8e1d07%40yandex.ru>
Precedence: bulk

Hi, thank you for your valuable feedback. If you have time you can test 
it on sqlsmith (https://github.com/anse1/sqlsmith) -
it will give some corner cases that should be observed.

On 11.05.2026 08:20, solaimurugan vellaipandiyan wrote:
> Hi Alena,
>
> I did some additional testing on the latest patch and the behavior
> looks good in my environment so far.
>
> I verified the original EXISTS pull-up case and confirmed that after
> applying the patch the planner generates optimized Semi Join plans
> instead of EXISTS(SubPlan).
> I also tested several additional cases discussed in the thread, including:
> 1.NOT EXISTS queries
> 2. constant qual conditions
> 3. ANY array conditions
> 4. LEFT JOIN cases
> 5. FULL JOIN cases
> For the FULL JOIN scenarios, I checked both the execution plans and
> actual query results. In one case the planner simplified the query
> into a Hash Join plan, but the final query results remained correct.
> I additionally ran:
>   - make check
>   - make -C contrib check
> Both completed successfully in my environment and I did not observe
> planner crashes or incorrect query results during testing.
> Overall, the patch seems to improve planner behavior consistently for
> these EXISTS pull-up scenarios.
>
> Regards,
> Solaimurugan V
>
>
-- 
-----------
Best regards,
Alena Rybakina