Re: outer join versus not exists

chester c young <chestercyoung(at)yahoo(dot)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