Manually collect statistics
Statistics capture metadata about your tables and columns — row counts, value distributions, and data density. The query optimizer uses this information to generate efficient execution plans. Stale or missing statistics cause the optimizer to make poor estimates, leading to slow queries and suboptimal plans.
Use the ANALYZE statement to collect statistics manually in the following situations.
When to run ANALYZE manually
Run ANALYZE manually in these situations:
More than 20% of rows in a table have changed through
INSERT,UPDATE, orDELETEoperations, or a new index has been created on the table.An extract, transform, load (ETL) job has executed many
INSERT,UPDATE, orDELETEstatements in sequence.Data has been written to a new table using functions or stored procedures.
An execution plan shows an estimated row count of 1 and contains multiple operators such as Broadcast, Sort+GroupByAgg, or NestLoop — this signals that the optimizer lacks accurate statistics.
Syntax
ANALYZE [VERBOSE] [ROOTPARTITION ALL | [table [(column [, ...])]]]Running ANALYZE without parameters collects statistics on all tables in the database. This may take a long time for large databases.
Parameters
| Parameter | Description | When to use |
|---|---|---|
VERBOSE | Prints execution details, including the number of scanned rows and visible rows. | Use when diagnosing slow or unexpected ANALYZE behavior. |
ROOTPARTITION ALL | Collects statistics on the parent tables of all partitioned tables in the database. | Use when you want to quickly refresh partition-level statistics across all partitioned tables. |
ROOTPARTITION table | Collects statistics on the parent table of a specific partitioned table. | Use when you have changed partition definitions or data distribution for one specific partitioned table. |
(no ROOTPARTITION) | Collects statistics on both parent tables and all subpartitioned tables. | Default behavior — use for a full statistics refresh on partitioned tables. |
table[(column1, column2, ...)] | Collects statistics on the specified columns of a table. Without column names, all columns are analyzed. | Use when you need a faster targeted refresh — specify only the columns used in JOIN, WHERE, ORDER BY, GROUP BY, or HAVING clauses. |
Examples
Collect statistics on two specific columns of the orders table:
ANALYZE orders(o_orderdate, o_orderpriority);Run a full-table analysis with verbose output:
ANALYZE VERBOSE orders;Usage notes
To minimize ANALYZE run time on large tables, target the columns referenced in query predicates and join conditions rather than analyzing all columns. Specify the table and column names explicitly:
ANALYZE t1(a, b).If
ANALYZEis run without parameters on a large database, schedule it during off-peak hours to reduce impact on running queries.After loading data into a new table, always run
ANALYZEbefore executing queries against it.