Message-ID: <435F3F6C.3070203@opusvl.com>
Date: Wed, 26 Oct 2005 09:33:48 +0100
From: Rich Doughty <rich@opusvl.com>
Organization: Opus VL
User-Agent: Mozilla Thunderbird 1.0.2 (X11/20050317)
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Outer join query plans and performance
References: <435E186A.3060003@opusvl.com> <13679.1130249522@sss.pgh.pa.us>
	<435E7952.8080403@opusvl.com> <29458.1130277780@sss.pgh.pa.us>
In-Reply-To: <29458.1130277780@sss.pgh.pa.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Tom Lane wrote:
> 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).

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