sql - PostgreSQL:NOT IN 与 EXCEPT 性能差异(编辑 #2)

Question

我有两个功能相同的查询。其中一个表现很好，另一个表现很差。我看不出性能差异从何而来。

查询#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 花费的时间太长。所有这些列都不是 NULL，因此应该没有差异。

编辑 3:我在这两列上都有一个索引。我还没有完成查询 #1(约 10 分钟后放弃)。查询 #2 立即返回。

Answer 1

查询 #1 不是执行此操作的优雅方式...(NOT)IN SELECT 对一些条目没问题，但它不能使用索引(Seq Scan)。

没有 EXCEPT，替代方法是使用 JOIN(HASH JOIN):

    SELECT sp.id
    FROM subsource_position AS sp
        LEFT JOIN subsource AS s ON (s.position_id = sp.id)
    WHERE
        s.position_id IS NULL

EXCEPT 很久以前出现在 Postgres 中......但是使用 MySQL 我相信这仍然是使用索引实现此目的的唯一方法。