4 ms·
A composite, or multi-column index, is not the same thing as two separately indexed columns. They are distinct patterns with different trade-offs: "A multicol
by michelpp 5y ago
A composite, or multi-column index, is not the same thing as two separately indexed columns. They are distinct patterns with different trade-offs:
"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."
https://www.postgresql.org/docs/current/indexes-multicolumn.html https://www.postgresql.org/docs/current/indexes-multicolumn....
- inshadows 5y agoWhy not both then? I mean three... 2 for each column, one multi-column.
- jimktrains2 5y agoStorage overhead and the overhead of keeping the index up to date. if you have a write-heavy workload or one where transactions can lock the index, that can cause throughput issues.
- aeyes 5y agoIf you have a multi-column index on (number_start, number_end) Postgres will use it even if you are only filtering on number_start. An additional single column index on number_start would be redundant.
- qeternity 5y agoYes, I know, this is my entire point. They should have tried a composite index, which they didn’t.