Re: Performance of subselects

=?ISO-8859-1?Q?Christian_Schr=F6der?= <cs(at)deriva(dot)de> writes:
> if I want to find all records from a table that don't have a matching
> record in another table there are at least two ways to do it: Using a
> left outer join or using a subselect. I always thought that the planner
> would create identical plans for both approaches, but actually they are
> quite different which leads to a bad performance in one case.

No, they're not the same; NOT IN has different semantics for nulls.

> Another interesting thing: If table "a" contains only 400,000 rows
> (instead of 500,000) the query planner decides to use a hashed subplan
> and performance is fine again:

You're probably at the threshold where it doesn't think the hashtable
would fit in work_mem.

regards, tom lane