Postgresql SQL：当谈到 NOT IN 和 NOT EQUAL TO 时，哪个更有效，为什么

在PostgreSQL中，在合理的列表长度上通常会有相当小的差异，尽管IN在概念上要干净得多.很长的AND ... <> ...个列表和很长的NOT IN个列表的表现都很糟糕，其中AND个列表的表现比NOT IN个列表差得多.

在这两种情况下，如果它们足够长，你甚至可以问这个问题，那么你应该对值列表进行反连接或子查询排除测试.

WITH excluded(item) AS (
    VALUES('item1'), ('item2'), ('item3'), ('item4'),('item5')
)
SELECT * 
FROM thetable t
WHERE NOT EXISTS(SELECT 1 FROM excluded e WHERE t.item = e.item);

或者:

WITH excluded(item) AS (
    VALUES('item1'), ('item2'), ('item3'), ('item4'),('item5')
)
SELECT * 
FROM thetable t
LEFT OUTER JOIN excluded e ON (t.item = e.item)
WHERE e.item IS NULL;

(在现代Pg版本中，两者都将生成相同的查询计划).

如果值列表足够长(数万个项)，那么查询解析可能会开始产生巨大的成本.在这一点上，您应该考虑创建一个TEMPORARY表，将COPY个数据排除在其中，可能在其上创建索引，然后在TEMP表上使用上面的方法之一代替CTE.

演示:

CREATE UNLOGGED TABLE exclude_test(id integer primary key);
INSERT INTO exclude_test(id) SELECT generate_series(1,50000);
CREATE TABLE exclude AS SELECT x AS item FROM generate_series(1,40000,4) x;

其中exclude是要忽略的值列表.

然后，我在相同数据上比较以下方法，并以毫秒为单位比较所有结果:

• NOT IN名单:3424.596
• AND ...名单:80173.823
• 基于VALUES的JOIN排除:20.727
• 基于VALUES的子查询排除:20.495
• 基于表的JOIN，ex list上无索引:25.183
• 基于子查询表，ex list上没有索引:23.985

... 使基于CTE的方法比AND名单快3000多倍，比NOT IN名单快130倍.

这里的代码是:https://gist.github.com/ringerc/5755247(注意这个链接).

对于这个数据集大小，在排除列表上添加索引没有任何区别.

笔记:

• IN个列表生成SELECT 'IN (' || string_agg(item::text, ',' ORDER BY item) || ')' from exclude;个
• AND个列表(SELECT string_agg(item::text, ' AND item <> ') from exclude;个)
• 重复运行时，子查询和基于联接的表排除基本相同.
• 对该计划的审查表明，Pg将NOT IN转化为<> ALL

所以你可以看到，IN个和AND个列表之间确实存在huge个差距，而不是进行适当的连接.让我惊讶的是，使用VALUES列表的CTE的速度有多快...解析VALUES列表几乎不花时间，在大多数测试中执行相同或slightly faster than的表方法.

如果PostgreSQL能够自动识别一个长得离谱的IN子句或类似AND条件的链，并切换到更智能的方法，比如进行哈希连接或隐式将其转换为CTE node ，那就太好了.现在它不知道该怎么做.

另见:

• this handy blog post Magnus Hagander wrote on the topic