From: | Rich Doughty <rich(at)opusvl(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Outer join query plans and performance |
Date: | 2005-10-25 18:28:34 |
Message-ID: | 435E7952.8080403@opusvl.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-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
From | Date | Subject | |
---|---|---|---|
Next Message | Kishore B | 2005-10-25 19:46:52 | Why different execution times for different instances for the same query? |
Previous Message | Tom Lane | 2005-10-25 18:01:04 | Re: Why Index is not working on date columns. |