Thread

Re: pull-up subquery if JOIN-ON contains refs to upper-query

Ilia Evdokimov <ilya.evdokimov@tantorlabs.com> — 2025-09-02T21:07:01Z

On 04.06.2025 13:40, Alena Rybakina wrote:
>
> Do you have any ideas on how to solve this problem? So far, the only 
> approach I see is to try an alternative plan but I'm still learning this.
>

Hi,

I've reviewed this patch, and I have suggestion about the approach.

Currently, the patch extends 'convert_EXISTS_sublick_to_join' with quite 
complex logic (clause collection, volatile checks, rewriting join quals, 
etc). While it works, the amount of branching and special cases makes 
the function harder to follow.

Looking at the logic, it seems that a large part of the complexity comes 
from trying to directly adapt 'convert_EXISTS_sublink_to_join' instead 
of factoring out a dedicated path. An alternative would be to introduce 
a separate function *'convert_EXISTS_sublink_to_lateral_join' *- with a 
similar API to 'convert_ANY_sublink_to_join'. Such a function can focus 
only on the EXISTS-to-join case, while keeping the existing function 
shorter and easier to reason about.

I even made some first rough sketches of this approach (not a finished 
patch, just an outline). Of course, it would still need proper 
adaptation, but I think it demonstrates that the overall structure can 
be kept simpler.

What do you think about refactoring in this direction?

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC,
https://tantorlabs.com