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
