ORDER BY Without LIMIT Makes the Engine Sort Every Row You Produced
Sorting has to see all the rows before it can emit the first one, so an ORDER BY over a large result is paid in full even if your application only reads the first screen. Adding LIMIT changes the problem: the engine can keep just the best N rows as it goes, which is far cheaper, and if an index already holds the rows in that order it can stop after N without sorting at all. The lesson is that ORDER BY and LIMIT are one decision, not two.
Questions this Concept answers
- Why does adding `LIMIT 20` to a query that already has `ORDER BY` make the sorting step cheaper?
pgexplain/docs/rules/top_n_heapsort.md at master · bright98/pgexplain
This page explains what PostgreSQL does when a query says ORDER BY with a LIMIT. Rather than sorting every row and throwing most away, it keeps a small heap of just the best N rows as it reads, a str…