Hacker News Reader

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.

View on HN

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)

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.

Reply on news.ycombinator.com