Re: generalizing the planner knobs

On Dec 5, 2005, at 4:17 AM, Tom Lane wrote:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> 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.
>
> Is it worth pointing out that using the same plan all the time is *no*
> recipe for guaranteeing response time? There is no such thing as a
> plan
> that is good for every case --- outlying data values can make a
> usually-good plan blow out your performance guarantee anyway.
> Disabling
> the planner is just a recipe for ensuring that that will happen, IMHO.
>
> regards, tom lane
>

I think I know what Greg is trying to say: I think in this plan
stability does not mean that the plan has to be completely fixed -
usually it is all about indexing. People start with an empty
perfectly analyzed database and data is added. However, some day some
cron job doing ANALYZE or whatever fails and the system will slow
down or even break down because data is added to some table which is
still seq-scanned. This is what usually happens and which leads to
support cases.

Adding hints to some comments or to the statement itself is not a
good solution as well. This is why I proposed a table or some flag
telling the planner what to favour (= always use a certain index). So
the basic idea is not to turn index of in general but to have the
chance to do it on a per index basis. I guess this would not be to
complex to implement and it solves 90% of all problems without having
to hide some information inside comments (which is no good at all).

best regards,

hans