From: | "Li, Zheng" <zhelli(at)amazon(dot)com> |
---|---|
To: | "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org> |
Subject: | NOT IN subquery optimization |
Date: | 2019-06-28 16:18:52 |
Message-ID: | D204D6B4-2C4B-44FD-848A-9503A3DC2874@amazon.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Resending patch v2.2, looks like the previous submission did not get attached to the original thread.
This version fixed an issue that involves CTE. Because we call subquery_planner before deciding whether to proceed with the transformation, we need to setup access to upper level CTEs at this point if the subquery contains any CTE RangeTblEntry.
Also added more test cases of NOT IN accessing CTEs, including recursive CTE. It's nice that CTE can use index now!
Regards,
-----------
Zheng Li
AWS, Amazon Aurora PostgreSQL
On 6/28/19, 12:02 PM, "Li, Zheng" <zhelli(at)amazon(dot)com> wrote:
Rebased patch is attached.
Comments are welcome.
-----------
Zheng Li
AWS, Amazon Aurora PostgreSQL
On 6/14/19, 5:39 PM, "zhengli" <zhelli(at)amazon(dot)com> wrote:
In our patch, we only proceed with the ANTI JOIN transformation if
subplan_is_hashable(subplan) is
false, it requires the subquery to be planned at this point.
To avoid planning the subquery again later on, I want to keep a pointer of
the subplan in SubLink so that we can directly reuse the subplan when
needed. However, this change breaks initdb for some reason and I'm trying to
figure it out.
I'll send the rebased patch in the following email since it's been a while.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html
Attachment | Content-Type | Size |
---|---|---|
not_in_to_anti_join_transformation_v2_2.patch | application/octet-stream | 163.7 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Julien Rouhaud | 2019-06-28 16:43:40 | Re: Avoid full GIN index scan when possible |
Previous Message | Tomas Vondra | 2019-06-28 16:10:51 | Re: Avoid full GIN index scan when possible |