Skip to content

Instantly share code, notes, and snippets.

@pcreux pcreux/index.sql
Created Jul 5, 2016

Embed
What would you like to do?
Postgresql rank() with LIMIT 1 = 10x speed up?
CREATE INDEX "index_patrons_on_amount_raised_in_cents" ON "patrons" ("amount_raised_in_cents" DESC)
SELECT ranks.rank
FROM (
SELECT id, rank() OVER (ORDER BY amount_raised_in_cents DESC) AS rank
FROM patrons
) ranks
WHERE id = 1
LIMIT 1
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.29..1429.42 rows=1 width=8)
-> Subquery Scan on ranks (cost=0.29..1429.42 rows=1 width=8)
Filter: (ranks.id = 1)
-> WindowAgg (cost=0.29..1304.41 rows=10001 width=8)
-> Index Scan using index_patrons_on_amount_raised_in_cents on patrons (cost=0.29..1154.39 rows=10001 width=8)
(5 rows)
(0.3ms)
SELECT ranks.rank
FROM (
SELECT id, rank() OVER (ORDER BY amount_raised_in_cents DESC) AS rank
FROM patrons
) ranks
WHERE id = 1
QUERY PLAN
-------------------------------------------------------------------------------
Subquery Scan on ranks (cost=990.47..1290.50 rows=1 width=8)
Filter: (ranks.id = 1)
-> WindowAgg (cost=990.47..1165.49 rows=10001 width=8)
-> Sort (cost=990.47..1015.47 rows=10001 width=8)
Sort Key: patrons.amount_raised_in_cents
-> Seq Scan on patrons (cost=0.00..326.01 rows=10001 width=8)
(6 rows)
(6.9ms)
@pcreux

This comment has been minimized.

Copy link
Owner Author

commented Jul 5, 2016

With 100,000 records postgresql uses the index with / without LIMIT 1. It's 10x faster with LIMIT 1.

I suspect that Postgresql doesn't know that the subquery returns unique IDs. Adding LIMIT 1 might make it stop scanning the subquery results on the first matching id.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.