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: Wed, 26 Oct 2005 09:33:48 +0100
Message-ID: <435F3F6C.3070203@opusvl.com> (raw)
In-Reply-To: <29458.1130277780@sss.pgh.pa.us>
References: <435E186A.3060003@opusvl.com>
<13679.1130249522@sss.pgh.pa.us>
<435E7952.8080403@opusvl.com>
<29458.1130277780@sss.pgh.pa.us>
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
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: <435F3F6C.3070203@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