The author shows these indexes:
CREATE INDEX i_actor_1 ON actor (last_name);
CREATE INDEX i_actor_2 ON actor (last_name, first_name);
…and says that you don’t need the index on last_name because the two-column index supports queries on just its first column.
But wouldn’t the best option be to have separate indexes for last_name and first_name? Then you could query efficiently by either or both. I guess maintaining two one-column indexes is more expensive than maintaining one two-column index?
Two separate indexes on last_name and first_name is the most flexible solution for the reason you gave. It isn’t true that only one of them is ever used, often both indexes are scanned to create bitmaps which are then merged.
A single index on (last_name, first_name) can be very useful for queries like “WHERE last_name = ‘Smith’ ORDER BY first_name”. An index like that could even satisfy the query if it only fetches values from the two columns (index only scan).
Your typical query engine will not use two indexes simultaneously to lookup a value in a table. It will choose just one. Querying on both requires the two columns.
The index on last_name and first_name is good for predicate including last_name and first_name or just last_name. But it won’t be utilized for first_name except as a possible scan of the index.
So given the two indexes, you can eliminate the index with just last_name because that’s basically covered in the other.
However if you had queries with predicate on first_name alone, then a separate index would be beneficial.
If you use Rails then I recommend using my tool active_record_doctor to detect redundant indexes.