# ANALYZE The `ANALYZE` command collects statistics for a table. ```sql ANALYZE TABLE table_name [columns [column_name_list | all] [enable | disable]] ``` The `ANALYZE` command manually gathers statistics for a table. The `ANALYZE` command may: * Collect column statistics: * Row count, * Column cardinality (number of unique values in a column), * Null count (number of null values in a column). * Update and create histograms. The type of statistics collected and histograms built by the `ANALYZE` command depends on autostats settings and the `ANALYZE` command itself. 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts.md) and [Statistics and Sampling Tasks](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-tasks.md) 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts/#section-idm4578590413484832788452287954.md). 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 column 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](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/optimizer-statistics.md) 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts/#section-idm4556660363819232788448162409.md) 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` 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 Collects column statistics on all columns in `table_name` if incremental autostats is not enabled. Incremental autostats is enabled when `AUTOSTATS_CARDINALITY_MODE` is set to `INCREMENTAL`. Collects histograms over all columns that have histograms enabled. Invalidates 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](https://docs.singlestore.com/db/v9.1/reference/information-schema-reference/query-performance-workload-management-and-statistics/mv-prospective-histograms.md) 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts/#section-idm4556660363819232788448162409.md) 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](https://docs.singlestore.com/db/v9.1/query-data/query-tuning/statistics-and-sampling/statistics-and-sampling-concepts.md) 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](https://docs.singlestore.com/db/v9.1/reference/configuration-reference/engine-variables/list-of-engine-variables/#sync-variables-lists.md) `enable_json_statistics` needs to be set to true. To build a histogram on a JSON key: ```sql ANALYZE table COLUMN enable; ANALYZE table COLUMNS enable; ``` To drop histograms for a JSON key: ``` ANALYZE table COLUMN disable; ANALYZE table COLUMNS disable; ``` The format of \ 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; ``` *** Modified at: June 11, 2026 Source: [/db/v9.1/reference/sql-reference/operational-commands/analyze/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/operational-commands/analyze/) (An index of the documentation is available at /llms.txt)