Postgres中加速SELECT DISTINCT查询的解决方案

Question

这个查询基本上是：

SELECT DISTINCT "my_table"."foo" from "my_table" WHERE...

假定查询中的DISTINCT部分是导致查询运行缓慢的原因，我省略了查询的其余部分，以避免混淆，因为我主要关心的是Distinct的速度问题（Distinct总是一个缓慢的来源）。

涉及的表有250万行数据。这里需要DISTINCT，但未列出具体目的（因为我不想返回修改后的查询，而只是想获取一般信息，关于如何使DBMS层面上的distinct查询运行更快，如果可能的话）。

如何在不改变SQL的情况下（即，无法更改此传入的SQL），使DISTINCT查询更快（特别是使用Postgres 9）？

- orokusaki

---

需要完整的查询才能给出答案。通常，人们使用DISTINCT的原因不好。 - Denis de Bernardy

@orokusaki：查询是一个整体，不是在一侧的distinct，在另一侧的join和第三侧的group by。你不能因为没有distinct而使查询更快就认为distinct是问题所在...你无法回答这样的问题。或者说，你可以使用索引来解决。 - Cyril Gandon

@orokusaki：这真的取决于情况。在查询中频繁使用distinct通常反映了某个连接的子优化不足。并非总是如此，但经常发生。在这种情况下，想法是重写查询，使子语句位于返回唯一行的子查询中（或使用in()子句进行检查）。 - Denis de Bernardy

@Denis- 关于性能方面，存在一个次优的连接，但是无法避免。我正在过滤M2M关系（获取所有在其“foos”列表中具有[x、y或z]的用户（m2m）。 - orokusaki

Answer 1

通常情况下，您可以通过使用 group by 来避免使用 distinct ，从而使此类查询运行更快：

select my_table.foo 
from my_table 
where [whatever where conditions you want]
group by foo;

- user554546

---

我无法修改SQL，这就是为什么我省略了大部分查询的原因。 - orokusaki

要么让用户（编写运行这些查询的应用程序的开发人员或随意运行这些查询的用户）切换他们的SQL。如果你做不到这一点，那么你可能可以通过在foo上对my_table进行索引来获得一些收益。 - user554546

这对我来说是个很好的解决方案。起初我以为是大偏移量使我的查询变慢，但在从DISTINCT切换到GROUP BY后，它们运行得更快了20倍。谢谢！ - xaisoft

感谢Jack的好建议！将SELECT DISTINCT替换成GROUP BY，使得我的查询运行时间从649毫秒降至87毫秒，快了近7.5倍。我的INNER JOIN是在一个包含约30,000行数据的表格和另一个包含约322,000行数据的连接表格之间进行的（我想匹配前者中的相关行并使用后者进行筛选）。我已经为INNER JOIN中使用的所有列和单个WHERE语句添加了索引，所以我希望找到另一种优化方法来加速查询，在这种情况下这种方式非常有效。 - bluebinary

为什么这会使查询更快？ - Ioan Alexandru Cucu

@IoanAlexandruCucu 这个答案可能包含一些关于此的提示。 - gerrit

Answer 2

你的DISTINCT操作会导致输出行按顺序排序以查找重复项。如果在查询选择的列上创建索引，数据库可能能够按索引顺序读取它们并节省排序步骤。很多情况取决于查询和涉及的表的细节--你说“你知道问题出在DISTINCT上”实际上限制了可用答案的范围。

- antlersoft

---

我了解这限制了回答的范围，这也是我这样做的原因。我只寻求在数据库层面上发生的答案，这也是你所给出的答案 (+1)。 - orokusaki

仅有索引还不够。我在不同的列上建立了索引，但查询仍需要几分钟才能搜索800万行数据以查找4个不同的值。 - Cerin

参见 https://dev59.com/yGgu5IYBdhLWcg3winnU#14732410，将select distinct查询放在子查询中并计数对我奇怪地起作用了。 - rogerdpack

Answer 3

您可以尝试增加work_mem设置，具体取决于您的数据集大小，它可能会导致查询计划切换到哈希聚合，通常速度更快。

但在全局设置之前，请先阅读相关资料。因为max_connections设置作为这个数字的乘数，所以您很容易将服务器搞崩溃。

这意味着如果您将work_mem设置为128MB，并设置max_connections = 100（默认值），那么您至少需要12.8GB的RAM。您基本上是告诉服务器可以使用这么多内存来执行查询（甚至不考虑Postgres或其他方面的任何其他内存使用情况）。

- maniek