Postgres Pro
Downloads
Home   >   mailing lists

Unify "In" Sublink to EXIST Sublink for better optimize opportunity - Mailing list pgsql-hackers

From Andy Fan
Subject Unify "In" Sublink to EXIST Sublink for better optimize opportunity
Date
Msg-id CAKU4AWqu=fZNX9zKh_ES-xjX3Om0J_aiJ9WhBCS8MrJcKvn=0A@mail.gmail.com
Whole thread Raw
Responses Re: Unify "In" Sublink to EXIST Sublink for better optimize opportunity  (Andy Fan <zhihui.fan1213@gmail.com>)
List pgsql-hackers
Tree view

Due to the implementation of convert_ANY_sublink_to_join,  we have
limitations below, which has been discussed at [1] [2].

    if (contain_vars_of_level((Node *) subselect, 1))
        return NULL; 

I'm thinking if we can do the ${subject}. If so,  the query like

SELECT * FROM t1 WHERE 
a IN (SELECT * FROM t2 WHERE t2.b > t1.b);

can be converted to

SELECT * FROM t1 WHERE 
EXISTS (SELECT * FROM t2 WHERE t2.b > t1.b AND t1.a = t2.a);

Then the sublink can be removed with existing logic (the NOT-IN format
will not be touched since they have different meanings). 

Any ideas? 


--
Best Regards
Andy Fan

pgsql-hackers by date:

Previous
From: Amit Kapila
Date:
Subject: Re: hash_xlog_split_allocate_page: failed to acquire cleanup lock
Next
From: Alvaro Herrera
Date:
Subject: Re: shadow variables - pg15 edition