postgresql - Postgres count(*) 优化思路

Question

我目前正在从事一个项目，该项目涉及跟踪用户及其使用我的数据库(RDMS 的 PostgreSQL)的操作，并且在尝试对每个用户的出现执行 COUNT(*) 时遇到了问题。我想要的是能够有效地计算每个用户出现在每个记录中的次数，并且还能够查看特定上的计数strong>日期范围。

那么，问题是我们如何实现从表内容中统计用户出现的总次数，以及我们如何统计某个日期范围内的总次数。

我尝试过的

您可能知道，Postgres 不能很好地支持使用索引的 COUNT(*)，因此我们必须考虑其他方法来减少它查看的记录数以加快查询速度。所以我的第一个方法是创建一个表来跟踪用户有与他们相关的日志消息的次数，以及在哪一天(类似于物化 View 背后的想法，但我不想不断刷新物化 View 用我的计数查询)。这是我想出的:

CREATE TABLE users_counts(user varchar(65536), counter int default 0, day date);

CREATE RULE inc_user_date_count 
AS ON INSERT TO main_table 
DO ALSO UPDATE users_counts SET counter = counter + 1 
WHERE user = NEW.user AND day = DATE(NEW.date_);

每次在我的“main_table”中插入一条新记录时，我们都会更新当前的 users_counts 表以增加日期等于新记录日期且用户名相同的记录。

p>

注意“main_table”中的 date_ 列是时间戳，因此我必须将新记录 date_ 转换为 DATE 类型。

问题是，如果当天的新表“users_count”中不存在用户列值，那么什么都不会更新。

这是我的问题:

我如何编写规则，以便我们检查当天是否存在用户，如果存在，则增加该计数器，否则插入用户、日期和计数器为 1 的新行；

我还想知道我的方法是否可行，或者是否有任何我没有想过的想法。随着我的数据库的增长，执行计数的效率越来越低，所以我想避免任何性能瓶颈。

编辑 1:我实际上能够通过创建一个单独的规则来解决这个问题，但我不确定这是否正确:

CREATE RULE test_insert AS ON INSERT TO main_table 
DO ALSO INSERT INTO users_counts(user, counter, day) 
SELECT NEW.user, 1, DATE(NEW.date) 
WHERE NOT EXISTS (SELECT user FROM users.log_messages WHERE user = NEW.user_);

基本上，如果用户不存在于我名为 user_counts 的 CACHED 表中，并且上面的第一条规则更新计数，则会发生插入。

我不确定的是我怎么知道什么时候首先调用哪个规则，是更新规则还是插入。而且必须有更好的方法，我如何结合这两个规则？这可以用一个函数来完成吗？

Answer 1

的确，当涉及到 count(*) 查询时，postgresql 是出了名的慢。但是，如果您确实有一个限制条目数的 where 子句，则查询会快得多。如果您使用的是 postgresql 9.2 或更新版本，则此查询将与在 mysql 中一样快，因为在 9.2 中添加了仅索引扫描，但最好解释分析您的查询以确保。

我的解决方案有意义吗？

只要您的解释分析显示未使用仅索引扫描，就可以了。基于触发器的解决方案，例如您已经采用的解决方案，得到了广泛的使用。但是正如您已经意识到的那样，初始状态出现了问题(无论是进行更新还是插入)。

先调用哪个规则

Multiple rules on the same table and same event type are applied in alphabetical name order.

来自 http://www.postgresql.org/docs/9.1/static/sql-createrule.html这同样适用于触发器。如果您希望首先执行特定规则，请更改其名称，使其按字母顺序排在前面。

如何结合这两个规则？

一个解决方案是修改您的规则以执行 upsert (查看该页面底部的示例更新插入)。另一种是用初始值填充计数器表。诀窍是同时创建触发器以避免错误。这blog post解释得很好。

虽然初始设置会很慢，但每个单独的插入可能会更快。两个相反的因素是 WHERE NOT EXISTS 查询的缓慢与捕获异常的开销。

Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

获取上面链接的 postgresql 文档页面。