如何使用Postgresql有效地获得一系列排名用户(排行榜)

然而,从数据库中获得一系列排名的解决方案都不是高效的.

问题很简单假设我们有一个带有“id”列和另一个INTEGER列的Postgres表,其值不唯一,但是我们有一个此列的索引.

例如表可以是：

CREATE TABLE my_game_users (id serial PRIMARY KEY,rating INTEGER NOT NULL);

目标

>定义用户在“评级”列下降订购用户的排名
>能够查询以任何特定用户为中心的新“排名”排序的?50位用户的列表
>例如,我们可能会返回排名为{15,16,…,64,65}的用户,其中中心用户的排名是#40
性能必须扩大,例如10万用户不到80 ms.

尝试#1：row_number()窗口函数

WITH my_ranks AS 
  (SELECT my_game_users.*,row_number() OVER (ORDER BY rating DESC) AS rank
   FROM my_game_users)
SELECT *
FROM my_ranks
WHERE rank >= 4000 AND rank <= 4050
ORDER BY rank ASC;

这个“工作”,但查询平均550ms与快速笔记本电脑上的100,000用户没有任何其他真正的工作完成.

我尝试添加索引,并重新使用该查询以不使用“WITH”语法,没有任何工作来加速.

尝试#2 – 计算具有更高评级值的行数
我尝试过这样的查询：

SELECT  t1.*,(SELECT  COUNT(*)
   FROM my_game_users t2
   WHERE (t1.rating,-t1.id) <= (t2.rating,-t2.id)
  ) AS rank
FROM my_game_users t1
WHERE id = 2000;

这是体面的,这个查询需要大约120ms,100,000用户具有随机评分.但是,这只能返回具有特定id(2000)的用户的排名.

我看不到任何有效的方法来扩展这个查询以获得一系列的排名.任何尝试扩展这个查询都是非常缓慢的.

我只知道“中心”用户的ID,因为用户必须按照排序顺序排列,才知道哪个用户在范围内！

尝试#3：内存中有序树

我最终使用Java TreeSet来存储这个排名.每当将新用户插入数据库或用户的评级更改时,我可以更新TreeSet.

这是超快的,大约25毫秒与100,000用户.

但是,它仅在服务请求的Webapp节点上更新.我使用Heroku,并将为我的应用程序部署多个节点.因此,我需要为服务器添加一个计划任务,以便每小时重新构建此排名树,以确保节点不会太同步！

如果有人知道在Postgres中有效的方法可以全面解决问题,那么我都是耳朵！