From: Sami Dalouche <skoobi@free.fr>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: [Fwd: Re: Outer joins and Seq scans]
Date: Thu, 01 Nov 2007 15:21:17 +0100
Message-ID: <1193926878.31606.4.camel@samlaptop> (raw)
In-Reply-To: <4121.1193923796@sss.pgh.pa.us>
References: <1193865739.23774.2.camel@samlaptop>
<1193910660.29867.3.camel@samlaptop>
<4121.1193923796@sss.pgh.pa.us>
Thanks for your answer.
So, basically, what you are saying is that there is nothing particularly
wrong with the query, nor with its optimization ? So if I need
performance for this query, I should just revert to other techniques
(giving more memory to postgres, caching outside postgres, etc..) ?
Regards,
Sami Dalouche
Le jeudi 01 novembre 2007 à 09:29 -0400, Tom Lane a écrit :
> 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: skoobi@free.fr, tgl@sss.pgh.pa.us
Subject: Re: [Fwd: Re: Outer joins and Seq scans]
In-Reply-To: <1193926878.31606.4.camel@samlaptop>
* 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