Postgres Pro
Downloads
Home   >   mailing lists

Thread: NOT IN subquery optimization

NOT IN subquery optimization

From
"Li, Zheng"
Date:
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
Attachment

Re: NOT IN subquery optimization

From
Thomas Munro
Date:
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

Re: NOT IN subquery optimization

From
Alvaro Herrera
Date:
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

Re: NOT IN subquery optimization

From
"Li, Zheng"
Date:
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
Attachment