To what extent should this be done for the best performance?
It should be done if your average target device has unnacceptable FPS. That said, lots of things can affect performance, re-using shaders and materials is just one thing to try when optimizing.
For your terrain example, it's true that the most advanced terrain systems use multiple shader variants for close and far range, but it's not always necessary. If you were to do this, it's quite some work as terrains have particular kinds of shaders and you have to blend between two as well. Finally, that would only be 1, 2, maybe 3 shaders. Quite nothing compared to everything that goes on top of it.
In Vulkan those things get better but keeping material count low is still means less work for the renderer.
I don't have a particular benchmark to link, it would be interesting to create one with concrete examples because it's not immediately obvious what parameters of a
SpatialMaterial will significantly change the actual shader behind it, and by how much the difference would be.