Indexes are a nightmare to get right. Often performance optimizations of SQL databases include removing indexes as much as adding indexes.
Indexes are a nightmare to get right. Often performance optimizations of SQL databases include removing indexes as much as adding indexes.
If you are seeing performance gains from removing indexes, then I'm assuming your workload is very heavy on writes/updates compared to reads.
But yes, the issue with too many indexes is more often that they harm write performance.
A related issue is indexes that are too wide, either covering many columns or “including” them. As well as eating disk space they also eat extra memory (and potentially cause extra IO load) when used (less rows per page, so more pages loaded into RAM for the same query).
Both problems together, too many indexes many of which are too wide, usually comes from blindly accepting recommendations from automated tools (particularly when they are right that there is a problem, and it is a problem that a given index may solve, but fixing the queries so existing indexes are useful could have a much greater effect than adding the indexes).
All useful tools can be used incorrectly - and I agree SQL is one of the more frequently misused ones. I think a lot of that is that it's one of the more powerful tools.