Postgres Slow ROW_NUMBER() function 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)
   -&gt;  WindowAgg  (cost=0.43..208332.50 rows=1999999 width=49) (actual time=0.086..1043.536 rows=500020 loops=1)
         -&gt;  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?
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 =&lt;rel_pages / 10&gt;);


Where &lt;rel_pages / 10&gt; needs to manually computed and the numeric value plugged in.