PostgreSQL：如何使用generate_series()找出列中缺少的数字？

create table results ( commandid integer primary key);
insert into results (commandid) select * from generate_series(1,1000);
delete from results where random() < 0.20;

这有效：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE NOT EXISTS (SELECT 1 FROM results WHERE commandid = s.i);

这个替代配方也是如此：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
LEFT OUTER JOIN results ON (results.commandid = s.i) 
WHERE results.commandid IS NULL;

以上两种情况似乎都会在我的测试中产生相同的查询计划,但是您应该使用EXPLAIN ANALYZE与数据库中的数据进行比较,以确定哪种方法最佳.

说明

请注意,而不是NOT IN我在一个公式中使用了带有子查询的NOT EXISTS,而在另一个公式中使用了普通的OUTER JOIN. DB服务器更容易优化这些,并避免了NOT IN中出现NULL的混乱问题.

我最初偏爱OUTER JOIN公式,但至少在9.1中我的测试数据NOT EXISTS表格优化到同一计划.

当系列很大时,两者都会比下面的NOT IN配方表现更好,就像你的情况一样. NOT IN曾经要求Pg对正在测试的每个元组进行IN列表的线性搜索,但是对查询计划的检查表明Pg可能足够聪明,现在可以对其进行哈希处理. NOT EXISTS(由查询计划器转换为JOIN)和JOIN工作得更好.

在存在NULL命令的情况下,NOT IN公式既令人困惑又可能效率低下：

SELECT s.i AS missing_cmd
FROM generate_series(0,1000) s(i)
WHERE s.i NOT IN (SELECT commandid FROM results);

所以我会避免它.拥有1,000,000行,另外两行在1.2秒内完成,NOT IN配方运行CPU限制,直到我感到无聊并取消它.