Hi Helen,

Thanks for your help. See my comments below.

----- Original Message -----
From: "Jerome Bouvattier" <jbouvattier@...>

>
> ----- Original Message -----
> From: "Helen Borrie" <helebor@...>
> To: <firebird-support@yahoogroups.com>
> Sent: Friday, September 03, 2004 2:56 PM
> Subject: Re: [firebird-support] Re: Optimizer randomly chooses plans (WAS
> Big table and DB slowdowns)
>
>
[..]
> >
> >There is still something I don't quite understand. How come the optimizer
> >can hesitate that way ? I find it scary because it makes things
> >unpredictable. My app works ok for hours, then crawl, then come back to
> >normal, etc... This without much change to data itself. The pb once
occured
> >right after a restore.
> >
> >Any ideas ?
>
> The optimizer makes cost-based decisions when it chooses a plan. In
> considering indexes on the basis of selectivity, it uses the index
> statistics as calculated when the first user logged in.

I first thought changing statistics would be the cause too. But I don't
think it is the case here. For the following reasons:

- My connections are pooled and the pool has a relatively high timeout. This
means that a pool of 0 connections is scarce (happens at night), thus index
selectivities updates are too. I'm 100% sure that the optimizer changes its
mind when a few connections are still open.
- As I said, the pb can pop up on a freshly restored db. The mentionned
query would execute fast for a few minutes and then suddenly the slow plan
is used, whereas a few dozen rows have been inserted/updated. (out of 80
million rows, I don't think this can change my index selectivities a lot).
An hour later the good plan could be the preferred one again. and on .. and
on...

So, given the above, shouldn't the cause be elsewhere ?

> You have that
> problem index there (low selectivity, and it will get worse as you add
more
> rows that mostly share a single value).

As I said, I removed the 2 poorly selective indices. This leaves me with :

PK_PRESENCE 1.887221401375427e-008
PRESENCE_IDX3 7.386178822343936e-006
PRESENCE_IDX4 1.887221401375427e-008
PRESENCE_IDX5 1.601524388661346e-007
RELATION_262 0.0001687478943495080
RELATION_296 2.158489223802462e-006

Now the optimizer hesitates between PRESENCE_IDX3 which is definetely the
one to choose and a NATURAL scan !

> Set's strategy, for making it
> ignore that index, will help avoid this slow-down effect from the
optimizer
> mistakenly choosing that index, based on old statistics.

And can I be sure, using this technique, that the optimizer has enough hints
to always pick the right PLAN ?


Thank you very much in advance.

--
Jerome