> Are the trade-offs measurable?
Yes, but you need the context about what is the correct tradeoff for your use case. If you've got a service that depends on fast writes then adding latency via extra indices for improved read speed may not be an acceptable trade off. It depends on your application though.
Not just indexing, but table partitions, materialized views, keeping things in-memory...