My understanding is that selectivity used by optimizer is defined as
the ratio of the number of distinct values to the number of rows.
But this is not the value stored in rdb$indices table !

rdb$indices.statistic contains just 1 / number_of_distinct_values,
whereas number_of_rows is just estimated based on record size
and number of data pages allocated.

rdb$indices.statistic is updated when the index is created,
or when it is activated, or by Set Statistics command.
I am not sure when number of rows is "calculated",
probably when internal lock is obtained on that index ?
(Because number_of_rows part of selectivity is relatively fresh,
can this be considered as "automatic selectivity recalculation" ?)

I think that in most databases it is sufficient to recalculate statistics
only once, after data get stable.
In Firebird 1 (and older) the optimizer was so bad that recalculating
statistics was just wasting time since it usually had no effect on plan.
It is also worth mentioning that Set Statistics command is pretty
fast operation, since it looks only at index, not data pages.


Ivan

----- Original Message -----
From: "Arno Brinkman" <firebird@...>
Sent: Saturday, December 06, 2003 6:14 PM
Subject: Re: [firebird-support] Question about PLANs


> > > The optimizer recalculates plan on every statement thus if content has
> > > changed then the PLAN can change too. Note! That selectivity what optimizer
> > > uses to calculate cost is not updated after every insert/delete. That means
> > > it could be that selectivity value is not accurate. Although the change
> > > between 5 and 15 records is not big ;-). You can force recalculate of index
> > > with SET STATISTICS statement if needed.
> >
> > I need some confirmation on this: If the 'index selectivity' is not
> > recalculated after insert/delete, does this mean that we have to
> > manually issue 'set statistics' every now and then? Or is there any
> > automatic recalcuation behind the screen?
>
> There is an automatic recalculation, but don't ask me which values/changes
> exactly triggers this recalculation. If you do a huge insert/delete then you
> could eventually run SET STATISTICS to update statistics values directly.
>
> Regards,
> Arno Brinkman
> ABVisie