[phamilton]

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]

All 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]

In 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]

`set local` allows tuning query planner settings scoped to a transaction, so you don't have to do it globally.