Yang Zhang <yanghatespam@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
