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.
CockroachDB also automatically collects multi-column statistics on the columns that prefix each index.
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 16:55:24.725089+00:00 | 500 | 9 | 0 | 584550071425531905
__auto__ | {id} | 2020-08-26 16:55:24.725089+00:00 | 500 | 500 | 0 | 584550071432740865
__auto__ | {city,id} | 2020-08-26 16:55:24.725089+00:00 | 500 | 500 | 0 | NULL
__auto__ | {rider_id} | 2020-08-26 16:55:24.725089+00:00 | 500 | 50 | 0 | 584550071446732801
__auto__ | {city,rider_id} | 2020-08-26 16:55:24.725089+00:00 | 500 | 50 | 0 | NULL
__auto__ | {vehicle_city} | 2020-08-26 16:55:24.725089+00:00 | 500 | 9 | 0 | 584550071461019649
__auto__ | {vehicle_id} | 2020-08-26 16:55:24.725089+00:00 | 500 | 15 | 0 | 584550071467966465
__auto__ | {vehicle_city,vehicle_id} | 2020-08-26 16:55:24.725089+00:00 | 500 | 15 | 0 | NULL
__auto__ | {start_address} | 2020-08-26 16:55:24.725089+00:00 | 500 | 500 | 0 | 584550071482122241
__auto__ | {end_address} | 2020-08-26 16:55:24.725089+00:00 | 500 | 500 | 0 | 584550071489167361
__auto__ | {start_time} | 2020-08-26 16:55:24.725089+00:00 | 500 | 30 | 0 | 584550071496671233
__auto__ | {end_time} | 2020-08-26 16:55:24.725089+00:00 | 500 | 367 | 0 | 584550071504437249
__auto__ | {revenue} | 2020-08-26 16:55:24.725089+00:00 | 500 | 100 | 0 | 584550071512137729
(13 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';
For more information about the DELETE statement, see DELETE.
