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

Question

该查询基本上是：

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

假装我 100% 确定查询的 DISTINCT 部分是其运行缓慢的原因，我省略了查询的其余部分以避免混淆，因为它是我主要关心的是不同部分的缓慢（不同总是缓慢的根源）。

该表有 250 万行数据。此处未列出的目的需要 DISTINCT （因为我不想返回修改后的查询，而只想返回有关使不同查询在 上运行得更快的一般信息>DBMS 级别，如果可能）。

如何在不更改 SQL 的情况下使 DISTINCT 运行得更快（特别是使用 Postgres 9）（即，我无法更改传入的 SQL，但可以在数据库级别优化某些内容）？

Answer 1

通常，您可以通过使用 group by 来解决 distinct 问题，从而使此类查询运行得更快：

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

Answer 2

您的 DISTINCT 导致它对输出行进行排序以查找重复项。如果您在查询选择的列上放置索引，数据库可能能够按索引顺序读出它们并保存排序步骤。很大程度上取决于查询的细节和所涉及的表——你说的“知道问题出在 DISTINCT”确实限制了可用答案的范围。

Answer 3

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

但在全球范围内将其设置得太高之前，请先阅读它。您可以轻松地炸毁您的服务器，因为 max_connections 设置充当该数字的乘数。

这意味着，如果您设置 work_mem = 128MB 并设置 max_connections = 100（默认值），则您应该拥有超过 12.8GB 的​​ RAM。您实际上是在告诉服务器它可以使用这么多内存来执行查询（甚至不考虑 Postgres 或其他方式使用的任何其他内存）。