SHOW STATISTICS

On this page Carat arrow pointing down
Warning:
CockroachDB v20.1 is no longer supported as of November 12, 2021. For more details, refer to 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


Yes No
On this page

Yes No