大型数据的NOT IN查询性能问题

Question

我正在尝试从表中获取id和数字，条件为number不在id中。

select id,number from tmp_t where number not in (select id from tmp_t)

我已经尝试过查询，但它花费了太多的时间...大概40分钟后我就和服务器断线了。那么我该怎么做呢？数据大约有500K行。

所以我想展示的是，“这是id和数字，这些数字在id中并不存在。”因为我试图插入数字，但是数字是FK，并且取决于ID，所以我想知道id和数字，这就是为什么我使用not in。

也许有人知道？顺便说一句，im使用Postgresql-13

Answer 1

您可以使用NOT EXISTS来编写它，尽管如果id的任何值为NULL，这些查询都会得到不同的结果(在这种情况下，NOT IN可能不会产生您想要的答案，所以从这个角度来看，not EXISTS也更好)。

select id,number from tmp_t where not exists 
    (select 1 from tmp_t a where a.id=tmp_t.number);

但是，只要work_mem足够大，您的公式也是有效的。

Answer 2

通常情况下，NOT EXISTS更快(如果涉及空值，则不会出现意外)：

select t1.id, t1.number 
from tmp_t t1
where not exists (select *
                  from tmp_t t2
                  where t2.id = t1.number)