Simon Riggs <simon@2ndquadrant.com> writes:
> Csaba raises a good point here. Many people say they want "hints" when
> what they actually require the plan to be both stable and predictable.
Plan stability is also an important feature, especially for OLTP systems which
have hard real-time requirements. OLTP systems typically don't care about
getting the "best" plan for a query, only a plan that is "good enough".
"Good enough" means it can keep up with the rate of incoming requests; it
doesn't matter whether it keeps up with 10% headroom or 20% headroom. But if
one incoming query even one in a thousand takes 1000% of the time available
then the entire system risks falling down.
But plan stability is something that should be integrated directly in the
server. Not something achieved by having the user hint every query to defeat
the optimizer.
What I'm working on for my purposes here is a perl script that takes all the
queries in the application (either gathered from the log or stored statically)
and runs ANALYZE on all of them. Then within a transaction it runs ANALYZE on
the database and re-ANALYZES every query again. If any plans change then it
mails them to the DBA and rolls back the transaction with the database
analysis. The DBA gets a chance to approve the new plans before they go into
effect.
That's more or less what I expect an integrated plan stability feature to do.
It's like a shared query plan cache except that instead of being a cache it's
a database of plans that are specifically approved by the DBA. Queries that
don't have an approved plan could be configured to either produce a warning or
an error until the plan is approved.
--
greg