APPENDIX · P7 · POSTGRESQL · FOR SLIDE 16
← Go to Slide 16
P7. Top-N and Rank in a B-tree
EXPLAIN (COSTS OFF)
SELECT player_id, rating FROM season_ratings
WHERE season_id = 7
ORDER BY rating DESC, achieved_at ASC, player_id ASC
LIMIT 10;
SELECT 1 + (SELECT count(*) FROM season_ratings r
WHERE r.season_id = me.season_id AND
(r.rating > me.rating OR (r.rating = me.rating AND
(r.achieved_at, r.player_id) <
(me.achieved_at, me.player_id)))) AS place
FROM season_ratings me
WHERE me.season_id = 7 AND me.player_id = 1042;
What the result should be
A top-N plan depends on data and statistics;
under the right conditions—index-based selection.
place = 11152 (web data)
Missing player_id: 0 rows, not in 1st place.
See
EXPLAIN (ANALYZE, BUFFERS).
Index
Tie
Rank Price
(season_id, rating DESC, achieved_at, player_id) — The B-tree is updated incrementally.
In the web data, 900001 and 900002 have a rating of 1900: they had previously achieved a higher rating.
An exact rank is more valuable than a top rank. A sorted set returns ZREVRANK in O(log N) time.
Compare Redis with indexed SQL. The web data includes 20,002 players; measure performance and response times based on your own workload.