CREATE STATISTICS to to use.
Once you and load data into it (e.g., , ), table statistics can be generated. Table statistics help the cost-based optimizer determine the cardinality of the rows used in each query, which helps to predict more accurate costs.
For compatibility with PostgreSQL, CockroachDB supports the ANALYZE/ANALYSE statement as an alias for CREATE STATISTICS. For syntax, see Aliases.
By default, CockroachDB on all indexed columns and up to 100 non-indexed columns, and automatically collects multi-column statistics on columns that prefix each index. As a result, most users do not need to directly issue CREATE STATISTICS statements.
Syntax
Parameters
Required privileges
The user must have the or theSELECT privilege on the target table. implicitly have this privilege.
On a table with enabled, SELECT alone is not sufficient if the RLS policies apply to the user; the user must have MAINTAIN. users, roles with , and the table owner are exempt, unless the table has set. is unaffected. For details, refer to .
Aliases
For , CockroachDB supportsANALYZE and ANALYSE as aliases for CREATE STATISTICS.
Alias syntax
Alias parameters
Examples
Setup
To follow along, run to start a temporary, in-memory cluster with the sample dataset preloaded:Create statistics on a single column
revenue_stats statistics a duplicate of the statistics automatically collected on the revenue column.
Create statistics on multiple columns
city and revenue are not an index prefix, making the city_revenue_stats statistics unique for the table.
Create statistics on a default set of columns
TheCREATE STATISTICS statement shown below automatically figures out which columns to get statistics on.
Create statistics as of a given time
To create statistics as of a given time (in this example, 1 minute ago to avoid interfering with the production workload), run a statement like the following:AS OF SYSTEM TIME clause works, including supported time formats, see .
Create partial statistics using extremes
CockroachDB supports , which collect statistics on a subset of table data to provide more up-to-date information without scanning the entire table. To create that collect statistics on the highest and lowest index values:rides table by scanning only the highest and lowest index values, rather than performing a full table scan.
You can also create extremes statistics on specific columns, provided there is an index with the specified column as the first key column:
Create partial statistics on specific data
To create on a specific column and values matching specific conditions, ensure the column is indexed:Delete statistics
To delete statistics for all tables in all databases:DELETE statement, see .
View statistics jobs
Every time theCREATE STATISTICS statement is executed, it starts a background job. This is true for queries issued by your application as well as queries issued for .
To view statistics jobs, there are two options:
-
Use to see all statistics jobs that were created by user queries (i.e., someone entering
CREATE STATISTICSat the SQL prompt or via application code): -
Use
SHOW AUTOMATIC JOBSto see statistics jobs that were created by :
Known limitations
TheANALYZE alias of does not support specifying an timestamp. ANALYZE statements use AS OF SYSTEM TIME '-0.001ms' automatically. For more control over the statistics interval, use the CREATE STATISTICS syntax instead.

