Tom Lane wrote:
Rich Doughty <[EMAIL PROTECTED]> 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
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
