SHOW STATISTICS

Warning:
This version of CockroachDB is no longer supported. For more details, see the Release Support Policy.

The SHOW STATISTICS statement lists table statistics used by the cost-based optimizer.

Note:

By default, CockroachDB automatically generates statistics on all indexed columns, and up to 100 non-indexed columns.

Synopsis

SHOW STATISTICS FOR TABLE table_name

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:

icon/buttons/copy
$ cockroach demo

List table statistics

icon/buttons/copy
> 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:

icon/buttons/copy
> 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:

icon/buttons/copy
> 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.

See Also

YesYes NoNo