To: Sami Dalouche <skoobi@free.fr>
cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [Fwd: Re: Outer joins and Seq scans] 
In-reply-to: <1193910660.29867.3.camel@samlaptop> 
References: <1193865739.23774.2.camel@samlaptop>
	<1193910660.29867.3.camel@samlaptop>
Comments: In-reply-to Sami Dalouche <skoobi@free.fr>
	message dated "Thu, 01 Nov 2007 10:51:00 +0100"
Date: Thu, 01 Nov 2007 09:29:56 -0400
Message-ID: <4121.1193923796@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Sami Dalouche <skoobi@free.fr> writes:
> Compare that to the following query, that is exactly the same except
> that the City table is inner'joined instead of outer joined
> ...
> the explain analyze is available at :
> http://www.photosdesami.com/temp/exp6.txt

AFAICS it's just absolutely blind luck that that query is fast.  The
planner chooses to do the contactinf7_/city8_ join first, and because
that happens to return no rows at all, all the rest of the query falls
out in no time, even managing to avoid the scan of adcreatedevent.
If there were any rows out of that join it would be a great deal slower.

There is a pretty significant semantic difference between the two
queries, too, now that I look closer: when you make 
"... join City city8_ on contactinf7_.city_id=city8_.id"
a plain join instead of left join, that means the join to contactinf7_
can be reduced to a plain join as well, because no rows with nulls for
contactinf7_ could possibly contribute to the upper join's result.
That optimization doesn't apply in the original form of the query,
which restricts the planner's freedom to rearrange things.

			regards, tom lane