Re: Outer join query plans and performance

In this particular case both outer joins are to the same table, and
the where clause is applied to one or the other, so it's pretty easy
to prove that they should generate identical results.  I'll grant that
this is not generally very useful; but then, simple test cases often
don't look very useful.

We've had mixed results with PostgreSQL and queries with
multiple outer joins when the WHERE clause limits the results
based on columns from the optional tables.  In at least one case
which performs very well, we have enough tables to cause the
"genetic" optimizer to kick in.  (So I suppose there is a chance
that sometimes it won't perform well, although we haven't seen
that happen yet.)

I can't speak to MySQL, but both Sybase and MaxDB handled
such cases accurately, and chose a plan with very fast
execution.  Sybase, however, spent 5 to 10 seconds in the
optimizer finding the sub-second plan.

-Kevin


>>> Tom Lane <tgl@sss.pgh.pa.us>  >>>
Rich Doughty <rich@opusvl.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).

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