Skip site navigation (1) Skip section navigation (2)

Re: Outer join query plans and performance

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Rich Doughty <rich(at)opusvl(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Outer join query plans and performance
Date: 2005-10-25 14:12:02
Message-ID: 13679.1130249522@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-performance
Rich Doughty <rich(at)opusvl(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Richard HuxtonDate: 2005-10-25 14:16:27
Subject: Re: Reindex - Is this necessary after a vacuum?
Previous:From: Michael StoneDate: 2005-10-25 14:05:49
Subject: Re: insertion of bytea

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group