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;
statistics_name | column_names | created | row_count | distinct_count | null_count | histogram_id
------------------+-----------------+----------------------------------+-----------+----------------+------------+---------------------
__auto__ | {city} | 2020-08-26 17:17:13.852138+00:00 | 500 | 9 | 0 | 584554361172525057
__auto__ | {vehicle_city} | 2020-08-26 17:17:13.852138+00:00 | 500 | 9 | 0 | 584554361179242497
__auto__ | {id} | 2020-08-26 17:17:13.852138+00:00 | 500 | 500 | 0 | NULL
__auto__ | {rider_id} | 2020-08-26 17:17:13.852138+00:00 | 500 | 50 | 0 | NULL
__auto__ | {vehicle_id} | 2020-08-26 17:17:13.852138+00:00 | 500 | 15 | 0 | NULL
__auto__ | {start_address} | 2020-08-26 17:17:13.852138+00:00 | 500 | 500 | 0 | NULL
__auto__ | {end_address} | 2020-08-26 17:17:13.852138+00:00 | 500 | 500 | 0 | NULL
__auto__ | {start_time} | 2020-08-26 17:17:13.852138+00:00 | 500 | 30 | 0 | NULL
__auto__ | {end_time} | 2020-08-26 17:17:13.852138+00:00 | 500 | 367 | 0 | NULL
__auto__ | {revenue} | 2020-08-26 17:17:13.852138+00:00 | 500 | 100 | 0 | NULL
(10 rows)
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.
