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 |
Message-ID: | ef790c4a-6186-4591-7f19-9c2c87b6fd2a@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
https://postgrespro.com
The Russian Postgres Company
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 |