[ahachete]

> Aren't there two arguments for why this is bad?

> - db will generate new plans as necessary when row counts and values change. Putting in hints makes the plan rigid likely leading to headaches down the line.

> - as new Postgres comes out, it's planner will do a better job. Again forcing specific plan might force the planner into optimization that no longer is optimal.

It depends what is your priority. Most production environments want to favor predictability over raw performance. I'd rather trade 10% performance degradation in average for a consistent query performance.

Even if statistics or new versions could come with 10% better plans, I prefer that my query's performance is predictable and does not experience high p90s or even worse that you risk experiencing plan flips that turn your 0.2s 10K/qps query into a 40s query.

[kevincox]

100%, even if my performance slow degrades as my data changes I would much rather have gradual performance and be able to manually switch to a better plan at some point than have the DB suddenly switch a new plan which is probably better, but if not may cause a huge performance swing that takes my application offline.

It would be very interesting if there was some sort of system that ensured query plans don't change "randomly" but if it noticed that a new plan was expected to be better on most queries it would surface this in some interface that you could then test and approve.

Maybe it could also support gradual rollout, try this on 1% of queries first, then if it does improve performance it can be fully approved.

It would be extra cool if this was automatic. New plans could slowly be slowly rolled out by the DB and rolled back if they aren't actually better.