SHOW STATISTICS

On this page Carat arrow pointing down

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.

CockroachDB also automatically collects multi-column statistics on the columns that prefix each index.

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 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:

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