ANALYZE

In addition to the ANALYZE command, SingleStore provides automatic statistics collection, which is referred to as autostats. Autostats may automatically collect column statistics and create and update histograms. Refer to Statistics and Sampling Concepts and Statistics and Sampling Tasks for information on autostats.

Warning

ANALYZE may invalidate plans and cause them to be re-optimized and recompiled the next time the plan is run. That is, ANALYZE triggers plan invalidation and re-optimization when statistics change and a new plan is estimated to be faster, as described in Plan Invalidation. This is true whether autostats are enabled or not.

Column Statistics Collection

The table below shows when column statistics are collected by the ANALYZE TABLE command.

Table Settings	Column statistics collected by `ANALYZE TABLE`
`AUTOSTATS_CARDINALITY_MODE = INCREMENTAL`	No
`AUTOSTATS_CARDINALITY_MODE = PERIODIC`	Yes
`AUTOSTATS_CARDINALITY_MODE = OFF`	Yes

If incremental autostats is enabled for a table (AUTOSTATS_CARDINALITY_MODE = INCREMENTAL):
- ANALYZE will not gather colum statistics and will return quickly.
- The autostats system updates the table statistics when the table is modified through a DML statement (e.g. INSERT, UPDATE, DELETE, LOAD DATA, and TRUNCATE). The statistics do not need to be re-gathered when ANALYZE is run.
Incremental autostats (AUTOSTATS_CARDINALITY_MODE = INCREMENTAL) is not supported for rowstore tables.
Collecting statistics with ANALYZE can be expensive for large tables when incremental autostats is not enabled. You can choose when to run ANALYZE to best manage the performance impact.
The ANALZYE command overwrites any previous statistics collected on the table.
Collecting column statistics is strongly recommended for optimal query performance.
The autostats settings for a table can be found by querying the OPTIMZER_STATISTICS information schema view.

Histogram Collection

The ANALYZE command recollects data for histograms for columns that already have histograms built for them, and creates histograms for columns specified with ENABLE in the ANALYZE statement. Histograms are rebuilt by ANALYZE regardless of the setting of AUTOSTATS_HISTOGRAM_MODE.

SingleStore recommends collecting histograms for columns that are commonly filtered on. Histograms improve query performance but require memory to maintain and time to build and maintain. Refer to histograms vs. sampling for additional information.

Syntax

The ANALYZE command has the following forms:

SQL

ANALYZE TABLE table_name;
ANALYZE TABLE table_name COLUMNS column_name [, ...] ENABLE;
ANALYZE TABLE table_name COLUMNS ALL ENABLE;
ANALYZE TABLE table_name COLUMNS column_name [, ...] DISABLE;
ANALYZE TABLE table_name COLUMNS ALL DISABLE;
ANALYZE TABLE table_name DROP;

All forms of ANALYZE are subject to the following restrictions:

ANALYZE can be run on a master or child aggregator node.
ANALYZE requires SELECT and ALTER or INSERT permissions on the target table.
table_name must be the name of a table in the chosen database.
column_name must refer to a column in the table table_name.

ANALYZE TABLE table_name

Collect column statistics on all columns in table_name if incremental autostats is not enabled. Increments autostats is enabled when AUTOSTATS_CARDINALITY_MODE is set to INCREMENTAL.

Collects histograms over all columns that have histograms enabled.

Invalidate all plans on this table which were compiled with statistics that are significantly different from the newly observed statistics.

ANALYZE TABLE table_name COLUMNS column_name [, ...] ENABLE

Enables histogram collection for the listed columns then collects statistics and invalidates plans as described in ANALYZE TABLE table_name.

Histograms will be collected for columns listed in this command and any columns on which histograms were previously enabled.

ANALYZE TABLE table_name COLUMNS ALL ENABLE

Enables histogram collection for all columns then collects statistics and invalidates plans as described in ANALYZE TABLE table_name.

If the table contains columns which do not support histograms, a warning will be raised for each such column.

Because this command creates a histogram on every column of the table and collects histogram information for each new histogram, please note the following:

On very wide tables, this can be expensive to collect, increasing the execution time of the ANALYZE command.
The histograms are stored in memory. Make sure to plan for the extra memory enabling those histograms will consume.
Histograms are not always useful to have. Query the MV_PROSPECTIVE_HISTOGRAMS information schema view to check if a column has been identified from your workload, where having a histogram on that column would be beneficial to your queries.
In some cases sampling stats are better than histograms, see Histograms vs. Sampling for more information.

Instead of enabling histograms on all columns, SingleStore recommends using the default behavior by setting: default_autostats_histogram_mode=CREATE.

When default_autostats_histogram_mode is set to CREATE, if a column is identified at least twice in the MV_PROSPECTIVE_HISTOGRAMS information schema view as potentially benefiting from having a histogram, a histogram for that column will be automatically created and updated at that time. Like any histograms you create, that histogram is updated when you run ANALYZE, and as part of the periodic autostats process.

ANALYZE TABLE table_name COLUMNS column_name [, ...] DISABLE

Delete histograms on specified columns if present and disable histograms on those columns.

Does not collect any statistics.

ANALYZE TABLE table_name COLUMNS ALL DISABLE

Delete histograms on all columns of the table if present and disable histograms on all columns. If the table contains columns that do not support histograms, a warning will be raised for each such column.