Re: Outer join query plans and performance

Tom Lane wrote:
> Rich Doughty <rich(at)opusvl(dot)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