Yang Zhang <yanghatespam@gmail.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
