Note:
Cockroach Labs supports the current stable release and two releases prior. Therefore, this version will no longer be supported after the Spring 2020 release.

New in v2.1: Use the CREATE STATISTICS statement to generate table statistics for the cost-based optimizer to use.

Once you create a table and load data into it (e.g., INSERT, IMPORT), 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.

Warning:

This is an experimental feature. The interface and output are subject to change.

Considerations

Each time CREATE STATISTICS is used, a new statistic is created without removing any old statistics. To delete statistics for all tables in all databases, use DELETE.

Synopsis

CREATE STATISTICS statistics_name ON column_name FROM table_name

Required Privileges

The user must have the CREATE privilege on the parent database.

Parameters

Parameter Description
statistics_name The name of the statistic you are creating.
column_name The name of the column you want to create the statistic for.
table_name The name of the table you want to create the statistic for.

Examples

Create statistics

copy
icon/buttons/copy
> CREATE STATISTICS students ON id FROM students_by_list;
CREATE STATISTICS
Note:

Multi-column statistics are not supported yet.

Delete all statistics

To delete statistics for all tables in all databases:

copy
icon/buttons/copy
> DELETE FROM system.table_statistics WHERE true;
DELETE 1

For more information, see DELETE.

See Also



Yes No