I agree about not putting too much detail, but I think the typical D&D campaign setting core sourcebook has the right amount. I don't actually own many, just the 3ed FRCS and Rokugan, and I think they are fine. Then FR regional books can go into much deeper details if wanted, and adventures will go into full details of a much smaller scale locales.
One additional thing to think about, is the distinction between society and the wilderness.
It's a personal thing, but mostly I tend to set adventures in the wilderness (especially dungeons, apparently I am a bit old-school with that...), and I make up my own details, thus I'm fine with a campaign setting giving me only overland maps (which I really need to have a sense of consistency, otherwise I cannot design large continents by myself) with scattered suggestions for points-of-interest, and then leaving me the job for detailing them and mapping on a small scale.
OTOH, I have more difficulties designing things such as the overarching/background plots and the relationships between people/organizations of power, so I would really like having the campaign setting doing that job for me. I suppose it's different for others... but in my case, I can't really come up with good intricacies of conflicting agendas between NPCs and power groups, especially if some historical perspective is added.
So what I really need to be ready in a campaign setting book is those large-scale, overarching "structures" in both geography and society.