PostgreSQL中GroupAggregate的速度缓慢问题

Question

在PostgreSQL 9.2中，我有一个用户评分的物品表：

   id   | userid | itemid |    rating     |      timestamp      |      !update_time
--------+--------+--------+---------------+---------------------+------------------------
 522241 | 3991   | 6887   |  0.1111111111 | 2005-06-20 03:13:56 | 2013-10-11 17:50:24.545
 522242 | 3991   | 6934   |  0.1111111111 | 2005-04-05 02:25:21 | 2013-10-11 17:50:24.545
 522243 | 3991   | 6936   | -0.1111111111 | 2005-03-31 03:17:25 | 2013-10-11 17:50:24.545
 522244 | 3991   | 6942   | -0.3333333333 | 2005-03-24 04:38:02 | 2013-10-11 17:50:24.545
 522245 | 3991   | 6951   | -0.5555555556 | 2005-06-20 03:15:35 | 2013-10-11 17:50:24.545
 ...    | ...    | ...    | ...           | ...                 | ...

我希望执行一个非常简单的查询：对于每个用户，在数据库中选择评级的总数。

我使用以下简单的方法：

SELECT userid, COUNT(*) AS rcount
FROM ratings
GROUP BY userid

表格包含1000万条记录。查询需要大约2到3分钟的时间。说实话，我对此不太满意，我认为对于这么长的查询，1000万并不是一个很大的数字。（或者它确实是吗..??）

因此，我要求PostgreSQL向我显示执行计划：

EXPLAIN SELECT userid, COUNT(*) AS rcount
FROM ratings
GROUP BY userid

这将导致：

GroupAggregate  (cost=1756177.54..1831423.30 rows=24535 width=5)
  ->  Sort  (cost=1756177.54..1781177.68 rows=10000054 width=5)
      Sort Key: userid
      ->  Seq Scan on ratings  (cost=0.00..183334.54 rows=10000054 width=5)

我将其理解为：首先，整个表从磁盘中读取（顺序扫描）。其次，通过n*log(n)进行userid排序（排序）。最后，在线性时间内逐行读取排序后的表并进行汇总。我认为这不是最优算法，如果我自己要实现它，我会使用哈希表并在第一遍构建结果。没关系。

看起来是按userid排序花费了很长时间。因此添加了一个索引：

CREATE INDEX ratings_userid_index ON ratings (userid)

很遗憾，这并没有改善性能，我肯定自己不是高级用户，我相信我做错了一些基本的事情，但这就是我卡住的地方。我希望有任何想法可以让查询在合理的时间内执行。还有一点需要注意的是：在执行过程中，PostgreSQL 工作进程占用了我的一个 CPU 内核的 100％，这表明磁盘访问不是主要的瓶颈。

编辑

按 @a_horse_with_no_name 的要求。哇，对我来说相当高级：

EXPLAIN (analyze on, buffers on, verbose on)
SELECT userid,COUNT(userid) AS rcount
FROM movielens_10m.ratings
GROUP BY userId

输出：

GroupAggregate  (cost=1756177.54..1831423.30 rows=24535 width=5) (actual time=110666.899..127168.304 rows=69878 loops=1)
  Output: userid, count(userid)
  Buffers: shared hit=906 read=82433, temp read=19358 written=19358
  ->  Sort  (cost=1756177.54..1781177.68 rows=10000054 width=5) (actual time=110666.838..125180.683 rows=10000054 loops=1)
        Output: userid
        Sort Key: ratings.userid
        Sort Method: external merge  Disk: 154840kB
        Buffers: shared hit=906 read=82433, temp read=19358 written=19358
        ->  Seq Scan on movielens_10m.ratings  (cost=0.00..183334.54 rows=10000054 width=5) (actual time=0.019..2889.583 rows=10000054 loops=1)
              Output: userid
              Buffers: shared hit=901 read=82433
Total runtime: 127193.524 ms

编辑2

@a_horse_with_no_name的评论解决了该问题。 我很高兴分享我的发现:

SET work_mem = '1MB';
EXPLAIN SELECT userid,COUNT(userid) AS rcount
FROM movielens_10m.ratings
GROUP BY userId

产生与上面相同的结果：

GroupAggregate  (cost=1756177.54..1831423.30 rows=24535 width=5)
  ->  Sort  (cost=1756177.54..1781177.68 rows=10000054 width=5)
      Sort Key: userid
      ->  Seq Scan on ratings  (cost=0.00..183334.54 rows=10000054 width=5)

然而，

SET work_mem = '10MB';
EXPLAIN SELECT userid,COUNT(userid) AS rcount
FROM movielens_10m.ratings
GROUP BY userId

提供

HashAggregate  (cost=233334.81..233580.16 rows=24535 width=5)
  ->  Seq Scan on ratings  (cost=0.00..183334.54 rows=10000054 width=5)

现在查询只需要大约3.5秒就能完成。

- Tregoreg

---

请发布 explain (analyze on, buffers on, verbose on) .. 的输出结果。 - user330315

你可能需要考虑的一件事是将这个查询放入一个“物化视图”中，并在评分插入/触发的过程中（偶尔）更新该视图。也就是说，缓存这个查询。 - Paul Nathan

你主要的问题不是顺序扫描，而是需要在磁盘上进行排序。在运行查询之前，可以尝试设置work_mem='250MB'（甚至更高），如果你有足够的内存的话。 - user330315

@a_horse_with_no_name 太好了，完美解决了问题！实际上，在我的情况下，即使是10 MB也足够了。如果我使用“set work_mem ='1MB'”，那么计划将使用我发布的排序分组聚合。但是当我使用“set work_mem ='10MB'”时，计划会更改为哈希分组聚合。现在这很有道理！ - Tregoreg

Answer 1

考虑查询可能会返回什么结果...您可以构建一个可变长度的哈希并创建/增加其值；或者您可以按userid对所有行进行排序并计数。从计算上讲，后一种选择更便宜。这就是Postgres所做的。

然后考虑如何按磁盘IO排序数据。一种选择是打开磁盘页面A，B, C, D等，然后在内存中按userid排序行。换句话说，顺序扫描后排序。另一种选项称为索引扫描，即使用索引按顺序提取行：访问页面B，然后D，然后A，然后再次B，再次A，C等。

当按顺序获取少量行时，索引扫描非常高效；如果按顺序获取许多行 - 更不用说所有行 - 则不那么高效。因此，您正在获得的计划是最佳的：

1. 浏览所有行（seq scan） 2. 按条件对行进行排序 3. 按条件计数行

麻烦的是，您要按userid排序大约1000万行才能对其进行计数。除了投资更多的RAM和超快速的SSD外，没有任何方法可以使事情变得更快。

但是，您可以完全避免这个查询。要么：

- 对实际需要的少数用户进行评分计数（使用where子句），而不是提取整个集合；或者 - 向您的用户表添加ratings_count字段，并使用评级触发器来维护计数。 - 如果精确计数比大致了解更重要，则使用材料化视图。

- Denis de Bernardy

---

请原谅我的疑虑，但我不明白为什么哈希表方法在计算上是次优的。实际上，如果我无法在PostgreSQL层面解决问题，这就是我计划要做的。我几乎可以保证我会比2分钟更快... - Tregoreg

@Tregoreg：我猜想有一些考虑因素，比如“通过排序行，你可以逐步构建集合并避免查找”，以及“通过排序行，我们不会冒险哈希值过大而无法放入内存”的问题。但我建议你把你的疑问带到pg-hackers列表中。Tom Lane可能会给你确切的原因，为什么他们采用这种方式而不是其他方式。 - Denis de Bernardy

如果我将查询缩小为“SELECT userid FROM ratings”，它只需要大约3秒钟。虽然仍不完美，我喜欢你提出的两个解决方案，但我认为这是可以接受的。只是出于好奇，我想知道在PostgreSQL中是否可以使用原始查询获得更好的结果。因为如果不能，我将来会完全避免这样的查询，并尽可能将应用程序逻辑移出SQL。 - Tregoreg

是的，我理解对于拥有数十亿行的表格而言，内存消耗可能会引起关注。无论如何，感谢您清晰的解释，似乎您是绝对正确的——PostgreSQL工作进程从磁盘读取850 MB并写入150 MB，但仅消耗6 MB的RAM。 - Tregoreg

通过执行 select userid from ratings 得到的查询计划跳过了排序和计数，因此您基本上是在测量穿过整个表格需要多长时间。据我所知，对于这个特定的查询，您无法做得比查询计划更好。当然，如果您实际上需要一些用户并运行：select userid，count(*) from ratings where id in (1,2,3,4)，那么情况就完全不同了。在 ratings (userid) 上建立索引应该会使查询非常快。对于顶级评分者，应该按照 users (ratings_count) 建立索引。 - Denis de Bernardy

Answer 2

尝试像下面这样做，因为COUNT(*)和COUNT(userid)之间有很大的区别。

SELECT userid, COUNT(userid) AS rcount
FROM ratings
GROUP BY userid

- Sanal K

---

我已经尝试过那个了。可悲的是，它没有任何区别。执行时间仍然是2分钟，并且在执行计划中估算的数字也完全相同。 - Tregoreg

@Tregoreg，你有加入到评分表吗？还是你正在尝试用你在这里发布的相同查询？ - Sanal K

我正在使用与此帖子中完全相同的查询。实际上，我最初使用了更复杂的连接查询，但这是我成功从中隔离出来的“瓶颈核心”。 - Tregoreg

Answer 3

您可以尝试运行“VACUUM ANALYZE ratings”来更新数据统计信息，这样优化器就可以选择更好的方案来执行SQL语句。

- Pengcheng Lei