Обсуждение: NOT IN subquery optimization
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 thetransformation, 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@amazon.com> wrote: Rebased patch is attached. Comments are welcome. ----------- Zheng Li AWS, Amazon Aurora PostgreSQL On 6/14/19, 5:39 PM, "zhengli" <zhelli@amazon.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
Вложения
On Sat, Jun 29, 2019 at 4:19 AM Li, Zheng <zhelli@amazon.com> wrote:> > 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 withthe 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! Hi Zheng, Jim, With my Commitfest doozer hat on, I have moved this entry to the September 'fest. I noticed in passing that it needs to be adjusted for the new pg_list.h API. It'd be good to get some feedback from reviewers on these two competing proposals: https://commitfest.postgresql.org/24/2020/ https://commitfest.postgresql.org/24/2023/ -- Thomas Munro https://enterprisedb.com
On 2019-Aug-02, Thomas Munro wrote: > Hi Zheng, Jim, > > With my Commitfest doozer hat on, I have moved this entry to the > September 'fest. I noticed in passing that it needs to be adjusted > for the new pg_list.h API. It'd be good to get some feedback from > reviewers on these two competing proposals: > > https://commitfest.postgresql.org/24/2020/ > https://commitfest.postgresql.org/24/2023/ Hello, Is this patch dead? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Hi Hackers, I'm sending an updated patch: 1. add GUC enable_not_in_transform to guard the optimization/transformation, the guc is on by default. 2. fix a bug: bail out NOT IN transformation early in convert_ANY_sublink_to_join so that parse->rtable doesn't get appendedconditions are not met for the transformation. 3. add a CTE not in test case. Here are the conditions for the transformation: /* *Allow transformation from NOT IN query to ANTI JOIN if ALL of the * following conditions are true: * 1. The GUC apg_not_in_transform_enabled is set to true. * 2. the NOT IN subquery is not hashable, in which case an expensive * subplan will be generated if we don't transform. * 3. the subquery does not define any CTE. */ Regards, ----------- Zheng Li AWS, Amazon Aurora PostgreSQL