In this example, we're ordering by the most restrictive part of the where clause. If we switch the order by, we get different results:

indextest=# EXPLAIN SELECT *
indextest-# FROM indextest
indextest-# WHERE id < 300000 AND number < 10000
indextest-# ORDER BY id;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort (cost=1998.32..1998.40 rows=33 width=25)
Sort Key: id
-> Index Scan using indextest_index_number on indextest (cost=0.00..1997.49 rows=33 width=25)
Index Cond: (number < 10000)
Filter: (id < 300000)
(5 rows)


In this case, the index scan and filter reduce the number of results and then PostgreSQL has to sort the results as we saw previously according to the id.

The difference between this plan and the previous plan is that PostgreSQL can do the sort as it retrieves results in the first case because it's ordering by the least number of records. In the second example, all the records have to be returned according to the restrictions in the where clause after the results get sorted. It can't be done in the one step.

If you use multiple order by clauses, the same process occurs except the sort step always occurs. PostgreSQL has to perform the first sort which it may be able to do on the fly, then it has to re-order those results again according to the next order clause and so on:

indextest=# EXPLAIN SELECT *
indextest-# FROM indextest
indextest-# WHERE id < 300000 AND number < 10000
indextest-# ORDER BY number, id, letter;
QUERY PLAN
---------------------------------------------------------------------------------------------------
Sort (cost=1998.32..1998.40 rows=33 width=25)
Sort Key: number, id, letter
-> Index Scan using indextest_index_number on indextest (cost=0.00..1997.49 rows=33 width=25)
Index Cond: (number < 10000)
Filter: (id < 300000)
(5 rows)

The records can be ordered in either ascending or descending order, the plan will be the same.


That covers the basic queries, as your queries get more complicated the plans will change and get a bit harder to read. These basics will allow you to read those more advanced plans and see what's going on.