From: Rich Doughty <rich@opusvl.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Outer join query plans and performance
Date: Tue, 25 Oct 2005 19:28:34 +0100
Message-ID: <435E7952.8080403@opusvl.com> (raw)
In-Reply-To: <13679.1130249522@sss.pgh.pa.us>
References: <435E186A.3060003@opusvl.com>
<13679.1130249522@sss.pgh.pa.us>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: rich@opusvl.com, tgl@sss.pgh.pa.us
Subject: Re: Outer join query plans and performance
In-Reply-To: <435E7952.8080403@opusvl.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox