Index Statistics (Stats v2)

Statistics can be generated for all indexed columns. Statistics generated for indexed columns are known as Index Statistics. Index statistics gather the following information:

  • Table cardinality

  • Index prefix scalar statistics:

    • Number of distinct values

    • Null count

  • Histograms, which provide data about the distribution of values in a column or across multiple columns in an index.

How to generate Index Statistics

Index Statistics are generated in one of two ways:

  1. User runs the ANALYZE command

  2. The system incrementally updates the Index Statistics after a change occurs to the index

It is possible to disable automatic Index Statistics generation using the stats-v2-collection database option. When disabled, Index Statistics will only be generated using the ANALYZE command.

  • Once a table grows above a specified cardinality threshold, Index Statistics will no longer be automatically regenerated. For more information, see Running ANALYZE to Obtain Index Statistics.

  • The ANALYZE command calculates index statistics based on all available record versions, including older record versions that represent deleted or updated rows. Unreferenced older record versions are removed only during a full garbage collection cycle. If your workload significantly changes the data profile (for example truncating a table), the results of the ANALYZE command will not immediately reflect such changes in data profile. If it is necessary to have the results of the ANALYZE command reflect changes to the data profile sooner, use max-full-gc-skips to control the frequency of full garbage collections. For more information on max-full-gc-skips, see Database Options.

How to enable Index Statistics

Table Statistics (Stats v3) are enabled by default.

To revert back to Index Statistics (Stats v2):

Please contact NuoDB Support before proceeding.

  1. Set the stats-version database option to v2 on all NuoDB engines.

  2. Set the stats-v2-collection database option to enable on all NuoDB engines.

  3. For all schemas in the database, execute analyze all tables in schema <schema>, including the SYSTEM schema.

How to configure Index Statistics

No additional configuration is necessary.