Re: Outer join query plans and performance

Tom Lane wrote:
> Rich Doughty <rich@opusvl.com> writes:
>
>>EXPLAIN SELECT *
>>FROM
>>     tokens.ta_tokens      t  LEFT JOIN
>>     tokens.ta_tokenhist   h1 ON t.token_id = h1.token_id LEFT JOIN
>>     tokens.ta_tokenhist   h2 ON t.token_id = h2.token_id
>>WHERE
>>     h1.histdate = 'now';
>
>
>>EXPLAIN SELECT *
>>FROM
>>     tokens.ta_tokens      t  LEFT JOIN
>>     tokens.ta_tokenhist   h1 ON t.token_id = h1.token_id LEFT JOIN
>>     tokens.ta_tokenhist   h2 ON t.token_id = h2.token_id
>>WHERE
>>     h2.histdate = 'now';
>
>
> 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. postgres does the first in 6 seconds and the
second in a lot longer (eventually abandoned).


--

   - Rich Doughty