Josh Berkus <josh@agliodbs.com> wrote on 08.10.2004, 07:53:26:
>
> > It's not so much that they are necessarily inefficient as that they
> > constrain the planner's freedom of action. You need to think a lot more
> > carefully about the order of joining than when you use inner joins.
>
> I've also found that OUTER JOINS constrain the types of joins that can/will be
> used as well as the order. Maybe you didn't intend it that way, but (for
> example) OUTER JOINs seem much more likely to use expensive merge joins.
>
Unfortunately, yes thats true - thats is for correctness, not an
optimization decision. Outer joins constrain you on both join order AND
on join type. Nested loops and hash joins avoid touching all rows in
the right hand table, which is exactly what you don't want when you
have a right outer join to perform, since you wish to include rows in
that table when there is no match. Thus, we MUST choose a merge join
even when (if it wasn't an outer join) we would have chosen a nested
loops or hash.
Best Regards, Simon Riggs