Indexes for sorting

B-tree indexes store their entries in ascending order. Starting in PostgreSQL 8.3, the nulls are also stored in them, defaulting to last in the table. You can reverse both of those defaults, such as the following:

CREATE INDEX i ON t(v DESC NULLS FIRST);

The query planner will use an index that returns rows in sorted order in some cases when ORDER BY has been specified. Generally, this only happens when a small number of rows are being returned by the table, which is the opposite of what you might expect. This is because reading the index blocks is optional. You can always derive the data by sorting the data, and the planner considers index reads to be random instead of sequential access. If large percentage of the table is being read, directly reading the table—sequentially—and sorting the result will have a lower estimated cost than the redundant disk access of reading both the random index blocks and the data blocks too. Expecting to see an index...