From: Tom Lane <tgl@sss.pgh.pa.us>
To: Sami Dalouche <skoobi@free.fr>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [Fwd: Re: Outer joins and Seq scans]
Date: Thu, 01 Nov 2007 09:29:56 -0400
Message-ID: <4121.1193923796@sss.pgh.pa.us> (raw)
In-Reply-To: <1193910660.29867.3.camel@samlaptop>
References: <1193865739.23774.2.camel@samlaptop>
<1193910660.29867.3.camel@samlaptop>
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
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, skoobi@free.fr
Subject: Re: [Fwd: Re: Outer joins and Seq scans]
In-Reply-To: <4121.1193923796@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