# SHOW STATISTICS

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

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

    > 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';

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

- Cost-Based Optimizer
- `CREATE STATISTICS`
- `CREATE TABLE`
- `INSERT`
- `IMPORT`
- SQL Statements