All old database kernels have architectural limitations that emerge because there is no realistic way to anticipate distant future workloads and hardware, and it is nearly impossible to materially modify the architecture in practice.
The saving grace is that for some common data distributions, the degraded statistical models are still pretty close to representative of the data.
However, some data distributions badly expose the fact that the statistical models were improperly constructed to fit resource limits. In these cases, the statistical model is unpredictable and erratic, and will change every time the statistics collection is run even if the data does not change.
We are not talking about an extraordinary amount of statistics data. I would expect databases to have a special structure optimized for this purpose rather than using an OLTP row. The Postgres approach to statistics collection was reasonable a couple decades ago, but modern workloads expose it more frequently now.
FWIW, I still use Postgres a fair amount because it is very solid within its limits. It is the reason I am familiar with its sharp edges.
The root cause is that the statistics collection architecture of Postgres that feeds the query optimizer has some deep design flaws. Under some conditions it will produce statistical models of the data that are badly skewed in random ways, which causes the query optimizer which uses those statistics to make erratic and incorrect decisions.
Fixing the statistics collection would be a massive undertaking, the issue is architectural in nature. Adding hints would provide a reasonable workaround and is probably easier in terms of development complexity than fixing the statistics collection.
It is possible to reduce the probability of these bugs by reorganizing the data. The idea of changing your data model to work around a database bug is pretty horrific but companies do it.