Re: NOT IN subquery optimization

From: Andrey Lepikhov <a(dot)lepikhov(at)postgrespro(dot)ru>
To: "Li, Zheng" <zhelli(at)amazon(dot)com>, Michael Paquier <michael(at)paquier(dot)xyz>
Cc: "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: NOT IN subquery optimization
Date: 2020-01-09 04:47:26
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

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
The Russian Postgres Company

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Dilip Kumar 2020-01-09 05:00:10 Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions
Previous Message Karl O. Pinc 2020-01-09 04:32:26 Re: Patch to document base64 encoding