Sorted Index and Order By

There are a few things here…

1. The order of a result set from a query is not guaranteed because it has an index. In general with SQL you cannot assume any particular order of the result set unless you specify the order by clause.
2. The advantage of creating a b-tree index when using order by is that you can traverse the index in the same order you want the results to be in. This is faster than scanning the table which most likely won’t be stored in the same order.
3. All b-tree indices are sorted (it helps ensure the lookup is fast). It just depends on if you want it sorted in ascending or descending order.

Thats a great explanation. Thank you so much!

The accepted answer misses an important detail, as an index also works backwards. So if you have ASC index, a DESC query uses that index without having to scan the table.

So to extend the answer:

You might wonder why bother providing all four options, when two options together with the possibility of backward scan would cover all the variants of ORDER BY . In single-column indexes the options are indeed redundant, but in multicolumn indexes they can be useful. Consider a two-column index on (x, y) : this can satisfy ORDER BY x, y if we scan forward, or ORDER BY x DESC, y DESC if we scan backward. But it might be that the application frequently needs to use ORDER BY x ASC, y DESC . There is no way to get that ordering from a plain index, but it is possible if the index is defined as (x ASC, y DESC) or (x DESC, y ASC) .