Re: A problem in deconstruct_distribute_oj_quals

 

Sorry I didn't make myself clear.  The plan change may not be obvious
except when the cheapest path happens to be joining t2 and t3 first and
then joining with t4 afterwards.  Currently HEAD would not generate such
a path because the joinqual of t2/t3 always has the OJ relid of t3/t4 in
its required_relids.

To observe an obvious plan change, we can add unique constraint for 'a'
and look how outer-join removal works.

alter table t add unique (a);

-- with that code
# explain (costs off)
select t1.a from (t t1 left join t t2 on true) left join (t t3 left join t t4 on t3.a = t4.a) on t2.a = t3.a;
                    QUERY PLAN
---------------------------------------------------
 Nested Loop Left Join
   ->  Seq Scan on t t1
   ->  Nested Loop Left Join
         ->  Seq Scan on t t2
         ->  Index Only Scan using t_a_key on t t3
               Index Cond: (a = t2.a)
(6 rows)


-- without that code
# explain (costs off)
select t1.a from (t t1 left join t t2 on true) left join (t t3 left join t t4 on t3.a = t4.a) on t2.a = t3.a;
          QUERY PLAN
------------------------------
 Nested Loop Left Join
   ->  Seq Scan on t t1
   ->  Materialize
         ->  Seq Scan on t t2
(4 rows)

This is another side-effect of that code.  The joinqual of t2/t3 is
treated as being pushed down when we try to remove t2/t3, because its
required_relids, which incorrectly includes the OJ relid of t3/t4,
exceed the scope of the join.  This is not right.

Thanks
Richard