This is a counterpoint to Josh’s recent post: Why PostgreSQL Doesn’t Have Query Hints. I don’t really disagree, except that I think that there are many different definitions of “hints” floating around, leading to a lot of confusion. I could subtitle this post “More Terminology Confusion” after my previous entry.
So, let’s pick a reasonable definition: “hints are some mechanism to influence the SQL planner to choose a better plan”. Why did I choose that definition? Because it’s the actual use case. If a user encounters a bad plan, or an unstable plan, they need a way to get it to choose a better plan. There’s plenty of room to argue about the right way to do that and the wrong way, but almost every DBMS allows some form of hints. Including PostgreSQL.
Here are a few planner variables you can tweak (out of many):
Not specific enough for you? Well, you can try plantuner to pick or forbid specific indexes.
Want to enforce join order? Try setting
Want to get even more specific? You can set the selectivity of individual operators.
There is a philosophical difference between PostgreSQL’s approach and that of many other systems. In PostgreSQL, it is encouraged to specify costs and selectivities more than exact plans. There are good reasons for that, such as sheer number of possible plans for even moderately complex queries (as Josh points out). Additionally, specifying exact plans tends to lead you into exactly the type of trouble you are trying to avoid by specifying hints in the first place — after input cardinalities change, the previous plan may now be a very poor one.
PostgreSQL clearly has a set of mechanisms that could be called “hints”. It turns out that there are actually quite a lot of ways to control the plan in postgres; but they generally aren’t recommended except as a solution to a specific problem someone posts to the performance list. That is part of the postgresql culture: a bit like getting a prescription for a doctor, so that the doctor can see the whole picture, help you look for alternative solutions, and weigh the side effects of the treatment against the benefits. I’m exaggerating, of course — these tweaks are documented (well, most of them), and anyone can use them; you just won’t hear them shouted from the rooftops as recommendations.
Except in this post, I suppose, which you should use at your own risk.