CREATE STATISTICS (Experimental)

On this page Carat arrow pointing down
Warning:
As of April 30, 2020, CockroachDB v2.1 is no longer supported. For more details, refer to the Release Support Policy.

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

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:

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

For more information, see DELETE.

See Also


Yes No
On this page

Yes No