Comment by SoftTalker 7 hours ago
Decades ago we used to provide hints in queries based on "knowing the data" but modern optimizers have a lot better statistics on indexes, and the need to tell the query optimizer what to do should be rare.
> There is a real need to be able to take key queries and say, "don't change the way you run this query".
We've hit this with MSSQL too. Suddenly production is down because for whatever reason MSSQL decided to forget its good plan and instead table scan, and then continue to reuse that cached table-scanning plan.
For one specific query MSSQL likes to do this with at a certain customer we've so far just added the minutes since start of year as a dummy column, while we work on more pressing issues. Very blunt, yet it works.
Yes, but the problem is that optimizers will sometimes change join conditions without warning in production.
There is a real need to be able to take key queries and say, "don't change the way you run this query". Most databases offer this. Unfortunately PostgreSQL doesn't. There are ways to force the join (eg using a series of queries with explicit temporary tables), but all create overhead. And the result is that a PostgreSQL website will sometimes change a good query plan to a bad one, then have problems. Just because it is Tuesday.