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-26 08:33:48 |
Message-ID: | 435F3F6C.3070203@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:
>
>>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
From | Date | Subject | |
---|---|---|---|
Next Message | Steinar H. Gunderson | 2005-10-26 10:30:51 | Re: zero performance on query |
Previous Message | Jean-Max Reymond | 2005-10-26 06:59:22 | Re: blue prints please |