The SHOW STATISTICS
statement lists table statistics used by the cost-based optimizer.
By default, CockroachDB automatically generates statistics on all indexed columns, and up to 100 non-indexed columns.
Synopsis
Required Privileges
No privileges are required to list table statistics.
Parameters
Parameter | Description |
---|---|
table_name |
The name of the table you want to view statistics for. |
Examples
Setup
The following examples use MovR, a fictional vehicle-sharing application, to demonstrate CockroachDB SQL statements. For more information about the MovR example application and dataset, see MovR: A Global Vehicle-sharing App.
To follow along, run cockroach demo
to start a temporary, in-memory cluster with the movr
dataset preloaded:
$ cockroach demo
List table statistics
> SHOW STATISTICS FOR TABLE rides;
Delete statistics
To delete statistics for all tables in all databases:
> DELETE FROM system.table_statistics WHERE true;
To delete a named set of statistics (e.g, one named "users_stats"), run a query like the following:
> DELETE FROM system.table_statistics WHERE name = 'users_stats';
After deleting statistics, restart the nodes in your cluster to clear the statistics caches.
For more information about the DELETE
statement, see DELETE
.