Как эффективно получить ряд ранжированных пользователей (для лидеров), используя Postgresql

Я прочитал много сообщений по этой теме, таких как mysql-get-rank-from-leaderboards.

Однако ни одно из решений не эффективно в масштабе для получения диапазона рангов из базы данных.

Проблема проста. Предположим, что у нас есть таблица Postgres с столбцом "id" и другим столбцом INTEGER, значения которого не уникальны, но у нас есть индекс для этого столбца.

например. таблица может быть:

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

Цель

• Определите ранг для пользователей, заказывающих пользователей в столбце "рейтинг" по убыванию.
• Уметь запрашивать список из ~ 50 пользователей, заказанных этим новым "рангом", с центром в любом конкретном пользователе.
• Например, мы можем возвращать пользователей с рангом {15, 16,..., 64, 65}, где центральный пользователь имеет ранг № 40
• Производительность должна масштабироваться, например. не менее 80 мс для 100 000 пользователей.

Попытка # 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;

Это "работает", но запросы усредняют 550 мс с 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;

Это прилично, этот запрос занимает около 120 мс, при этом 100 000 пользователей имеют случайные рейтинги. Однако это возвращает только ранг для пользователя с определенным идентификатором (2000).

Я не вижу эффективного способа расширить этот запрос, чтобы получить ряд рангов. Любая попытка расширить это делает очень медленный запрос.

Я знаю только идентификатор пользователя "center", так как пользователи должны быть упорядочены по рангу, прежде чем мы узнаем, какие из них находятся в диапазоне!

Попытка # 3: упорядоченное в памяти дерево

В итоге я использовал Java TreeSet для хранения рангов. Я могу обновить TreeSet всякий раз, когда новый пользователь вставлен в базу данных или изменяется рейтинг пользователя.

Это супер быстрый, около 25 мс с 100 000 пользователей.

Однако у него есть серьезный недостаток, который он обновил только на Webapp node, обслуживающем запрос. Я использую Heroku и развожу несколько узлов для своего приложения. Таким образом, мне нужно было добавить запланированную задачу для сервера, чтобы каждый раз создавать таблицу ранжирования, чтобы убедиться, что узлы не слишком из-за синхронизации!

Если кто-нибудь знает об эффективном способе сделать это в Postgres с полным решением, то я все уши!

Ответы

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;

select * , rank() over (order by rating desc) rank 
from my_game_users 
order by rating desc
limit 50 offset 4000

select *, rank() over (order by rating desc) rank 
from my_game_users 
order by rating desc
limit 50 offset 15