Re: How would I write this query...

On Mon, May 01, 2006 at 05:23:41PM -0400, John D. Burger wrote:
> In my experience, queries like the OUTER LEFT JOIN version posted
> earlier are usually much more efficient than NOT IN queries like the
> above.  The planner seems to be pretty smart about turning (positive)
> IN queries into joins, but NOT IN queries usually turn into nested
> table scans, in my experience.

That's because they're not equivalent. IN/NOT IN have special semantics
w.r.t. NULLs that make them a bit more difficult to optimise. OUTER
JOINs on the other hand is easier since in a join condition anything =
NULL evaluates to NULL -> FALSE.

I think there's been some discussion about teaching the planner about
columns that cannot be NULL (like primary keys) thus allowing it to
perform this transformation safely. I don't know if anyone has done it
though...

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.