From: | "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: JOIN vs. LEFT JOIN |
Date: | 2009-01-28 20:50:11 |
Message-ID: | 2655E082C2C2460683342FA387FCEABB@etsinformatics.local |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Thanks a lot Tom for explaining !
----- Original Message -----
From: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov>
Cc: "Nico Callewaert" <callewaert(dot)nico(at)telenet(dot)be>; "Andreas Wenk"
<a(dot)wenk(at)netzmeister-st-pauli(dot)de>; <pgsql-novice(at)postgresql(dot)org>
Sent: Wednesday, January 28, 2009 5:49 PM
Subject: Re: [NOVICE] JOIN vs. LEFT JOIN
> "Obe, Regina" <robe(dot)dnd(at)cityofboston(dot)gov> writes:
>> So in terms of performance
>
>> [INNER] JOIN -- fastest
>> LEFT JOIN -- generally slower (but there really is no alternative if you
>> don't want to leave out records without matches
>
> This is nonsense. A left join is not inherently slower than an inner
> join.
>
> What *is* true is that a left join constrains the optimizer more than an
> inner join, ie some join reorderings are allowed for inner joins but
> would change the answers if an outer join is involved. So in the
> context of a specific query you might get a slower plan if you use a
> left join. But you can't say that as a blanket statement. In a lot
> of cases there won't be any difference at all (particularly with more
> recent PG versions --- our optimizer has gotten smarter about outer
> joins over time).
>
>> FULL JOIN -- rarely used and slower than INNER, LEFT, RIGHT, but it
>> comes in handy at times.
>
> Likewise, a full join isn't necessarily slow in itself, but it
> constrains the possible plans quite a lot.
>
> regards, tom lane
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Rebecca Jones | 2009-01-29 15:51:01 | REALLY stupid question |
Previous Message | Tom Lane | 2009-01-28 17:10:57 | Re: UUId or Similar |