sql - 从分组列中采样重复值的最佳性能

Question

这个问题是关于 first_value() 的功能的，使用其他功能或解决方法。

这也是关于大表中“性能上的小提升”。使用例如。 max()在下面解释的上下文中，需要虚假比较。即使速度很快，也会产生一些额外的成本。

---

这个典型的查询

SELECT x, y, count(*) as n 
FROM t 
GROUP BY x, y;

需要重复 GROUP BY 中的所有列返回多列。这样做的语法糖是使用位置引用:

SELECT x, y, count(*) as n 
FROM t 
GROUP BY x, 2  -- imagine that 2, 3, etc. are repeated with x

有时不仅需要糖，还需要一些语义来理解复杂的上下文:

SELECT x, COALESCE(y,z), count(*) as n 
FROM t 
GROUP BY x, y, z  -- y and z are not "real need" grouping clauses?

我可以想象许多其他复杂的上下文。让我们看看通常的解决方案:

SELECT x, max(y) as y, count(*) as n 
FROM t 
GROUP BY x  -- best semantic! no need for other columns here

哪里max()函数可以是任何“sample()”(例如第一个或最后一个值)。什么都不做的性能优于max() ，例如聚合函数 first_value() , 但它需要一个 WINDOW ，所以失去了性能。有一些老建议to implement first/last agg functions in C .

是否有比max() 性能更好的“快速获取任何一个值”聚合函数？或 GROUP BY X,2,... ?
也许是最近发布的一些新功能？

Answer 1

如果您真的不关心集合中的哪个成员被选中，并且如果您不需要计算额外的聚合(如计数)，则可以使用 DISTINCT ON (x) 快速而简单的替代方法 没有 ORDER BY:

SELECT DISTINCT ON (x) x, y, z FROM t;

x、y 和 z 来自同一行，但该行是从具有相同行的每组行中任意挑选的x。

如果您无论如何都需要计数，那么您在性能方面的选择是有限的，因为在任何一种情况下都必须读取整个表。不过，您可以将它与窗口函数结合在同一个 SELECT 中:

SELECT DISTINCT ON (x) x, y, z, count(*) OVER (PARTITION BY x) AS x_count FROM t;

考虑 SELECT 查询中的事件序列:

• Best way to get result count before LIMIT was applied

根据要求，可能有更快的计数方法:

• Fast way to discover the row count of a table in PostgreSQL

结合GROUP BY，我认为获得一些性能的唯一现实选择是first_last_agg extension .但不要期望太多。

对于其他没有计数的用例(包括顶部的简单用例)，有更快的解决方案，具体取决于您的具体用例。特别是获得每组的“第一个”或“最后一个”值。模拟松散索引扫描。 (如 @Mihai commented ):

• Optimize GROUP BY query to retrieve latest record per user