查询语句 #1:
SELECT id
FROM subsource_position
WHERE
id NOT IN (SELECT position_id FROM subsource)
这将返回以下计划:
QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on subsource_position (cost=0.00..362486535.10 rows=128524 width=4)
Filter: (NOT (SubPlan 1))
SubPlan 1
-> Materialize (cost=0.00..2566.50 rows=101500 width=4)
-> Seq Scan on subsource (cost=0.00..1662.00 rows=101500 width=4)
查询2:
SELECT id FROM subsource_position
EXCEPT
SELECT position_id FROM subsource;
计划:
QUERY PLAN
-------------------------------------------------------------------------------------------------
SetOp Except (cost=24760.35..25668.66 rows=95997 width=4)
-> Sort (cost=24760.35..25214.50 rows=181663 width=4)
Sort Key: "*SELECT* 1".id
-> Append (cost=0.00..6406.26 rows=181663 width=4)
-> Subquery Scan on "*SELECT* 1" (cost=0.00..4146.94 rows=95997 width=4)
-> Seq Scan on subsource_position (cost=0.00..3186.97 rows=95997 width=4)
-> Subquery Scan on "*SELECT* 2" (cost=0.00..2259.32 rows=85666 width=4)
-> Seq Scan on subsource (cost=0.00..1402.66 rows=85666 width=4)
(8 rows)
我感觉我可能错过了其中一个查询的明显问题,或者我已经错误配置了PostgreSQL服务器。 我本来希望这个 NOT IN
查询能够很好地进行优化; 是否 NOT IN
总是存在性能问题,还是它在这里无法优化有原因呢?
其他数据:
=> select count(*) from subsource;
count
-------
85158
(1 row)
=> select count(*) from subsource_position;
count
-------
93261
(1 row)
编辑:我已经解决了下面提到的A-B != B-A问题。但是,我的问题仍然存在:查询#1仍然比查询#2要慢得多。我认为这是由于两个表具有相似数量的行。
编辑2:我正在使用PostgreSQL 9.0.4。我无法使用EXPLAIN ANALYZE,因为查询#1需要太长时间。所有这些列都是NOT NULL的,因此不应该由此产生任何差异。
编辑3:我在这两列上都有索引。我还没有让查询#1完成(在约10分钟后放弃)。查询#2立即返回。
select sp.id from subsource_position sp left join subsource s on sp.id = s.position_id where s.position_id is null
。看起来像是它看到了 "没有 where 的选择",直接转到了顺序扫描并将其材料化,因为中间结果集很大。如果在内部 SELECT 中添加 WHERE,您将看到它从 seq 扫描过渡到索引扫描,并且在不同点上进行了材料化。材料化看起来是您的敌人。 - mu is too short