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

0
votes

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?

1
sqldatabasepostgresqlindexingsql-order-by
Yes but after the other two columns in the index key. Think about it: you want the engine to do a point lookup on col1, col2, then order the results by col3. This completely changes if you are using inequalities > <> <= etc – Charlieface
Thanks. So does that mean I should create a separate index for col3, or just that it should be the final property of the same index? – Joshua Flood
The latter. One index with key columns in this order col1, col2, col3 don't forget include (other_columns) if your version supports it. And don't use select * if you can help it, specify the columns you need. – Charlieface
Amazing thanks! Just using * at the moment in development because I will be adding columns later. – Joshua Flood

1 Answers

1
votes

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.