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.
signature.asc
Description: Digital signature
