If you think "needs upstream fix", then no database is suitable for use, because they all choose poor plans in many edge cases. This is why most databases have extensions that let you hint or force index use (not Postgres, however; a mistake with mitigation), or support parenthesising your joins to force an evaluation order (this is an indirect way of forcing index use or join order, a mitigation), etc.

Query planning is something where a poor choice can have serious performance ramifications, because n is usually much larger than in most programs. Analyzing the algorithmic complexity of a piece of SQL takes some experience and experimentation, and with different table stats the query planner may make different decisions. It can be worthwhile limiting the planner's discretion to get more predictable performance.

(I work on a product where many of the features can be expressed in terms of relational algebra. Often, both the best performing and quickest to write and test implementation logic is a bunch of SQL, and not the kind of CRUD that is easily wrapped with an ORM. What would make my life easier is a SQL linter that, given a model of costs, would prevent people writing queries that scale only linearly with specific table sizes. I've accumulated sufficient intuition that I could do this for MySQL at this point.)