2 ms·
I like it as an emergency measure, but I often see them used when there's a shallow understanding of operating the db. Before using a hint or rewriting a query
by phamilton 3y ago
I like it as an emergency measure, but I often see them used when there's a shallow understanding of operating the db.
Before using a hint or rewriting a query to force a specific plan, I try and push the team to do these things:
1. Run `vacuum analyze` and tune the auto vacuum settings. This fixes issues surprisingly often.
2. Increase statistics on the table.
3. Tweak planner settings globally or just for the query. Stuff like `set local join_collapse_limit=1` can fix a bad plan. This is pretty similar to hinting, so not a huge argument that this is better beyond not requiring an extension.
- franckpachot 3y agoAll those methods are try and guess. With hints you can have a scientific approach to understand why the bad plan has been chosen and find the right plan. Then, you can address the root cause. join_collapse_limit=1 may set the join order but not the join direction, so that's not enough if cardinality is misestimated. And pg_hint_plan can set this parameter for one statement if that's what you want, better than setting for the transaction
- Yeroc 3y agoIn a database supporting several applications or even just a large data model it can be rather difficult to ensure a global setting to the query planner doesn't cause a regression to other queries. A query hint can be a nice way to quickly solve a performance issue without risk of regressing elsewhere. Agree they should be used as a last resort and as a sort term fix while a better, longer term fix is investigated but they are a critical tool to have in your tool box especially as postgres moves into the business critical domains occupied by the commercial database vendors.
- phamilton 3y ago`set local` allows tuning query planner settings scoped to a transaction, so you don't have to do it globally.