sql - 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

该表包含 10M 条记录。查询需要……好吧，大约需要 2 或 3 分钟。老实说，我对此并不满意，而且我认为 10M 对于需要这么长时间的查询来说并不是很大。 (或者是..？？)

此后，我让 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排序(sort)。最后，逐行读取排序后的表并在线性时间内聚合。好吧，这不是我认为的最佳算法，如果我自己实现它，我会使用哈希表并在第一遍中构建结果。没关系。

看来是按userid排序才耗时这么长。所以添加了一个索引:

CREATE INDEX ratings_userid_index ON ratings (userid)

不幸的是，这没有帮助，性能保持不变。我绝对不认为自己是高级用户，而且我相信我做的事情从根本上是错误的。但是，这就是我卡住的地方。对于如何使查询在合理的时间内执行的任何想法，我将不胜感激。请注意:PostgreSQL 工作进程在执行期间 100% 使用了我的一个 CPU 内核，这表明磁盘访问不是主要瓶颈。

编辑

根据@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 秒即可完成。

Answer 1

考虑您的查询可能如何返回结果...您可以构建一个可变长度的散列并创建/增加它的值；或者您可以按用户 ID 和计数对所有行进行排序。从计算上讲，后一种选择更便宜。这就是 Postgres 所做的。

然后考虑如何对数据进行排序，同时考虑磁盘 IO。一种选择是打开磁盘页面 A、B、C、D 等，然后在内存中按 userid 对行进行排序。换句话说，seq 扫描后跟排序。另一种称为索引扫描的选项是使用索引按顺序拉取行:访问页面 B，然后是 D，​​然后是 A，然后再次访问 B，再次访问 A，访问 C，令人作呕。

索引扫描在按顺序拉取少量行时是有效的；与其说按顺序获取很多行——更不用说按顺序获取所有行了。因此，您获得的计划是最佳计划:

1. 犁抛所有行(顺序扫描)
2. 按条件对行进行排序
3. 按标准计算行数

问题是，您要对大约 1000 万行进行排序，以便按用户 ID 对它们进行计数。除了投资更多 RAM 和超高速 SSD，没有什么能让事情变得更快。

但是，您可以完全避免此查询。要么:

• 计算您实际需要的少数用户的评分——使用 where 子句——而不是提取整个集合；或
• 将 ratings_count 字段添加到您的用户表并使用评分触发器来维护计数。
• 如果精确计数不如模糊概念更重要，则使用实体化 View 。