So far, we haven't done anything special to the select query. Let's order the records and see what happens:

indextest=# EXPLAIN SELECT * FROM indextest
indextest-# WHERE id < 1000000
indextest-# ORDER BY letter;
QUERY PLAN
-------------------------------------------------------------------------------------------------
Sort (cost=175674.07..178089.92 rows=966338 width=25)
Sort Key: letter
-> Index Scan using unique_id_index on indextest (cost=0.00..22269.57 rows=966338 width=25)
Index Cond: (id < 1000000)
(4 rows)

This time we get different results again, we get two sets of results. Let's break them up and look at them separately.

Sort (cost=175674.07..178089.92 rows=966338 width=25)

Sort Key: letter

Because we're sorting the data before returning it, this takes time.

To perform the sort, PostgreSQL will need to look at the disk a lot of times.

Note: Sorting always takes place before returning of results. The whole table has to be sorted, then the parts you want returned - you don't want a random section of data returned and those results sorted - these are incorrect results. While it's not quite so obvious in this example, if we were ordering by id instead of letter it would be more obvious.

The index scan of the result plan is to limit the results we're returning and is the same as we saw in the previous example.