Composite indexes: the order matters more than you think

just wanted to share a recent learning that cost us a week of debugging. we had a really slow query on a large table, taking seconds instead of milliseconds. the query involved multiple conditions, and we had a composite index on `(col_a, col_b, col_c)`. turns out, the query was filtering on `col_c` first, then `col_a`, and `col_b`. postgres wasn't effectively using the index because the order of columns in the `where` clause didn't match the index definition, or rather, the query optimizer couldn't use it optimally for the `col_c` filter as it wasn't the leading column. once we reordered the index to `(col_c, col_a, col_b)`, or created a specific index for that query, query times dropped to single-digit milliseconds. it's a classic mistake, but a good reminder that the order of columns in composite indexes matters way more than you might think, especially when you have varying query patterns. always analyze your `explain` output carefully.