[rchowe]

Also that the order of columns matters in multi-column indices (although some index types can technically use any column), so the first column in the list should be something like a partition key or something that will slice down the data a lot.

[jiaaro]

And this is even true for some more exotic indexes where you might not expect it like exclusion constraints with gist. It’s much more expensive to maintain with a range as the first column and a highly selective id for the second column than the other way around (you want the highly selective column first)

[magicalhippo]

Perhaps PG is different but for the DB we use the ordering usually wins out over raw selectivity. Meaning it'll prefer an multi-column index that matches the ORDER BY even if it has a fair bit less selectivity.

Due to this we sometimes have to add multiple indexes with different permutations of the same columns.