Should the column used to order results be included in the index of a postgresql table?

I am creating indexes for a PostgreSQL database. I would like to know whether the column(s) used to order results in a PostgreSQL statement should be included in the index.

Assume I have created a table with label ‘table1’ in a PostgreSQL database with columns labelled ‘col1’, ‘col2’ and ‘col3’.

I would like to execute the following query:

SELECT * FROM table1 WHERE col1 = 'word1' AND col2 = 'word2' ORDER BY col3;

I know that an index for this search should include all columns referenced in the WHERE clause so, in this case, the index would include col1 and col2.

Should the index also include col3?

Answer

Because you have equality comparisons, Postgres should be able to use an index on (col1, col2, col3).

The first two columns are used for the where clause; the last for the order by.

Note that this is very specifically for your query. And it assumes that the collations on the strings are compatible and there is no type conversion. Also, the comparisons in the where need to be equality comparisons for the index to be used for the order by.

I believe that the direction of the order by also has to match the direction defined in the index.

I have found that the MySQL documentation on multi-column indexes is a good introduction to the topic. It focuses on the where clause, but it gives a good flavor of when indexes can and cannot be used — and the rules tend to be similar across databases.