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, and 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 to view statistics for. |
Output
| Column | Description |
|---|---|
statistics_name |
The name of the statistics. If __auto__, the statistics were created automatically. |
column_names |
The name of the columns on which the statistics were created. |
created |
The timestamp when the statistics were created. |
row_count |
The number of rows for which the statistics were computed. |
distinct_count |
The number of distinct values for which the statistics were computed. |
null_count |
The number of null values for which the statistics were computed. |
avg_size |
New in v22.1: The average size in bytes of the values of the columns for which the statistics were computed. |
histogram_id |
The ID of the histogram used to compute statistics. |
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 | avg_size | histogram_id
------------------+---------------------------+----------------------------+-----------+----------------+------------+----------+---------------------
__auto__ | {city} | 2022-04-20 22:43:08.851613 | 500 | 9 | 0 | 12 | 755053982033936385
__auto__ | {id} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 26 | 755053982039703553
__auto__ | {city,id} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 37 | NULL
__auto__ | {rider_id} | 2022-04-20 22:43:08.851613 | 500 | 50 | 0 | 17 | 755053982050910209
__auto__ | {city,rider_id} | 2022-04-20 22:43:08.851613 | 500 | 50 | 0 | 29 | NULL
__auto__ | {vehicle_city} | 2022-04-20 22:43:08.851613 | 500 | 9 | 0 | 11 | 755053982061690881
__auto__ | {vehicle_id} | 2022-04-20 22:43:08.851613 | 500 | 15 | 0 | 17 | 755053982067392513
__auto__ | {vehicle_city,vehicle_id} | 2022-04-20 22:43:08.851613 | 500 | 15 | 0 | 28 | NULL
__auto__ | {start_address} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 25 | 755053982080991233
__auto__ | {end_address} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 25 | 755053982087544833
__auto__ | {start_time} | 2022-04-20 22:43:08.851613 | 500 | 30 | 0 | 7 | 755053982093443073
__auto__ | {end_time} | 2022-04-20 22:43:08.851613 | 500 | 367 | 0 | 7 | 755053982099472385
__auto__ | {revenue} | 2022-04-20 22:43:08.851613 | 500 | 100 | 0 | 6 | 755053982105337857
(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.
