LIMIT Changes the Plan: Why the Database Optimises for the First Rows
Adding LIMIT 10 does not just trim the output; it changes which plan is cheapest, because now the startup cost matters more than the total cost. The planner will happily choose an index scan that returns rows in the right order and stop early, rather than a faster-overall plan that must produce everything first. That is why a query is instant with LIMIT and slow without it, and also why a LIMIT on top of a Sort or a HashAggregate saves nothing: those steps cannot emit anything until they have consumed everything.
Questions this Concept answers
- Why is a query sometimes instant with `LIMIT 10` and slow without it?
Top-N queries: fetch only the first N rows
Asking for only the first few rows changes the whole shape of a query plan. Without a limit the database must produce the entire result and sort it; with a limit it can instead read rows from an inde…