Someone should feel free to correct me on this analysis, but it seems to me a significant factor here is the ratio of risk to reward, which has been alluded to in the discussion of diversification. But I also think that the risk-reward ratio on housing is skewed such that it's very difficult to come out ahead for the amount of risk involved.
I invest in equities via index funds because while there is no guarantee on returns, there is a historical precedent for the increase in value to exceed inflation, often by quite a bit, though the probability of large fluctuations is high, meaning it is a higher-risk investment. I do not leverage my investment in equities, which removes one kind of risk. I can also easily convert my equities to cash, which removes another kind of risk. And it's a wide swath of equities, which reduces another kind of risk. Even so, equity investments are on the high risk-high reward end of the spectrum.
In contrast, the historical precedent for the increase in value of housing is for it to keep pace with inflation but no more (low reward), and it is often a leveraged purchase (higher risk). Fluctuations in housing value are generally lower (???? 2008 US housing crash aside??) than with equities (lower risk). In total, I see housing (someplace you are going to live, not real estate investments) as medium risk (leveraged, highly local market, single 'investment' ie no ability to diversify)-medium to low reward (only keeping up with inflation, highly local market).
To move housing from medium to low reward to medium/high reward requires things that have already been mentioned: rental income commensurate with the purchase price/investment or a super-heated housing market. And in the case of the super-heated housing market, it also requires selling the house at some point and finding someplace else to live, either locally or leaving the area, which I would think adds to risk side of the equation and I think is part of why we say housing is not a(n good) investment.
Obviously there are factors that can flip some of these, like if part of the plan is to make a killing on the house in the shortish term and then do some geographic arbitrage and move to a lower cost of living/lower cost of housing area after the sale.
For me, the skew in risk and reward in housing means it's not a great place to park a lot of money that I need to live off of. See also: efficient frontier in modern portfolio theory, etc.