Postgres Slow ROW_NUMBER() function

Question

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:

1. To fetch users with max scores (this is running fast)

2. 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?

Answer 1

You say you want the rank of a particular user, but that is not what your query does.

Heap Fetches: 500021

Vacuum your table to reduce the heap fetches. If the table is updated a lot, you may need to make changes to its autovac parameters to keep autovacuum running on it often enough.

For example,

alter table leaderboard_scores set (autovacuum_vacuum_scale_factor =0);
alter table leaderboard_scores set (autovacuum_vacuum_threshold =<rel_pages / 10>);

Where <rel_pages / 10> needs to manually computed and the numeric value plugged in.

Answer 2

create a temp table

The cost is clearly high, cause the high number of processed records (rows=500021). I think it wouldn't be much slower even without the index. Without the LIMIT the total time of the following would be ... lets say 2-3 sec?

DROP TABLE IF EXISTS temp_leaderboard_ranks;
SELECT user_id, row_number() OVER (ORDER BY score DESC)
  INTO temp_leaderboard_ranks
  FROM leaderboard_scores ORDER BY score DESC;
CREATE INDEX temp_lb_ranks_idx on temp_leaderboard_ranks(row_number)

or something

Another option is using a MATERIALIZED VIEW, mostly its the same as above, like you can create indices on it, but its easier if needs refresh and safer to use.

add the rank as a column

If the original table is changing fast and you need more realtime data, another option is to make the rank table permanent and use triggers keep it up to date.

• insert and delete records when the leaderboard_scores changes
• update all the affected records in the rank table if the scores column changes most of the time its only a few, which has a score between the NEW.score and OLD.score

If you decide for the trigger thing, you have to be careful with the change of ranks: if the score increases you must decrease the rank by one for all the IDs, which has less score then NEW.score and has higher rank then the previous rank of our ID, and also increase the rank of this ID (one or more ... or zero). If the score can decrease too in this case you have to do the opposite. Although it seems to be simple (and now i feel also, i explain it too mutch), this can be tricky with concurrent changes and multirow updates. If you put the rank in the leaderboard table, you can even create nice deadlocks too.