Hi Ivan,

> 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$STATISTICS contains (1 / (index-nodes - index-duplicates)). The cost
fetching data for a relation is also calculated with the estimate calculated
cardinality (data_pages * page_size / record-size). These are currently the
most importand numbers for the cost-calculation which the optimizer uses to
determine the process-order.

> 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" ?)

Which number of rows do you meant ?

> I think that in most databases it is sufficient to recalculate statistics
> only once, after data get stable.

Data does not always become stable in every table, sometimes it can vary a
lot.

> 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.

Correct it uses only the pages from the index, but still every index-page
needs to be visit. You can see the number of leaf-pages with gstat and this
number of pages needs to be visit for calculation.

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird links :
http://www.firebirdsql.com
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/


Nederlandse firebird nieuwsgroep :
news://80.126.130.81