Postgres Slow ROW_NUMBER() function
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.