Library

How to estimate number of records in the table using statistics value of the unique index

Firebird has a special parameter for every index, called "index statistics".
For unique indices, the value of the statistics is calculated as 1 divided by the number of unique keys in an index, e.g. for 100 records the statistics will be 1/100 = 0.01.
Internally, Firebird uses this value to estimate the quality of the specific index (i.e., to use or not to use it in the plan for the query).

This value is kept in the column RDB$STATISTICS in the system table RDB$INDICES, so, if you know the name of the index. you can retrieve it.

If this index is unique (in this case it is the primary key), we can use statistics value to get the approximate number of records

SQL> select rdb$statistics from rdb$indices where rdb$index_name ='PK_DOC_LIST';

         RDB$STATISTICS
=======================
  1.062473558022248e-07

SQL> select 1.0/rdb$statistics from rdb$indices where rdb$index_name ='PK_DOC_LIST';

                 DIVIDE
=======================
      9411998.938227322

SQL> select count(*) from DOC_LIST;

                COUNT
=====================
              9362939

As you can see, the number of records differs from the value calculated with statistics, due to rounding.
However, we can use statistics to quickly estimate the number of records in the tables and identify the biggest, for example:

SQL> select rdb$index_name, 1/rdb$statistics from rdb$indices where rdb$index_name like 'PK%' and rdb$statistics >0  order by 2 desc;

RDB$INDEX_NAME                                   DIVIDE
=============================== =======================
PK_DOC_LIST                           9411998.938227322
PK_PERF_ESTIMATED                     26190.00023190827
PK_PERF_AGG                           2514.000026047463
PK_WARES                              400.0000089406969
PK_INVNT_SALDO                        400.0000089406969
PK_AGENTS                             50.00000111758712
PK_MONEY_SALDO                        49.00000100396576
PK_OPTYPES                            8.999999932944775
PK_WARE_GROUPS                        8.000000000000000
PK_SEMAPHORES                         4.999999925494195
PK_DOC_STATES                         4.000000000000000