Combined indexes

In my job, as a professional PostgreSQL support vendor, I am often asked about the difference between combined and individual indexes. In this section, I will try to shed some light on this question.

The general rule is that if a single index can answer your question, it is usually the best choice. However, you cannot index all possible combinations of fields people are filtering on. What you can do instead is use the properties of combined indexes to achieve as much gain as possible.

Let's suppose we have a table containing three columns: postal_code, last_name, and first_name. A telephone book would make use of a combined index like this. You will see that data is ordered by location. Within the same location, data will be sorted by last name and first name.

The following table shows which operations are possible, given the three-column index:

If columns are indexed separately, you will most likely end up seeing bitmap scans. Of course, a single, hand-tailored index is better.