The starships left with the optimism. In the 50s there was a greater demand for stories with an unconstrained vision of the future where growth and expansion amount to flourishing. Later generations that lived in the excesses of growth saw it as the source of an intensifying dystopia. They stood athwart history and demanded decelleration. Star Trek lost ground to Terminator, Foundation to Neuromancer. Escaping sideways into fantasy gained the popularity lost by escapes into the future.
I predict a correlation between space-based scifi sales and polls on whether the country is heading in the right direction.
I think I would argue that we already see the sea change in the later Dune books
The inner systems become hidebound as they continue to reach out they find that not only has someone beaten them there, they have become Other as well as antagonistic and expansionist.
I still find myself quite taken by some sci-fi writing. Iain M Banks works, Rajaniemi, and Joan Slonczewski. The “problem” is that they are not popular the way Harry Potter or isekai are.
I kinda have the same feeling for music as well, the best phases for metal and punk music are usually when stuff is going to shit. When everything looks bright and good the music just doesn't have the same quality, people are not angry all the time.
I don't know, to the East of the Iron Curtain science fiction wasn't mostly about future optimism (at least after the initial "we're building a better society" optimism had been brutally murdered during the 1950s and 60s), but often a critical mirror of then-current society transported into the future to escape state censorship.
Maybe it's as simple as free societies not having the evolutionary pressure to produce great literature that requires an interested and intelligent reader to decode the hidden messages written between the lines ;)
Stated with a different spin, the detached-from-reality takes of Campbell-era SF finally became too strained to enjoy.
Why did people want to escape Earth though? Maybe they felt Earth was past saving.
Also, we've realized the scientific reality that traveling faster than light is likely impossible, and the vast distances to other habitable planets would mean tens of thousands of years of travel even with the most efficient technology.
Interstellar space is also hostile to life, and any life present at the destination will not use the same DNA coding for protein (if gene expression even works that way).
We also do not yet have the technology for a complete survey of nearby habitable planets.
It is not an encouraging line of thought.