Descending and Mixed-Direction Indexes Serve ORDER BY Without a Sort
A B-tree can be read in either direction, so a plain index already serves ORDER BY created_at and ORDER BY created_at DESC equally well. What it cannot do is serve a mixed order such as ORDER BY priority ASC, created_at DESC — for that you declare the directions in the index itself. This is how a 'newest first' list page returns its first twenty rows without the engine sorting the whole table.
Questions this Concept answers
- Why does a plain ascending index already serve `ORDER BY created_at DESC` without a sort?
11.4. Indexes and ORDER BY
A B-tree index stores its entries in order, so the database can walk the index and get rows back already sorted, skipping a separate sort step. This works in either direction, because the index can b…