sql - 提高PostgreSQL设置差异效率

我正在postgresql 9.3中尝试一些set操作。
我有两个表，为了简单起见，我们称它们为table_a和table_b：

create table table_a(id varchar primary key);
create table table_b(id varchar primary key);

我有一个简单的查询（在最简单的公式中，虽然它是实际插入的源代码）：

(select id from table_a) except (select id from table_b);

在开始使用postgresql之前，我会执行如下操作：

set-diff table_a.csv table_b.csv > table_c.csv

其中SEDIFO看起来大致类似：

while (not eof(a)) and (not eof(b)):
  line_a <- peek_line(a)
  line_b <- peek_line(b)
  if line_a < line_b:
    output read_line(a)
  else if line_a == line_b:
    read_line(a)
  else:
    read_line(b)
while not eof(a):
  output read_line(a)

这不需要太长的时间，具有不重要的内存需求，并最大程度地利用顺序磁盘I/O，这是很重要的，因为这台机器没有成堆的内存——它不能适应RAM中的所有数据。
但是，postgresql提出了这种计划（从一些实际的表中）：

                                    QUERY PLAN
----------------------------------------------------------------------------------
 SetOp Except  (cost=3184554.28..3238904.44 rows=9434298 width=51)
   ->  Sort  (cost=3184554.28..3211729.36 rows=10870032 width=51)
         Sort Key: "*SELECT* 1".id
         ->  Append  (cost=0.00..428039.64 rows=10870032 width=51)
               ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..345707.96 rows=9434298 width=54)
                     ->  Seq Scan on table_a  (cost=0.00..251364.98 rows=9434298 width=54)
               ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..82331.68 rows=1435734 width=32)
                     ->  Seq Scan on table_b  (cost=0.00..67974.34 rows=1435734 width=32)

查询时间太长-几分钟。
我确信postgresql可以使用我上面概述的那种合并策略，只使用索引扫描，而不进行排序。相反，它似乎是将两个表扫描连接起来并对它们进行排序，有点像这个命令行，尽管没有读取表2次：

sort table_a.csv table_b.csv table_b.csv | uniq -u

这涉及到相当多的额外工作——比如，当不是所有的东西都能放在内存中时，是日志（n）乘以I/O的一小部分。
所涉及的列是btree索引的。从查询中选择的唯一列与已编制索引并正在合并的列相同。各地的地区都是C。
在我使用很多文本文件和一些自定义索引工具之前。我尝试使用数据库来代替查询，并避免维护自定义索引。然而，性能令人震惊，以至于我正在考虑在数据库之外执行合并和大多数其他大规模更新操作，通过csv往返传输数据。
我错过了什么？

最佳答案

第一个想法：
plainEXCEPT表示EXCEPT DISTINCT这意味着它从结果中消除了重复行。使用EXCEPT ALL如果可以，应该更快。
不要使用combining queries如果你也有其他选择，他们知道是缓慢的。
从您的EXPLAIN中，似乎您也应用了排序，这也需要更多的时间（尤其是在组合查询时）。
My9.2上的结果：
EXCEPT

explain select id from table_a except (select id from table_b);

结果：

HashSetOp Except  (cost=0.00..947.00 rows=20000 width=5)
  ->  Append  (cost=0.00..872.00 rows=30000 width=5)
        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..563.00 rows=20000 width=5)
              ->  Seq Scan on table_a  (cost=0.00..363.00 rows=20000 width=5)
        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..309.00 rows=10000 width=4)
              ->  Seq Scan on table_b  (cost=0.00..209.00 rows=10000 width=4)

EXCEPT和ORDER BY

explain select id from table_a except (select id from table_b) order by id;

结果：

Sort  (cost=2375.77..2425.77 rows=20000 width=5)
  Sort Key: "*SELECT* 1".id
  ->  HashSetOp Except  (cost=0.00..947.00 rows=20000 width=5)
        ->  Append  (cost=0.00..872.00 rows=30000 width=5)
              ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..563.00 rows=20000 width=5)
                    ->  Seq Scan on table_a  (cost=0.00..363.00 rows=20000 width=5)
              ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..309.00 rows=10000 width=4)
                    ->  Seq Scan on table_b  (cost=0.00..209.00 rows=10000 width=4)

带JOIN的antiORDER BY

explain select table_a.id from table_a
left outer join table_b on table_a.id = table_b.id
where table_b.id is null order by table_a.id;

和

explain select id from table_a
where not exists (select * from table_b where table_b.id = table_a.id) order by id;

结果（相同）：

Merge Anti Join  (cost=0.57..1213.57 rows=10000 width=5)
  Merge Cond: ((table_a.id)::text = (table_b.id)::text)
  ->  Index Only Scan using table_a_pkey on table_a  (cost=0.29..688.29 rows=20000 width=5)
  ->  Index Only Scan using table_b_pkey on table_b  (cost=0.29..350.29 rows=10000 width=4)

NOT IN和ORDER BY

explain select id from table_a where id not in (select id from table_b) order by id;

结果（我的获胜者）：

Seq Scan on table_a  (cost=234.00..647.00 rows=10000 width=5)
  Filter: (NOT (hashed SubPlan 1))
  SubPlan 1
    ->  Seq Scan on table_b  (cost=0.00..209.00 rows=10000 width=4)

用过的

create table table_a(id varchar primary key, rnd float default random());
create table table_b(id varchar primary key, rnd float default random());

do language plpgsql $$
begin
    for i in 1 .. 10000 loop
        insert into table_a(id) values (i);
        insert into table_b(id) values (i);
    end loop;
    for i in 10001 .. 20000 loop
        insert into table_a(id) values (i);
    end loop;
end;
$$;