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):
enable_seqscanenable_mergejoinenable_indexscan
Not specific enough for you? Well, you can try plantuner to pick or forbid specific indexes.
Want to enforce join order? Try setting from_collapse_limit.
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.
Looks like I should have typed a little faster..I have a 3/4 finished blog entry on this same topic queued up!
Heh. Well, don’t let me stop you — you’re the one that should really be writing on this topic.
It was brought to our attention that it was not written within the last year (only posts from the last year are eligible
curt
air-jordan-9-retro
Those are not hints, the difference between “set mode” statements and hints is in scope. Hints are only relevant for the SQL in which they are specified, while “set mode” statements will affect all subsequent SQL statements as well.
These “set mode” statements are also inferior to hints with respect to their capabilities, as there is no way to force the optimizer to use specific index.
By adamantly refusing to implement hints, which exist in all other major databases, the creators of Postgres show two things:
1) They do not trust their users enough to allow them to control the optimizer. That is a very insulting attitude, to say the least. Hints are an option, nobody is forced to use them. The creators of Postgres are actually refusing to provide that option on the grounds of the user stupidity. I leave the further conclusions to the reader.
2) The Postgres is an art project, not a serious DB, fit for the commercial server rooms. By adamantly refusing to listen to repeated requests made by the corporate users like me, the creators of Postgres show why is Postgres still a small fish in the DB ocean and why is MySQL still the first choice when it comes to open source databases. As one of the Postgres “gurus”, has written, “Postgres doesn’t have hints because Postgres is not for profit”. Great. Thanks for the warning, Bubba!
After a “debate” on PostgreSQL performance group, I decided not to start any porting projects from Oracle. PostgreSQL is simply not there yet and it is questionable whether it will ever be. What is particularly infuriating is that some members of the PgSQL community are actually working for companies that sell closed source versions of PgSQL, which support hints and that they were advocating against hints. That leaves a bitter taste in my mouth and raises questions about how trustworthy both the company and the open source database that they are advocating are. Ingres lost to Oracle. Postgres community seems adamant to repeat the same mistakes and lose to Oracle once again. My advice to the Oraclites looking for an alternative is that Postgres is not it.