It varies from place to place, of course. People have talked about the overheated Bay Area housing market since I was really young. My parents were told they paid too much for their house in 1975. By far the best investment they ever made.
I, too, have paid exorbitant amounts for the two houses I've owned, but both have paid off, and then some. The market has been softer in the last few years, but I'm still looking at a house worth double what I and the bank paid for it 15 years ago. Pretty good investment of our down payment and mortgage payments. Used to feel bad for the guy who bought our first house--just as the 2008 recession was hitting full gear, but he's done quite well on it, too.
Good weather year round, a strong economy/strong local industries with a relatively low reliance on government spending, good education pipeline, easy access to other financial and business markets, strong cultural scene (for a lot of different affinity groups), access to all forms of entertainment/activities within relatively easy drives, and attractive scenery all appear to make up for a pretty high tax rate and a historically troublesome homelessness problem. People want to live here, businesses want to work here, and people keep paying for it. But we do lose people because of the cost. Portland, Seattle, Boise, and Austin all benefit/lose because of that. And that emigration has driven up the home costs in those places, too.