Re: NOT IN subquery optimization

On 1/7/20 12:34 AM, Li, Zheng wrote:
> Hi Andrey,
>
> Thanks for the comment!
>
> The unimproved cases you mentioned all fall into the category “correlated subquery”. This category is explicitly disallowed by existing code to convert to join in convert_ANY_sublink_to_join:
> /*
> * The sub-select must not refer to any Vars of the parent query. (Vars of
> * higher levels should be okay, though.)
> */
> if (contain_vars_of_level((Node *) subselect, 1))
> return NULL;
>
> I think this is also the reason why hashed subplan is not used for such subqueries.
>
> It's probably not always safe to convert a correlated subquery to join. We need to find out/prove when it’s safe/unsafe to convert such ANY subquery if we were to do so.
>

Maybe this part of code contains logical error?
You optimize only the special case of the "NOT IN" expression, equal to
NOT EXISTS. The convert_EXISTS_sublink_to_join() routine can contain
vars of the parent query.
May be you give an trivial example for this problem?

--
Andrey Lepikhov
Postgres Professional
https://postgrespro.com
The Russian Postgres Company