Re: Outer join query plans and performance

Tom Lane wrote:
> Rich Doughty <rich(at)opusvl(dot)com> writes:
>
>>Tom Lane wrote:
>>
>>>The reason these are different is that the second case constrains only
>>>the last-to-be-joined table, so the full cartesian product of t and h1
>>>has to be formed. If this wasn't what you had in mind, you might be
>>>able to rearrange the order of the LEFT JOINs, but bear in mind that
>>>in general, changing outer-join ordering changes the results. (This
>>>is why the planner won't fix it for you.)
>
>
>>FWIW mysql 4.1 (and i'm no fan at all of mysql) completes both these queries
>>in approximately 3 seconds.
>
>
> Does mysql get the correct answer, though? It's hard to see how they do
> this fast unless they (a) are playing fast and loose with the semantics,
> or (b) have very substantially more analysis logic for OUTER JOIN semantics
> than we do. Perhaps mysql 5.x is better about this sort of thing, but
> for 4.x I'd definitely find theory (a) more plausible than (b).

i would assume so. i'll re-run my testcase later and verify the results of the
two side-by-side.

> The cases that would be interesting are those where rearranging the
> outer join order actually does change the correct answer --- it may not
> in this particular case, I haven't thought hard about it. It seems
> fairly likely to me that they are rearranging the join order here, and
> I'm just wondering whether they have the logic needed to verify that
> such a transformation is correct.
>
> regards, tom lane
>

--

- Rich Doughty