Databases · Middle · Question 71 of 75
Why can ORDER BY created_at DESC LIMIT 10 be very fast on a huge table?
- 1
LIMITmakes every query fast regardless of indexes - 2PostgreSQL caches the last 10 rows of every table
- ✓Correct answer: With an index on
created_at, PostgreSQL reads the first 10 index entries and stops, without sorting the whole table
Explanation
Without a suitable index the database must read and sort everything, then return 10 rows. Add the filter columns first in the index when there's a WHERE.