问题描述
我试图在PostgreSQL中的两个几乎相同的表之间的差异。我正在运行的当前查询是：
I am trying to get the diff between two nearly identical tables in postgresql. The current query I am running is:
SELECT * FROM tableA EXCEPT SELECT * FROM tableB;
和
SELECT * FROM tableB EXCEPT SELECT * FROM tableA;
上面的每个查询大约需要2分钟才能运行（它是一张大桌子）
Each of the above queries takes about 2 minutes to run (Its a large table)
我想将两个查询合并以节省时间，所以我尝试了：
I wanted to combine the two queries in hopes to save time, so I tried:
SELECT * FROM tableA EXCEPT SELECT * FROM tableB
UNION
SELECT * FROM tableB EXCEPT SELECT * FROM tableA;
运行正常，需要20分钟！！！我猜想最多只需要4分钟，即分别运行每个查询的时间。
And while it works, it takes 20 minutes to run!!! I would guess that it would at most take 4 minutes, the amount of time to run each query individually.
UNION是否正在做一些额外的工作，这使它耗时太长？还是有什么方法可以加快此速度（无论是否使用UNION）？
Is there some extra work UNION is doing that is making it take so long? Or is there any way I can speed this up (with or without the UNION)?
更新：使用UNION ALL运行查询需要15分钟，几乎是原来的4倍。我说自己是每个人都独立运行，那么我是否正确地说UNION（全部）根本不会加快运行速度？
UPDATE: Running the query with UNION ALL takes 15 minutes, almost 4 times as long as running each one on its own, Am I correct in saying that UNION (all) is not going to speed this up at all?
推荐答案
关于您的额外工作问题。是。联合不仅将这两个查询组合在一起，而且还会遍历并删除重复项。
With regards to your "extra work" question. Yes. Union not only combines the two queries but also goes through and removes duplicates. It's the same as using a distinct statement.
因此，尤其是与您的除外语句 union all结合使用可能会更快。
For this reason, especially combined with your except statements "union all" would likely be faster.
在此处了解更多信息：
Read more here:http://www.postgresql.org/files/documentation/books/aw_pgsql/node80.html
这篇关于Postgresql UNION花费的时间是运行单个查询的10倍的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！