I have been in the following situation, and anecdotally I know many other DBAs and Developers have also bitten by such a situation. Hence I created this Postgres extension, to save us from the tyranny of a Query Optimizer :-). Following is directly from the project's Overview section.<p>You work very hard to tune your queries; to get the exact query execution plan that satisfies your performance needs.<p>But some time later, after either the data has changed suffiently, or the statistics have changed sufficiently, Postgres planner chooses to suddenly pick a query execution plan that makes your application look unresponsive. This shows up on your dashboard as an outage. Now you find yourself running against the clock, to rewrite the query, test it, change the application/ORM, and deploy the changes, and still just hoping that the production database accepts your offerings, and executes your query just acceptably enough, so you can go back to bed.<p>But if you use the pg_plan_guarantee extension, you will never face such a situation; we can guarantee that. This extension provides a mechanism, using which you can set your execution plan in stone; that is, Postgres will execute the plan you give it, or it will throw an error, but it will never try to guess a plan for you.<p>Because the data and/or statistics change gradually over time, you can be fairly confident that the guaraneed-plan will continue to serve your needs in a graceful manner. That is, your guaranted-plan will degrade gracefully, as opposed to suddenly, at the most inopportune time.
Some interesting discussion starting on Reddit as well.<p>Notably, sir_bok says: ... it is better to be uniformly slow than to risk a performance cliff where everything seems fine until it suddenly isn't. At least uniformly bad performance (or a gradual performance decline) is predictable and can be worked around by the users. You also have more time to react.<p><a href="https://www.reddit.com/r/PostgreSQL/comments/tpw6fq/new_extension_pg_plan_guarantee_guarantee_that/" rel="nofollow">https://www.reddit.com/r/PostgreSQL/comments/tpw6fq/new_exte...</a>