Mastering PostgreSQL 9.6

Making use of sorted output

B-tree indexes are not only useful to find rows. They are also useful 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, it means that it is possible to find the top N rows of a table in a fraction of a millisecond. ...