On Wed, Jul 29, 2009 at 11:34 AM, Ole Tange<ole@tange.dk> wrote:
> EXPLAIN tells me the LEFT JOIN does table scan as well. It seems the
> primary reason why your LEFT JOIN is faster than my 3 line alternative
> is because I do DELETEs, which is slow. So thank you for the LEFT JOIN
> idea.
If you don't want the NULL behaviour then you should also be able to
translate "WHERE foo NOT IN (select f from bar)" into "WHERE NOT
EXISTS (select 1 from bar where foo=f)"
You should also test 8.4 to see if the performance of all three of
these variants haven't changed. 8.4 understands how to plan these
kinds of joins much better now. It still doesn't know how to prove a
column cannot be NULL though so I don't know that it would actually
help in your particular case.
--
greg
http://mit.edu/~gsstark/resume.pdf