PostgreSQL慢速计数()-触发是唯一的解决方案吗？

Question

我有一张列有帖子的表格，分类如下：

• 类型
• 标签
• 语言

所有这些“类别”都存储在下一个表(posts_types)中，并通过下一个表(posts_types_assignment)连接。

COUNTing in PostgreSQL非常慢 (我在那个表中有超过500 K的记录)，我需要得到按类型/tag/lang的任意组合分类的帖子数量。

如果我要通过触发器来解决这个问题，它将充满许多多级循环，这看起来不太好，而且很难维护。

还有其他的解决方案，如何有效地获得在任何类型/标记/语言中分类的实际帖子数量？

Answer 1

让我把这事说清楚。

你有一张桌子posts。你有一张桌子posts_types。这两家公司在posts_types_assignment上加入了很多人。这样的查询是很慢的：

SELECT count(*)
FROM posts p
  JOIN posts_types_assigment pta1
    ON p.id = pta1.post_id
  JOIN posts_types pt1
    ON pt1.id = pta1.post_type_id
      AND pt1.type = 'language'
      AND pt1.name = 'English'
  JOIN posts_types_assigment pta2
    ON p.id = pta2.post_id
  JOIN posts_types pt2
    ON pt2.id = pta2.post_type_id
      AND pt2.type = 'tag'
      AND pt2.name = 'awesome'

你想知道为什么速度慢得令人痛苦。

我的第一个注意是，如果在PostgreSQL表中而不是在联接中有标识符，那么posts所做的工作就会少得多。但这是一个没有意义的问题，这个决定已经做出了。

我更有用的一点是，我相信PostgreSQL有一个类似于Oracle的查询优化器。在这种情况下，为了限制它必须考虑的可能查询计划的组合爆炸，它只考虑以某个表开始的计划，然后每次重复连接多一个数据集。但是，这种查询计划在这里不起作用。您可以从pt1开始，获得1条记录，然后转到pta1，获取一堆记录，加入p，得到相同数量的记录，然后加入pta2，现在您得到了大量的记录，然后加入到pt2，只得到几条记录。加入pta2是缓慢的一步，因为数据库不知道需要哪些记录，因此必须为每个post和元数据(类型、语言或标记)的组合创建一个临时结果集。

如果这确实是你的问题，那么正确的计划是这样的。将pt1加入到pta1，在其上添加一个索引。将pt2连接到pta2，然后连接到第一个查询的结果，然后连接到p。那就数一数。这意味着我们不会得到很大的结果集。

如果是这种情况，就无法告诉查询优化器，一旦您想让它想出一种新的执行计划，就无法告诉它。但是有一种方法可以强迫它。

CREATE TEMPORARY TABLE t1
AS
SELECT pta*
FROM posts_types pt
  JOIN posts_types_assignment pta
    ON pt.id = pta.post_type_id
WHERE pt.type = 'language'
  AND pt.name = 'English';

CREATE INDEX idx1 ON t1 (post_id);

CREATE TEMPORARY TABLE t2
AS
SELECT pta*
FROM posts_types pt
  JOIN posts_types_assignment pta
    ON pt.id = pta.post_type_id
  JOIN t1
    ON t1.post_id = pta.post_id
WHERE pt.type = 'language'
  AND pt.name = 'English';

SELECT COUNT(*)
FROM posts p
  JOIN t1
    ON p.id = t1.post_id;

除了随机排印等，这可能会表现得更好。如果没有，请再次检查表上的索引。

Answer 2

正如btilly所指出的那样，如果他正确地猜到了模式，那么表的设计就没有什么帮助了--例如(至少乍一看)，拥有三个表( posts_tag(post_id,tag) post_lang(post_id,lang) post_type(post_id,type) )似乎更自然，效率更高。

除此之外(或者除此之外)，人们可以想到一个表或物化视图，它总结了所有可能的计数，其中包含了(lang,type,tag,nposts)列。当然，完全计算这一点将非常缓慢，但是(除了第一次)可以在“背景中”、在某些时间间隔内(如果数据变化不大，并且不需要确切的计数)，或者热切地使用触发器来完成。参见例如这里