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 |           |          |
    "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?

  • please specify the postgresql version in the question. ty – n3ko Dec 16 '19 at 22:58

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.

| improve this answer | |
  • Yes the query does not exactly get the rank but I thought row_number could help out but this query itself is so slow. What query do you suggest I use to get a user's rank? – Sidhant Dec 16 '19 at 21:14
  • Also even after doing manual vacuum, it's the same performance – Sidhant Dec 16 '19 at 21:16
  • That should have helped. Can you turn on track_io_timing and do EXPLAIN (ANALYZE, BUFFERS)? – jjanes Dec 16 '19 at 23:14

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.