On Wed, Jan 28, 2009 at 11:37 PM, Scott Marlowe
<scott.marlowe@gmail.com> wrote:
On Thu, Jan 29, 2009 at 12:01 AM, Kevin Traster <
kevin@mffais.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
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.
Umm... No. The top of the post you quoted regards the difference between the query "get ciknum from cik" versus get ciknum from cik where NOT IN.... The only differene between the two queries is the qualification of "where ciknum not in ....". It does not involve the difference between NOT IN versus Except
Both queries do an Index Scan using cik_ciknum_idx and those numbers show the different costs doing the same task.
In this case, neither table allowes nulls in the columns, both tables have single indexes on the columns used.
Regarding the previous posts about the same issues of PERFORMENCE between NOT IN versus EXCEPT. There has not been any answer to explain it - just talk about the differenences between the two results.
Yes, I can still get the results using EXCEPT but it would be nice to no why I can't get NOT IN to complete the simple query.