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;
ANALYZE TABLE table_name CORRELATE COLUMN correlated_column_name WITH COLUMN column_name [USING COEFFICIENT coefficient_value];

All forms of ANALYZE are subject to the following restrictions:

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.

Does not collect any statistics.

ANALYZE TABLE table_name DROP

Deletes all stored statistics for a table and disables histograms on any columns for which they have been enabled. Invalidates all plans on the table which were compiled using statistics. Does not disable automatic statistics collection (autostats).

ANALYZE TABLE table_name CORRELATE COLUMN ... WITH COLUMN ... [USING COEFFICIENT coefficient_value]

Stores correlations between two columns in a table along with the correlation coefficient.

An example is the relationship between car makes and models: a car model is associated with a specific car maker. In this case, the model is the correlated column:

SQL

ANALYZE TABLE table_name CORRELATE COLUMN model WITH COLUMN make USING COEFFICIENT 1.0;

The optimizer defaults correlation coefficients to 0.5. The coefficient values can range from 0 (no correlation) to 1 (highly correlated).

For more information, see the Statistics and Sampling Concepts page.

JSON Statistics

Statistics for a table containing JSON keys can be collected and updated by using the ANALYZE command.

Note

To use JSON statistics the global engine variable enable_json_statistics needs to be set to true.

To build a histogram on a JSON key:

SQL

ANALYZE table <table_name> COLUMN <column+key> enable;

ANALYZE table <table_name> COLUMNS <list of (column+key)> enable;

To drop histograms for a JSON key:

ANALYZE table <table_name> COLUMN <column+key> disable;

ANALYZE table <table_name> COLUMNS <list of (column+key)> disable;

The format of <column+key> is similar to what is allowed in WHERE clauses and projections. For example:

SQL

SET GLOBAL enable_json_statistics = 1;
CREATE TABLE analyze_test(col json);
INSERT analyze_test VALUES('{"path" : { "path" : "hello" }}');

SQL

SELECT * 
FROM analyze_test 
WHERE col::`path`::$`path` = "hello";

To build a histogram on col::'path'::'path' from the previous example:

SQL

ANALYZE TABLE analyze_test COLUMN col::`path`::`path` ENABLE;