PostgreSQL聚合性能

Question

我注意到Postgres（8.3）中简单聚合性能存在一些问题。问题在于，如果我有一个表（例如200M行），它是按（customer_id，order_id）唯一的，那么查询select customer_id,max(order_id) from larger_table group by customer_id比一个简单的Java / JDBC程序要慢一个数量级以上，后者执行以下操作：

1）初始化一个空HashMap customerMap（这将映射id->最大订单大小） 2）执行“ select customer_id，order_id from larger_table”，并获取流式结果集 3）迭代结果集，在每一行上执行以下操作：

long id = resultSet.getLong("customer_id");
long order = resultSet.getLong("order_id");
if (!customerMap.containsKey(id)) 
    customerMap.put(id,order);
else 
    customerMap.put(id,Math.max(order,customerMap.get(id)));

这种性能差异是否是预期的？我认为不应该，因为我想上述情况与内部发生的情况非常接近。这是否表明数据库存在某些错误/调整不正确的问题？

- ryan

---

请在您的语句中包含EXPLAIN ANALYZE的输出，这样我们可以看出您是否缺少索引。 - Daniel Lyons

Answer 1

很可能是你的work_mem设置过低。首先应该检查这个设置。我最近也因为这个问题被咬了一口。其次可能出现的问题是你缺少一个外键索引。

下面是详细的解释：

通常，每当数据库性能看起来不太好时，需要问几个问题：

1. 你使用的是否是最新版本？从7.4到9.0之间的每个点发布都带来了实质性的性能改进——如果升级是可能的，建议升级。

2. 你是否在真实数据上进行基准测试？PostgreSQL的查询规划器将在同一张表格上产生不同的计划，具体取决于数据或其中包含的数据量的不同。确保始终使用真实数据进行测试。

3. 你的PostgreSQL配置如何？默认情况下，work_mem设置较低，我自己曾经遇到过涉及GROUP BY的情况，它人为地选择了错误的计划，因为它根本没有认为它有足够的工作内存来对结果进行排序。

4. 你的Java代码是否在与数据库相同的机器上运行？如果不是，则可能会看到不同计算机之间的差异，而不是方法之间的差异。

5. 你是否缺少索引？PostgreSQL不会为外键自动创建索引，只会为主键创建。我也曾因此受挫，但如果你在谷歌上搜索，就能找到一个脚本来检测和添加缺失的外键索引。

没有检查查询计划的情况下，揣测PostgreSQL为给定的查询选择了什么实现策略是不明智的。

- Daniel Lyons

---

关于 work_mem 的补充说明：与 JVM 默认分配的内存相比，其默认设置非常低。如果 Java 可以将所有 (customer,max_order_id) 元组保存在内存中，但 PostgreSQL 却不能... 那将是一场非常不公平的竞赛。;-) - A.H.