Re: Allowing join removals for more join types

On 18 May 2014 16:38 David Rowley Wrote

 

Sound like a good idea to me..

 

I have one doubt regarding the implementation, consider the below query

 

Create table t1 (a int, b int);

Create table t2 (a int, b int);

 

Create unique index on t2(b);

 

select x.a from t1 x left join (select distinct t2.a a1, t2.b b1 from t2) as y on x.a=y.b1;  (because of distinct clause subquery will not be pulled up)

 

In this case, Distinct clause is used on t2.a, but t2.b is used for left Join (t2.b have unique index so this left join can be removed).

 

So I think now when you are considering this join removal for subqueries then this can consider other case also like unique index inside subquery,

because in attached patch  unique index is considered only if its RTE_RELATION

 

+          if (innerrel->rtekind == RTE_RELATION &&

+                      relation_has_unique_index_for(root, innerrel, clause_list, NIL, NIL))

                       return true;

 

 

Correct me if I am missing something..