It's another one of those case by case deals.
The poor have medicaid (and can't afford it anyway).
The ultra wealthy tend to absorb the risk.
In my experience, the "weathly" (not ultra wealthy) tend to prefer owning it. It's a miss small or miss big option. Missing small means they paid the premiums (which they could afford) and didn't need the coverage. Missing big is they wish they could have the 500k-1M back that they spent on care.
The group in the middle is where the product flirts between needing to be owned or not. Most of that crowd will have between $500,000 and $2,000,000 in accessible assets. Technically that's not enough to self insure but far too much to ever qualify for medicaid. A large LTC claim can wipe out 25% or more of that number during retirement and destroy well laid plans for distribution from retirement accounts or planned inheritances. If that's where you see yourself landing during retirement, then seriously consider the product.
Over the past 6-7 years that insurance market has gotten crushed by the low interest environment and higher than expected claims. Every major carrier has increased premiums on inforce business and new business and many have left the market completely and shut down their LTC products. It's not a cheap product, but as the OP noted, the alternative is much worse. The odds of using the product are extremely high as well; with a relatively healthy 60 year old couple, there is a better than 65% chance that one of them will have a LTC insurance claim. That ranks behind life insurance (100% you're going to die), and health insurance, but far higher than things like a house fire, auto wreck, or disability claim.
Like any insurance that takes into account age and health, it's cheaper the younger you purchase it. But that needs to be balanced out with the practicality of how soon is too soon. I know a few CFP's that purchased it for their college kids to lock in rates and insurability (I personally think that's overkill). Most advisors that I know that are comfortable wading deeply into the insurance conversations begin the discussions with clients in their late 40's or early 50's. IMO, waiting until 60 is really walking your clients up to the edge of the cliff; by waiting that late you have a much higher risk that the client can't get the coverage (and in long-term care, if you get declined by one company, you're likely declined by all).