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?
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
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
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.