On Fri, Nov 14, 2008 at 12:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> And, very often, they're not --- or more likely, they were smarter than
> the optimizer last year, but now conditions have changed. Failing to
> adapt to new conditions is exactly the problem with query hints, and
> in general with any insistence that plans should be "stable".
Well, at least they didn't have to wait a year to fix the problem.
Similarly, whether or not the plan changed due to bad hints or bad
plans, detecting the change is relatively easy, so I don't really see
an argument based on *why* the plan failed. In my, and many others
opinion, if you decide to take your query plan into your own hands,
it's your problem if it fails. I do agree that hints are a little too
nice and simple, and generally get people into trouble because they're
hard-coded in an app, tend to cause issues later, and are then
difficult to track down. Oracle solved that years ago as well, which
is why they support more advanced plan stability features than just
hints.
However, given the number of large-scale OLTP sites I've been to, I
can tell you from experience that post-ANALYZE plan changes wreak
complete havoc on a system and in many cases, bring it to its knees.
In those cases, the proper query plan is well-known, and a hint (or
some other form of plan stability) is all that would be required to
prevent it from happening.
This is pretty off-topic for this thread, so I'll postpone the
discussion for 8.5.
--
Jonah H. Harris, Senior DBA
myYearbook.com
