[Semaphor]

Depending on your application, you can very accurately estimate it. And in a case I had yesterday, it involved stringy numbers because of a third party system, so I could indeed add a computed persisted column that converts our number to a VARCHAR, add a 9th index with a lot of fields on that computed column and then save… almost nothing compared to just scanning 6k rows.

[magicalhippo]

We had one such table for years. The one day I get an emergency call from support, big customer don't get their responses and it's critical for their workflow.

After some digging I found the service generating the responses got killed due to being unresponsive.

Turns out our customer got a new client which caused them to suddenly generate 100x as much data as others in this module. And that caused a lot more data in a table that joined this non-indexed table.

So everything was working, it was just the performance went over a cliff in a matter of days due to the missing index.

Added the required index and it's been humming ever since.

I've had similar experiences, and so these days I'm very liberal with indexes.

We have read-heavy workloads, if you mostly insert then sure be conservative.