If a column is used in the where clause, using that column in the order by clause lets the system use the index on that column if there is one and if the values allow the system to use that index.

indextest=# EXPLAIN SELECT *
indextest-# FROM indextest
indextest-# WHERE number < 1000000
indextest-# ORDER BY number;
QUERY PLAN
------------------------------------------------------------------------
Sort (cost=104166.11..104299.35 rows=53295 width=25)
Sort Key: number
-> Seq Scan on indextest (cost=0.00..99265.00 rows=53295 width=25)
Filter: (number < 1000000)
(4 rows)

In this case, the number range is too large for PostgreSQL to use the index, there are too many possible numbers less than 1,000,000. If we either reduce the number range or add another where clause, PostgreSQL can then use the index.

indextest=# EXPLAIN SELECT *
indextest-# FROM indextest
indextest-# WHERE number > 700000 AND number < 1000000
indextest-# ORDER BY number;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Index Scan using indextest_index_number on indextest (cost=0.00..58650.66 rows=14988 width=25)
Index Cond: ((number > 700000) AND (number < 1000000))
(2 rows)


Of course, the more restrictive the where clause (in this particular case, the numbers can be in a 30,000 range) the better performance. If we reduced the numbers again, the cost and number of records returned decrease.

Let's add a where clause that uses two different columns.

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


The Index Cond tells us which clause is the most restrictive part of the statement, the filter reduces the number of results.

PostgreSQL is smart enough to apply the index cond restriction first, then use the filter to cut down the records again.