pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedouter join versus not exists
3+ messages / 3 participants
[nested] [flat]
* outer join versus not exists
@ 2003-03-18 16:54 chester c young <chestercyoung@yahoo.com>
2003-03-18 17:29 ` Re: outer join versus not exists Tom Lane <tgl@sss.pgh.pa.us>
2003-03-18 17:30 ` Re: outer join versus not exists Bruno Wolff III <bruno@wolff.to>
0 siblings, 2 replies; 3+ messages in thread
From: chester c young @ 2003-03-18 16:54 UTC (permalink / raw)
To: pgsql-sql
In pg, is there any performance gain in using outer join with null as
versus using not exists, eg:
select t1.* from t1 right join t2 using( id ) where t2.id is null;
versus
select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);
__________________________________________________
Do you Yahoo!?
Yahoo! Platinum - Watch CBS' NCAA March Madness, live on your desktop!
http://platinum.yahoo.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: outer join versus not exists
2003-03-18 16:54 outer join versus not exists chester c young <chestercyoung@yahoo.com>
@ 2003-03-18 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-03-18 17:29 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
chester c young <chestercyoung@yahoo.com> writes:
> In pg, is there any performance gain in using outer join with null as
> versus using not exists, eg:
> select t1.* from t1 right join t2 using( id ) where t2.id is null;
You meant a left join here, no?
> versus
> select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);
It depends. Either one could be substantially better than the other,
depending on the data statistics.
If many of the t1 rows have multiple matches in t2, I'd think that the
join approach would lose, because it would generate lots of joined rows
that only get thrown away.
If t2.id is unique (and indexed), then the exists approach would
probably give you a plan approximately equivalent to a nestloop with
inner indexscan on t2. This would be one of the alternatives considered
for the join, so the join should be at least as fast as the exists,
possibly faster if the planner can find a faster plan involving merge or
hash joining.
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: outer join versus not exists
2003-03-18 16:54 outer join versus not exists chester c young <chestercyoung@yahoo.com>
@ 2003-03-18 17:30 ` Bruno Wolff III <bruno@wolff.to>
1 sibling, 0 replies; 3+ messages in thread
From: Bruno Wolff III @ 2003-03-18 17:30 UTC (permalink / raw)
To: chester c young <chestercyoung@yahoo.com>; +Cc: pgsql-sql
On Tue, Mar 18, 2003 at 08:54:27 -0800,
chester c young <chestercyoung@yahoo.com> wrote:
> In pg, is there any performance gain in using outer join with null as
> versus using not exists, eg:
>
> select t1.* from t1 right join t2 using( id ) where t2.id is null;
>
> versus
>
> select * from t1 where not exists (select 1 from t2 where t1.id=t2.id);
You can use explain analyze to compare the queries.
Another possible approach is:
select t1.* from t1, (select id from t1 except select id from t2) t3
where t1.id = t3.id;
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-03-18 17:30 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-18 16:54 outer join versus not exists chester c young <chestercyoung@yahoo.com>
2003-03-18 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-03-18 17:30 ` Bruno Wolff III <bruno@wolff.to>
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