Re: Curious why planner can't handle NOT IN

Yang Zhang <yanghatespam(at)gmail(dot)com> writes:
> The most obvious query doesn't work so hot due to repeated execution
> of the subplan:

> => explain insert into newaccount select * from account where id not
> in (select id from account);

Yeah. Try using a NOT EXISTS instead.

> This isn't a big deal as there's a straightforward workaround, but I
> am curious what happened here. Googling turns up various mentions of
> "NOT IN" with poor plans that involve subplans. Then again I have
> read mention of hash anti-join which seems appropriate here(?), but
> that wasn't used here

The trouble with NOT IN is that it's not exactly the same as an
antijoin, because of the spec-mandated bizarre behavior for NULLs.
It's very difficult to optimize it to any extent without producing
wrong answers. NOT EXISTS avoids that problem.

regards, tom lane