Multi-Column Indexes

CREATE INDEX first_name_last_name_age_idx
  ON users (first_name, last_name, age);

A multi-column index is sorted by the first column first, the second column next, and so forth. A lookup on only the first column in a multi-column index gets almost all of the performance improvements that it would get from its own single-column index. However, lookups on only the second or third column will have very degraded performance.

Unless you have specific reasons to do something special, only add multi-column indexes if you're doing frequent lookups on a specific combination of columns.

We frequently need to look up all the transactions between two specific users. There's a page on the website that allows a user to find all the payments they've made to a friend by that friend's name.

Add an index on the user_id and recipient_id columns called user_id_recipient_id_idx to speed up our app!

Make sure the user_id is the first column in the index, so we can also use this index to help our queries that only care about user_id.