From: Tom Lane <tgl@sss.pgh.pa.us>
To: chester c young <chestercyoung@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: outer join versus not exists
Date: Tue, 18 Mar 2003 12:29:16 -0500
Message-ID: <26874.1048008556@sss.pgh.pa.us> (raw)
In-Reply-To: <20030318165427.52097.qmail@web12703.mail.yahoo.com>
References: <20030318165427.52097.qmail@web12703.mail.yahoo.com>
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
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, chestercyoung@yahoo.com
Subject: Re: outer join versus not exists
In-Reply-To: <26874.1048008556@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