> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# SHOW STATISTICS
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
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
To list table statistics, the user must have any privilege on the table being inspected.
## Parameters
| Parameter | Description |
| ------------------ | ------------------------------------------------------------------------- |
| `table_name` | The name of the table to view statistics for. |
| `opt_with_options` | Control the behavior of `SHOW STATISTICS` with [these options](#options). |
### Options
| Option | Value | Description |
| ---------- | ----- | --------------------------------------------------------------------------------------------------------- |
| `FORECAST` | N/A | [Display forecasted statistics](#display-forecasted-statistics) along with the existing table statistics. |
## Output
| Column | Description |
| ----------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `statistics_name` | The name of the statistics. If `__auto__`, the statistics were created automatically. If `__forecast__`, the statistics are [forecasted](#display-forecasted-statistics). |
| `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` | 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:
```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
$ cockroach demo
```
### List table statistics
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW STATISTICS FOR TABLE rides;
```
```
statistics_name | column_names | created | row_count | distinct_count | null_count | avg_size | histogram_id
------------------+---------------------------+----------------------------+-----------+----------------+------------+----------+---------------------
__auto__ | {id} | 2022-09-22 16:45:29.957103 | 500 | 500 | 0 | 26 | 798866571146067969
__auto__ | {id,city} | 2022-09-22 16:45:29.957103 | 500 | 500 | 0 | 37 | NULL
__auto__ | {city} | 2022-09-22 16:45:29.957103 | 500 | 9 | 0 | 12 | 798866571134304257
__auto__ | {city,rider_id} | 2022-09-22 16:45:29.957103 | 500 | 50 | 0 | 29 | NULL
__auto__ | {vehicle_city} | 2022-09-22 16:45:29.957103 | 500 | 9 | 0 | 11 | 798866571197153281
__auto__ | {vehicle_city,vehicle_id} | 2022-09-22 16:45:29.957103 | 500 | 15 | 0 | 28 | NULL
__auto__ | {rider_id} | 2022-09-22 16:45:29.957103 | 500 | 50 | 0 | 17 | 798866571173724161
__auto__ | {vehicle_id} | 2022-09-22 16:45:29.957103 | 500 | 15 | 0 | 17 | 798866571208720385
__auto__ | {start_address} | 2022-09-22 16:45:29.957103 | 500 | 500 | 0 | 25 | 798866571232575489
__auto__ | {end_address} | 2022-09-22 16:45:29.957103 | 500 | 500 | 0 | 25 | 798866571245191169
__auto__ | {start_time} | 2022-09-22 16:45:29.957103 | 500 | 30 | 0 | 7 | 798866571257315329
__auto__ | {end_time} | 2022-09-22 16:45:29.957103 | 500 | 367 | 0 | 7 | 798866571269537793
__auto__ | {revenue} | 2022-09-22 16:45:29.957103 | 500 | 100 | 0 | 6 | 798866571283103745
(13 rows)
```
### Display forecasted statistics
The `WITH FORECAST` option calculates and displays forecasted statistics along with the existing table statistics. The forecast is a simple regression model that predicts how the statistics have changed since they were last collected. Forecasts that closely match the historical statistics are used by the cost-based optimizer.
CockroachDB generates forecasted statistics when the following conditions are met:
* There have been at least 3 historical statistics collections.
* The historical statistics closely fit a linear pattern.
The following example shows 3 historical statistics collections and the subsequent forecast:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SHOW STATISTICS FOR TABLE rides WITH FORECAST;
```
```
statistics_name | column_names | created | row_count | distinct_count | null_count | avg_size | histogram_id
------------------+---------------------------+----------------------------+-----------+----------------+------------+----------+---------------------
__auto__ | {id} | 2022-09-22 18:57:19.254073 | 500 | 500 | 0 | 26 | 798892488327364609
__auto__ | {id,city} | 2022-09-22 18:57:19.254073 | 500 | 500 | 0 | 37 | NULL
__auto__ | {city} | 2022-09-22 18:57:19.254073 | 500 | 9 | 0 | 12 | 798892488315830273
__auto__ | {city,rider_id} | 2022-09-22 18:57:19.254073 | 500 | 50 | 0 | 29 | NULL
__auto__ | {vehicle_city} | 2022-09-22 18:57:19.254073 | 500 | 9 | 0 | 11 | 798892488400011265
__auto__ | {vehicle_city,vehicle_id} | 2022-09-22 18:57:19.254073 | 500 | 15 | 0 | 28 | NULL
__auto__ | {rider_id} | 2022-09-22 18:57:19.254073 | 500 | 50 | 0 | 17 | 798892488351875073
__auto__ | {vehicle_id} | 2022-09-22 18:57:19.254073 | 500 | 15 | 0 | 17 | 798892488412004353
__auto__ | {start_address} | 2022-09-22 18:57:19.254073 | 500 | 500 | 0 | 25 | 798892488436908033
__auto__ | {end_address} | 2022-09-22 18:57:19.254073 | 500 | 500 | 0 | 25 | 798892488447590401
__auto__ | {start_time} | 2022-09-22 18:57:19.254073 | 500 | 30 | 0 | 7 | 798892488458928129
__auto__ | {end_time} | 2022-09-22 18:57:19.254073 | 500 | 367 | 0 | 7 | 798892488472920065
__auto__ | {revenue} | 2022-09-22 18:57:19.254073 | 500 | 100 | 0 | 6 | 798892488485011457
__auto__ | {id} | 2022-09-22 19:35:13.274435 | 500 | 500 | 0 | 26 | 798899939842326529
__auto__ | {id,city} | 2022-09-22 19:35:13.274435 | 500 | 500 | 0 | 37 | NULL
__auto__ | {city} | 2022-09-22 19:35:13.274435 | 500 | 9 | 0 | 12 | 798899939828039681
__auto__ | {city,rider_id} | 2022-09-22 19:35:13.274435 | 500 | 50 | 0 | 29 | NULL
__auto__ | {vehicle_city} | 2022-09-22 19:35:13.274435 | 500 | 9 | 0 | 11 | 798899939903963137
__auto__ | {vehicle_city,vehicle_id} | 2022-09-22 19:35:13.274435 | 500 | 15 | 0 | 28 | NULL
__auto__ | {rider_id} | 2022-09-22 19:35:13.274435 | 500 | 50 | 0 | 17 | 798899939874242561
__auto__ | {vehicle_id} | 2022-09-22 19:35:13.274435 | 500 | 15 | 0 | 17 | 798899939921068033
__auto__ | {start_address} | 2022-09-22 19:35:13.274435 | 500 | 500 | 0 | 25 | 798899939972808705
__auto__ | {end_address} | 2022-09-22 19:35:13.274435 | 500 | 500 | 0 | 25 | 798899939987783681
__auto__ | {start_time} | 2022-09-22 19:35:13.274435 | 500 | 30 | 0 | 7 | 798899939956031489
__auto__ | {end_time} | 2022-09-22 19:35:13.274435 | 500 | 367 | 0 | 7 | 798899940003348481
__auto__ | {revenue} | 2022-09-22 19:35:13.274435 | 500 | 100 | 0 | 6 | 798899940018978817
__auto__ | {id} | 2022-09-22 19:37:13.395095 | 500 | 500 | 0 | 26 | 798900333460258817
__auto__ | {id,city} | 2022-09-22 19:37:13.395095 | 500 | 500 | 0 | 37 | NULL
__auto__ | {city} | 2022-09-22 19:37:13.395095 | 500 | 9 | 0 | 12 | 798900333442203649
__auto__ | {city,rider_id} | 2022-09-22 19:37:13.395095 | 500 | 50 | 0 | 29 | NULL
__auto__ | {vehicle_city} | 2022-09-22 19:37:13.395095 | 500 | 9 | 0 | 11 | 798900333525762049
__auto__ | {vehicle_city,vehicle_id} | 2022-09-22 19:37:13.395095 | 500 | 15 | 0 | 28 | NULL
__auto__ | {rider_id} | 2022-09-22 19:37:13.395095 | 500 | 50 | 0 | 17 | 798900333491945473
__auto__ | {vehicle_id} | 2022-09-22 19:37:13.395095 | 500 | 15 | 0 | 17 | 798900333540900865
__auto__ | {start_address} | 2022-09-22 19:37:13.395095 | 500 | 500 | 0 | 25 | 798900333573799937
__auto__ | {end_address} | 2022-09-22 19:37:13.395095 | 500 | 500 | 0 | 25 | 798900333588676609
__auto__ | {start_time} | 2022-09-22 19:37:13.395095 | 500 | 30 | 0 | 7 | 798900333605093377
__auto__ | {end_time} | 2022-09-22 19:37:13.395095 | 500 | 367 | 0 | 7 | 798900333623869441
__auto__ | {revenue} | 2022-09-22 19:37:13.395095 | 500 | 100 | 0 | 6 | 798900333639041025
__forecast__ | {id} | 2022-09-22 19:57:10.465606 | 500 | 500 | 0 | 26 | 0
__forecast__ | {id,city} | 2022-09-22 19:57:10.465606 | 500 | 500 | 0 | 37 | NULL
__forecast__ | {city} | 2022-09-22 19:57:10.465606 | 500 | 9 | 0 | 12 | 0
__forecast__ | {city,rider_id} | 2022-09-22 19:57:10.465606 | 500 | 50 | 0 | 29 | NULL
__forecast__ | {vehicle_city} | 2022-09-22 19:57:10.465606 | 500 | 9 | 0 | 11 | 0
__forecast__ | {vehicle_city,vehicle_id} | 2022-09-22 19:57:10.465606 | 500 | 15 | 0 | 28 | NULL
__forecast__ | {rider_id} | 2022-09-22 19:57:10.465606 | 500 | 50 | 0 | 17 | 0
__forecast__ | {vehicle_id} | 2022-09-22 19:57:10.465606 | 500 | 15 | 0 | 17 | 0
__forecast__ | {start_address} | 2022-09-22 19:57:10.465606 | 500 | 500 | 0 | 25 | 0
__forecast__ | {end_address} | 2022-09-22 19:57:10.465606 | 500 | 500 | 0 | 25 | 0
__forecast__ | {start_time} | 2022-09-22 19:57:10.465606 | 500 | 30 | 0 | 7 | 0
__forecast__ | {end_time} | 2022-09-22 19:57:10.465606 | 500 | 367 | 0 | 7 | 0
__forecast__ | {revenue} | 2022-09-22 19:57:10.465606 | 500 | 100 | 0 | 6 | 0
(52 rows)
```
### Delete statistics
To delete statistics for all tables in all databases:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
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:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
DELETE FROM system.table_statistics WHERE name = 'users_stats';
```
For more information about the `DELETE` statement, see `DELETE`.
## See also
* Cost-Based Optimizer
* `CREATE STATISTICS`
* `CREATE TABLE`
* `INSERT`
* `IMPORT`
* SQL Statements