Show HN: pg_plan_guarantee – Postgres Query Optimizer Hints, on Steroids. | Hacker News Reader

Show HN: pg_plan_guarantee – Postgres Query Optimizer Hints, on Steroids.

github.com

46 pointsgurjeet5 comments

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.

You work very hard to tune your queries; to get the exact query execution plan that satisfies your performance needs.

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.

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.

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.

I love this! computer science is all about abstractions and guarantees, and it's great to have an option for more control. One thought about graceful degradation: would it be easy/possible to build in configuration to "when guarantee cannot be met, only throw an error x% of the time"? This keeps the situational awareness (errors about different query plans) while having an escape hatch: if postgres is updated & the new version decides to never generate good queries, the queries will still happen for some ~95% of users, just slower until the team can read through the new version's instructions to get the plan they need.

Whether that is a good idea or not depends on a lot of factors. Having a cost based query planner is a huge advantage most of the time. As you say data and statistics change over time. But that also means, that the optimal query plan might change over time.

Even if your extensions sets the query plan in stone, you might have severe degradation of query performance over time, because now a different plan might be optimal.

Additionally, the optimal query plan can depend on the parameter values of a query.

Just thought of this idea, let me know what you/others think. How about introducing a parameter that controls what percentage of time the frozen-plan will be used, and the rest of the time the regular optimizer will be allowed to create a new plan based on current data and statistics.

This will allow the users to choose the mix. So if I were to use this feature, I'd set it to use frozen-pan 90% of the time, and 10% of the time the most-current plan. This way, irrespective of whether the most current plan is better or worse than the frozen-plan, my application's performance will usually be as expected. But there would be spikes (upward or downward) on my dashboards, and I'd be inclined to investigate those spikes. Depending on what I find, I would either change/update the frozen plan, or increase the percentage even higher, to reduce the number of spikes in performance.

Some interesting discussion starting on Reddit as well.

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.

https://www.reddit.com/r/PostgreSQL/comments/tpw6fq/new_exte...

Reply on news.ycombinator.com