Mastering PostgreSQL 12 - Third Edition

Making use of sorted output

B-tree indexes are not only used to find rows; they are also used to feed sorted data to the next stage in the process:

test=# EXPLAIN SELECT *                FROM  t_test                ORDER BY id DESC  
               LIMIT 10;                              QUERY PLAN ---------------------------------------------------------------  Limit (cost=0.43..0.74 rows=10 width=9)    -> Index Scan Backward using idx_id on t_test          (cost=0.43..125505.43 rows=4000000 width=9) (2 rows)

In this case, the index already returns data in the right sort order, and therefore there is no need to sort the entire set of data. Reading the last 10 rows of the index will be enough to answer this query. Practically, this means that it is possible to find the top N rows of a table in a fraction of a millisecond. ...