From: Tom Lane <tgl@sss.pgh.pa.us>
To: Rich Doughty <rich@opusvl.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Outer join query plans and performance
Date: Tue, 25 Oct 2005 18:03:00 -0400
Message-ID: <29458.1130277780@sss.pgh.pa.us> (raw)
In-Reply-To: <435E7952.8080403@opusvl.com>
References: <435E186A.3060003@opusvl.com>
<13679.1130249522@sss.pgh.pa.us>
<435E7952.8080403@opusvl.com>
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).
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
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: tgl@sss.pgh.pa.us, rich@opusvl.com
Subject: Re: Outer join query plans and performance
In-Reply-To: <29458.1130277780@sss.pgh.pa.us>
* 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