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