Subject | Re: [firebird-support] Question about PLANs |
---|---|

Author | Arno Brinkman |

Post date | 2003-12-06T23:56:37Z |

Hi Ivan,

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.

lot.

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

> My understanding is that selectivity used by optimizer is defined asRDB$STATISTICS contains (1 / (index-nodes - index-duplicates)). The cost

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

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,Which number of rows do you meant ?

> 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 statisticsData does not always become stable in every table, sometimes it can vary a

> only once, after data get stable.

lot.

> In Firebird 1 (and older) the optimizer was so bad that recalculatingCorrect it uses only the pages from the index, but still every index-page

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

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