PostgreSQL use a "top-N sort heapsort" for this. You can see that in effect when doing "EXPLAIN ANALYZE" on a query like this. Without an index you cannot avoid

PostgreSQL use a "top-N sort heapsort" for this. You can see that in effect when doing "EXPLAIN ANALYZE" on a query like this. Without an index you cannot avoid a full table scan. However the top-N heapsort avoids allocating memory for ALL the rows, since you only care about the first 5 ones For example, I had a 6 million entry table sitting around, and asked for top 5 rows by an unindexed column. With LIMIT 5 applied it told me: Sort Method: top-N heapsort Memory: 95kB Without: Sort Method: quicksort Memory: 577595kB So if Postgres had to store all the 6 million rows sorted it would need 577 MB of work memory. If (see SHOW work_mem) it was below that that would lead to it writing a lot of temporary files to disk: Sort Method: external merge Disk: 144072kB Note how Postgres is more wasteful with memory usage than disk writes for temp storage. Generally a query that requires you to do a full table scan on a large table should be used sparingly however.