ANALYZE
Statistics for a table can be collected and updated on an ad hoc basis by running the ANALYZE
command:
ANALYZE TABLE table_name;
If autostats are enabled for a table, ANALYZE
for that table will not gather statistics and will return almost instantly.
ANALYZE
also triggers plan invalidation and re-optimization when statistics change, as described in Plan Invalidation.
If autostats are disabled for a table, ANALYZE
collects column statistics on every column, as well as histograms on chosen columns.ANALYZE TABLE table_
commands, as described below.
Collecting column statistics is strongly recommended for optimal query performance.
Statistics are not updated when the table is modified through DML statements, including INSERT
, UPDATE
, DELETE
, LOAD DATA
, and TRUNCATE
.ANALYZE
command is called, which completely overwrites any previous statistics collected on the table.
Warning
-
ANALYZE
may invalidate plans, i.e. cause them to be reoptimized and recompiled the next time they are run when data statistics change and a new plan could be faster. See the Plan invalidation section for more details. -
Collecting statistics with
ANALYZE
can be expensive for large tables, especially when collecting histograms.When autostats are disabled, because statistics are collected only when you run ANALYZE
, you can choose when to run it to best manage the performance impact.
Notes
All forms of ANALYZE
are subject to the following restrictions:
-
ANALYZE
can be run on a master or child aggregator node. -
ANALYZE
requiresSELECT
andALTER
orINSERT
permissions on the target table. -
table_
must be the name of a table in the chosen database.name -
column_
must refer to a column in the tablename table_
.name
The ANALYZE
command has the following forms:
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;
These are described in detail below.
ANALYZE TABLE table_name;
Collect column statistics on all columns in table_
if it is not autostats-enabled, and collect histograms over all previously designated columns.
ANALYZE TABLE table_name COLUMNS column_name [, ...] ENABLE;
Designate the specified columns to have histograms collected, in addition to any previously designated columns.ANALYZE TABLE table_
.
ANALYZE TABLE table_name COLUMNS ALL ENABLE;
Designate all columns to have histograms collected, in addition to any previously designated columns.ANALYZE TABLE table_
.
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_
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.schema. mv_ prospective_ histograms -
In some cases sampling stats are better than histograms, see Histograms vs.
Sampling for more information.
Instead, on workspaces on v7.default_
With default_
set to CREATE
, if a column is identified at least twice in information_
that having a histogram on it could be beneficial, then the histogram will be automatically created and updated at that time.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.
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.
ANALYZE TABLE table_name DROP;
Deletes all stored statistics for a table, and disables histograms on any columns for which they have been enabled.
Last modified: October 31, 2023