> http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want
>
> No. 2 on the list.
Heck, *I* wrote that text.
I quote:
"Optimizer hints are used to work around problems in the optimizer and
introduce upgrade and maintenance issues. We would rather have the
problems reported and fixed. We have discussed a more sophisticated
system of per-class cost adjustment instead, but a specification remains
to be developed."
That seems pretty straightforwards. There are even links to prior
discussions about what kind of system would work. I don't think this
text needs any adjustment; that's our clear consensus on the hint issue:
we want a tool which works better than what we've seen in other databases.
Quite frankly, the main reason why most DBMSes have a hinting system has
nothing to do with the quality of optimizer and everything to do with
DBAs who think they're smarter than the optimizer (incorrectly). Oracle
has a darned good query optimizer, and SQL server's is even better.
However, there are a lot of undereducated or fossilized DBAs out there
who don't trust the query planner and want to override it in fairly
arbitrary ways; I refer you to the collected works of Dan Tow, for example.
In many cases Hints are used by DBAs in "emergency" situations because
they are easier than figuring out what the underlying issue is, even
when that could be done relatively simply. Improving diagnostic query
tools would be a much better approach here; for example, the team
working on hypothetical indexes has a lot to offer. If you can figure
out what's really wrong with the query in 10 minutes, you don't need a hint.
Yes, I occasionally run across cases where having a query tweaking
system would help me fix a pathological failure in the planner.
However, even on data warehouses that's less than 0.1% of the queries I
deal with, so this isn't exactly a common event. And any hinting system
we develop needs to address those specific cases, NOT a hypothetical
case which can't be tested. Otherwise we'll implement hints which
actually don't improve queries.
Commercial DBMSes have to give in to what their big paying customers
want, no matter how stupid it is. I'm grateful that I can work on a DBMS
-- the third most popular SQL DBMS in the world -- which can focus on
quality instead.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
