基于PostgreSQL的索引条件聚合查询

Question

如何优化PostgreSQL上的条件聚合查询？

这类查询的一个例子是：

SELECT SUM(score) FROM "games" WHERE "PlayerId" = 23 AND "status" = 'FINAL';

索引(PlayerId, status)将有助于过滤器，任何技巧或想法，以优化聚合后过滤？

理想的情况是不涉及外部缓存机制，以及每次更新“游戏”表时保持运行和的保存机制--类似于缓存的工作方式，但更可靠。

Answer 1

您必须创建一个特殊的表来包含和。

然后在原始表上创建触发器，添加插入的值，减去已删除的值，对更新执行这两项操作，并在截断时将值设置为零。

您可以看到count聚合在我的博客文章中的一个很小的例子。与该示例不同的是，您必须有一个每个"PlayerId"和status只有一个和的求和表。

Answer 2

索引(PlayerId，status)将有助于过滤器，任何技巧或想法，以优化聚合后过滤？

是的，在("PlayerId", status,score)或("PlayerId", score) WHERE status='FINAL'上建立索引。然后，可以从索引中检索和汇总分数，而无需访问表，只进行索引扫描。

要做到这一点，你可能不得不在桌子上保持安静，以保证大部分页面都是可见的。但是，如果表在密集地更新到位于随机位置的行，那么它甚至可能不可能足够频繁地真空。

这是最简单的，也是我的第一选择。如果不够好，则可以使用触发器实时维护汇总表，或者尝试使用分区将相关数据保持在磁盘上。