CREATE STATISTICS (Experimental)

2020-02-16

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.

{{site.data.alerts.callout_danger}} This is an experimental feature. The interface and output are subject to change. {{site.data.alerts.end}}

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

{% include {{ page.version.version }}/sql/diagrams/create_stats.html %}

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

> CREATE STATISTICS students ON id FROM students_by_list;
CREATE STATISTICS

{{site.data.alerts.callout_info}} Multi-column statistics are not supported yet. {{site.data.alerts.end}}

Delete all statistics

To delete statistics for all tables in all databases:

> DELETE FROM system.table_statistics WHERE true;
DELETE 1

For more information, see DELETE.

See Also