Re: NOT IN >2hrs vs EXCEPT < 2 sec.

On Thu, Jan 29, 2009 at 12:01 AM, Kevin Traster <kevin(at)mffais(dot)com> wrote:
> 2 questions:
>
> 1) Different costs for same actions. Doing an explain on 2 nearly identical
> queries both involving the same Index scan on same table has 2 widely
> different costs for same Index scan 303375872.86 vs. 12576.70

Pretty sure this is a FAQ by now.

not in and except treat nulls differently. If you table has nullable
fields and nulls would break your query, then not in () is a bad
choice. Therefore, effort to optimize had been placed into except,
which is distinctly, symantically different from not in ().

It seems like some shift in the pg community has happened where we're
suddenly getting a lot of folks who came from a database where not in
and except are treated the same, even though they most definitely do
not mean the same thing.