PostgreSQL treats poor planning from the optimizer as a bug, not something that humans should be forced to work around. File bug reports on the mailing list, don't hack up your SQL.
Using CTEs you are still able to hack the optimizer and while in pg12 they're making that fence optional it is remaining an option, that seems to be their primary conceit to needing to bludgeon an underperformant query - though, per their policy, queries that remain underperformant are getting rarer as they fix up the planner more and more.
I reiterate that no optimizer is perfect. Which means that if PostgreSQL redid some obscure statistic and a common query just switched to a far worse query plan, you've got a disaster and no easy way to fix it. Even if you know the right query plan, there is no easy way to fix it.
Similarly "this query worked fine in development and staging, but mysteriously sucks in production" is not what you want to learn while deploying to production.
Yes, I've seen both failure modes. No it wasn't pretty. And no, "File a bug report, wait for the next version of PostgreSQL to have a fix" is not an acceptable action plan.
In general if you've got a working site and working queries, there are two possible outcomes to changing the query plan. Either nobody notices because the plan was already good enough. Or everybody is upset because the new plan is worse. I don't care if 99% of the time it turns out well and nobody notices, that 1% keeps people up at night.
The top missing feature in PostgreSQL is this. If the query plan is good, allow the query plan to get locked and not change. Predictability beats perfection in production environments.
This assumes a use-case where there is a "site" (i.e. a business layer in front of PG that can abstract away deficiencies in the query plan by adding e.g. caching.)
If you're directly working with PG, interactively writing OLAP reporting queries, you can have "working queries" that take ~300s (still practical enough to build your report) switch to taking milliseconds after an autovacuum (if, for example, a table being joined against was recently populated via ETL and hadn't been vacuumed yet.) People do notice that.
Sorry I have played that game before with SQL server back when it didn't have good hinting, having to fool the optimizer into getting a sane plan through trial and error and being at the mercy of waiting for a new release to fix the issue.
Yes PG is better being open source, but fundamentally having dealt with this for many years relying on the optimizer with no manual override when needed is highly counter productive. There are way too many edge cases for any optimizer. As a human I can easily see the proper order of operations and indexes to use if only I could just tell that to the system.
Most of the time you don't need to do this but its critical when you do and the system needs a way to do it.
Or is it not advanced enough?
Looks like it does whats needed although having to specify in a special kind of comment is not as ergonomic as just some keywords in the language itself.