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 10:12:02 -0400
Message-ID: <13679.1130249522@sss.pgh.pa.us> (raw)
In-Reply-To: <435E186A.3060003@opusvl.com>
References: <435E186A.3060003@opusvl.com>
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.)
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: <13679.1130249522@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