When does an index make queries slower?

Low cardinality is a big one. Index on something like gender or status with 2-3 values: each value matches a huge part of the table, so using the index means jumping around to random rows. A sequential scan is faster, optimizers often just ignore that index.

Other ones: using a function on the column (WHERE LOWER(email) = ...) skips a normal index, and lots of indexes on a write-heavy table.