With some material limitations, Postgres can use a leading subset of multi-column indexes. From the documentation:
A multicolumn B-tree index can be used with query conditions that involve any subset of the index’s columns, but the index is most efficient when there are constraints on the leading (leftmost) columns. The exact rule is that equality constraints on leading columns, plus any inequality constraints on the first column that does not have an equality constraint, will be used to limit the portion of the index that is scanned. Constraints on columns to the right of these columns are checked in the index, so they save visits to the table proper, but they do not reduce the portion of the index that has to be scanned. For example, given an index on
(a, b, c)and a query conditionWHERE a = 5 AND b >= 42 AND c < 77, the index would have to be scanned from the first entry witha= 5 andb= 42 up through the last entry witha= 5. Index entries withc>= 77 would be skipped, but they’d still have to be scanned through. This index could in principle be used for queries that have constraints onband/orcwith no constraint ona— but the entire index would have to be scanned, so in most cases the planner would prefer a sequential table scan over using the index.
But as Ben says, not appropriate to apply unique constraints.
