Databases · Senior · Question 56 of 75

Why can a `LIMIT` query sometimes be slower than the same query without `LIMIT`?

1LIMIT disables indexes
2LIMIT makes PostgreSQL sort the whole table twice
✓Correct answer: The planner may pick a plan that walks an index in order expecting to find matches quickly; if matches are rare, it scans far more rows

Explanation

This happens with skewed or correlated data. Better statistics, a composite index matching filter and order, or rewriting the query helps.

Further reading

Test yourself

Take the Databases · Senior quiz: questions and options are shuffled, and you get a review of your mistakes at the end.