We now also have some empirical evidence on how the query planner evolve/devolve based on this decision. InnoDB always relies heavily on query hint, and recently it has given up on trying to get the planner to support some simple pagination queries correctly, after killing too many production systems that didn't add a hint: https://dev.mysql.com/worklog/task/?id=13929. It basically declares that "we know our query planner sucks for this class of queries, here's a flag to disable that portion of the query planner"
Meanwhile, in recent releases, Postgres started to allow statistics to be created for correlated columns, so that the query planner can have the necessary information to come up with the correct plan: https://www.2ndquadrant.com/en/blog/pg-phriday-crazy-correla...
> Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in.
This is actually the thing I hate the most about MSSQL. If I allow plans to be cached, then some queries would end up using some bad plans intended for parameters with very different data distribution. If I disallow plans to be cached, then the query planner can take 1~2 second just to come up with a plan for a rather simple query with 2 levels of nesting. A rock and a hard place.
This is about repeatability and stability. I would rather the DB run slower so long as it does so consistently and repeatably until a human can profile the issue and make a source code change to speed it up. Having the optimizer just decide in the middle of the night on one of a number of different production databases is a nightmare.
MSSQL now days lets you save and load full plans and freeze plans, but the reality is just some simple hints go a long way, like which index to use and what type of join to use.
Another surprise coming from MSSQL is PG does not cache plans at all. It actually spends time replanning every statement coming in. The client can manually prepare a plan but only reuse on the same connection.