Обсуждение: Re: Re: Data warehousing requirements

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

<simon@2ndquadrant.com> writes:
> 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.

The alternative of course is to flip it around to be a left outer join
so that we can use those plan types.  But depending on the relative
sizes of the two tables this may be a loser.

If you are using a FULL join then it is indeed true that mergejoin is
the only supported plan type.  I don't think that was at issue here
though.

            regards, tom lane