I have created the following table in postgres.
test=# \d leaderboard_scores;
Table "public.leaderboard_scores"
Column | Type | Collation | Nullable | Default
------------+-----------------------------+-----------+----------+------------------------------------------------
id | integer | | not null | nextval('leaderboard_scores_id_seq'::regclass)
user_id | character varying(45) | | not null |
score | integer | | |
created_at | timestamp without time zone | | |
Indexes:
"leaderboard_scores_pkey" PRIMARY KEY, btree (id)
"leaderboard_scores_user_id_key" UNIQUE CONSTRAINT, btree (user_id)
"score_back_user_id" btree (score DESC) INCLUDE (user_id)
My table size is 2M rows
My Use cases are:
To fetch users with max scores (this is running fast)
To get the rank of a particular user
To acheive number 2 I was hoping to use row_number window function but that is running very very slow.
The query I am using
select user_id, row_number() over (order by score desc) from leaderboard_scores order by score desc offset 500000 limit 20;
This query is taking around 900 ms which is too much to get a rank for a given user.
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=52083.47..52085.56 rows=20 width=49) (actual time=1074.712..1074.755 rows=20 loops=1)
-> WindowAgg (cost=0.43..208332.50 rows=1999999 width=49) (actual time=0.086..1043.536 rows=500020 loops=1)
-> Index Only Scan using score_back_user_id on leaderboard_scores (cost=0.43..178332.52 rows=1999999 width=41) (actual time=0.074..807.340 rows=500021 loops=1)
Heap Fetches: 500021
Planning Time: 0.097 ms
Execution Time: 1074.783 ms
(6 rows)
How can I optimize to get a user's rank?