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
