PostgreSQL TopN 插件优势分析

topn 插件地址：https://github.com/citusdata/postgresql-topn

topn 插件可以在秒级内求出出现频率前 n 高的元组。

背景问题

假如有如下一张表，我们的目标是求出以下人名中，出现频率前 n 高的人名。举个例子，n = 2 的情况下，出现频率前 2 高的是 Emily 和 Alex。

NAME
Bob
Emily
Alex
Emily
Emily
Bob
Andy
Alex
Emily
Alex

SQL 解法

如果我们想通过普通 SQL 的方法去求的话，需要以下几步：

1. 通过 group by 语句做人名的聚合，并统计出现频数，存入表 t（不一定需要存进另一张表，这是为了方便讲解）；
2. 将聚合的结果表 t 按照频数排序；
3. 通过对表 t 进行 limit n 语句限制，最终统计出出现频率前 n 高的。

可能的问题

还是上面那张结构相同的表，假如表里面有 1000000000 条或者更多的元组，我们要求出现频率前 1000 大的。
如果还是用 SQL 去解决的话，会有类似的一种情况出现：可能出现 100000000 条不同的元组。这些元组的出现频率很低，但是它们依然存在于 表 t 中。这样会导致上述第二步出现非常多的冗余元组的排序，并且这是外部排序，需要不停的对磁盘进行 IO ，非常浪费时间。

TopN 插件

TopN 插件用一种较为巧妙的方法来解决了这一步，同样还是这张表，它用以下几步来解决：

1. 使用 topn_add_agg 进行聚合，统计出每条元组出现的频数，存成一个哈希表，表的每一项维护一个 <item，频数> 的二元组；
2. 使用 topn 函数读取哈希表并进行排序，并求出前 n 大；

乍一看好像差不多，其实核心点就在于这张哈希表。TopN 插件通过设定哈希表的大小 topn.number_of_counters（默认为 1000）。在第一步聚合的时候不断进行淘汰，如果哈希表已满，那么就将出现频率较低的 item 从哈希表中淘汰，始终维护哈希表的大小不超过指定值。上一章所说的那些低频元组将在这一步被淘汰掉。

在第二步排序的时候，直接将哈希表读入到内存中，无需再进行外部排序，然后对哈希表中的表项按照频数排序，由于哈希表的大小默认并不大，所以可以在一个一秒内求出出现频数 topn 的元组。

注意：这样也存在问题，即只能求出一个近似解。因为可能出现某些元组，在前面出现了几次，然后一直没出现被哈希表淘汰了，后续再进来的时候频数会从头开始算起，会对结果有一些影响。

其他优势

除了性能优势外，该插件还有以下优势：

1. topn_add 函数可以将一条新的条目插入一个已有的哈希表中，不用重新聚合；
2. topn_union 函数可以将两个哈希表合并，重新计算出一个新的哈希表。