ANALYZE

ANALYZE TABLE table_name

Collect column statistics on all columns in table_name if it is not autostats-enabled, and collect histograms over all previously designated columns. Invalidate all plans on this table which were compiled with significantly different statistics than the newly observed statistics.

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

Designate the specified columns to have histograms collected, in addition to any previously designated columns. Then collects statistics and invalidates plans the same way as ANALYZE TABLE table_name.

ANALYZE TABLE table_name COLUMNS ALL ENABLE

Designate all columns to have histograms collected, in addition to any previously designated columns. If the table contains columns which do not support histograms, a warning will be raised for each such column. Collects statistics and invalidates plans the same way as ANALYZE TABLE table_name.

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 information_schema.mv_prospective_histograms table 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, on workspaces on v7.0+ SingleStore recommends using the default behavior: default_autostats_histogram_mode=CREATE

With default_autostats_histogram_mode set to CREATE, if a column is identified at least twice in information_schema.mv_prospective_histograms that having a histogram on it could be beneficial, then the histogram will be automatically created and updated at that time. Like any histograms you create, it is updated when you run ANALYZE, and also as part of the periodic autostats (same interval: 10 minutes, and actually collected as part of the same process).

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

Delete histograms on specified columns if present, and designate the specified columns to not have histograms collected in the future. Does not collect any statistics.

ANALYZE TABLE table_name COLUMNS ALL DISABLE

Delete histograms on all columns of the table if present, and designate all columns to not have histograms collected in the future. 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 delete automatic statistics.

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

Stores correlations between two columns in a table along with their coefficient values. The correlated_column_name is the column that is closely related to another column in a table.

An example is the relationship between car makers and models. A car model is associated with a specific car maker. So in this case, the model is the correlated column:

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

The optimizer defaults correlation coefficients at 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.

ANALYZE MEMORY

ANALYZE MEMORY starts a query internally, collecting memory cost details from past runs for each query across leaf nodes, and retrieves execution time data from the aggregator node. An estimated peak memory usage is calculated for each query. Then, the estimated peak memory usage is populated to the corresponding query entry in the plancache.

ANALYZE MEMORY;

This memory information can also help with workload management tasks such as categorizing query types and managing available memory in the cluster.

JSON Statistics

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

To build a histogram on a JSON key:

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:

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

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

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

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