Postgresql UNION 花费的时间是运行单个查询的 10 倍

Question

我试图获取 postgresql 中两个几乎相同的表之间的差异。我正在运行的当前查询是：

SELECT * FROM tableA EXCEPT SELECT * FROM tableB;

并且

SELECT * FROM tableB EXCEPT SELECT * FROM tableA;

上面的每个查询都需要大约 2 分钟来运行（它是一个大表）

我想结合这两个查询以希望节省时间，所以我尝试：

SELECT * FROM tableA EXCEPT SELECT * FROM tableB
UNION
SELECT * FROM tableB EXCEPT SELECT * FROM tableA;

虽然它有效，但它需要跑20分钟！！！我猜想最多需要 4 分钟，即单独运行每个查询的时间。

UNION 是否做了一些额外的工作导致需要这么长时间？或者有什么方法可以加快速度（有或没有联盟）？

更新：使用 UNION ALL 运行查询需要 15 分钟，几乎是单独运行每个查询的 4 倍，我是否正确地说 UNION (all) 根本不会加快速度？

Answer 1

关于你的“额外工作”问题。是的。 Union 不仅合并两个查询，而且还遍历并删除重复项。这与使用 unique 语句相同。

因此，特别是与您的 except 语句“union all”结合使用可能会更快。

在这里阅读更多内容：
http://www.postgresql.org/files/documentation/books/aw_pgsql/node80.html< /a>

Answer 2

除了合并第一个和第二个查询的结果之外，UNION 默认情况下还会删除重复记录。 （参见http://www.postgresql.org/docs/8.1/static /sql-select.html）。检查两个查询之间的重复记录所涉及的额外工作可能是造成额外时间的原因。在这种情况下，不应有任何重复记录，因此可以通过指定 UNION ALL 来避免查找重复记录的额外工作。

SELECT * FROM tableA EXCEPT SELECT * FROM tableB
UNION ALL
SELECT * FROM tableB EXCEPT SELECT * FROM tableA;

Answer 3

我认为您的代码不会返回您想要的结果集。我宁愿认为你想要这样做：

SELECT * 
  FROM (
        SELECT * FROM tableA 
        EXCEPT 
        SELECT * FROM tableB
       ) AS T1
UNION 
SELECT * 
  FROM (
        SELECT * FROM tableB 
        EXCEPT 
        SELECT * FROM tableA
       ) AS T2;

换句话说，你想要一组互斥的成员。如果是这样，您需要阅读 SQL 中的关系运算符优先级；）当您阅读完后，您可能会意识到上述内容可以合理化为：

SELECT * FROM tableA 
UNION 
SELECT * FROM tableB
EXCEPT 
SELECT * FROM tableA 
INTERSECT
SELECT * FROM tableB;

---

FWIW，使用子查询（派生表 T1 和 T2）来显式显示（否则是隐式的）关系运算符优先级，您的原始查询是这样的：

SELECT * 
  FROM (
        SELECT * 
          FROM (
                SELECT * 
                  FROM tableA 
                EXCEPT 
                SELECT * 
                  FROM tableB
               ) AS T2
        UNION
        SELECT * 
          FROM tableB
       ) AS T1
EXCEPT 
SELECT * 
  FROM tableA;

上面的内容可以关系到：

SELECT * 
  FROM tableB 
EXCEPT 
SELECT * 
  FROM tableA;

...我认为这不是预期的结果。

Answer 4

您可以使用 tableA FULL OUTER JOIN tableB，它只需 1 次表扫描即可提供您想要的内容（具有适当的联接条件），它可能比上面的 2 个查询更快。

请发布更多信息。