pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: [Fwd: Re: Outer joins and Seq scans]
2+ messages / 2 participants
[nested] [flat]
* Re: [Fwd: Re: Outer joins and Seq scans]
@ 2007-11-01 13:29 Tom Lane <tgl@sss.pgh.pa.us>
2007-11-01 14:21 ` Re: [Fwd: Re: Outer joins and Seq scans] Sami Dalouche <skoobi@free.fr>
0 siblings, 1 reply; 2+ messages in thread
From: Tom Lane @ 2007-11-01 13:29 UTC (permalink / raw)
To: Sami Dalouche <skoobi@free.fr>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [Fwd: Re: Outer joins and Seq scans]
2007-11-01 13:29 Re: [Fwd: Re: Outer joins and Seq scans] Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-11-01 14:21 ` Sami Dalouche <skoobi@free.fr>
0 siblings, 0 replies; 2+ messages in thread
From: Sami Dalouche @ 2007-11-01 14:21 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2007-11-01 14:21 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-01 13:29 Re: [Fwd: Re: Outer joins and Seq scans] Tom Lane <tgl@sss.pgh.pa.us>
2007-11-01 14:21 ` Sami Dalouche <skoobi@free.fr>
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