Message-ID: <435E7952.8080403@opusvl.com>
Date: Tue, 25 Oct 2005 19:28:34 +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>
In-Reply-To: <13679.1130249522@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:
> 
>>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