> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # SHOW STATISTICS The `SHOW STATISTICS` [statement](/docs/stable/sql-statements.md) lists [table statistics](/docs/stable/create-statistics.md) used by the [cost-based optimizer](/docs/stable/cost-based-optimizer.md). By default, CockroachDB [automatically generates statistics](/docs/stable/cost-based-optimizer.md#table-statistics) on all indexed columns and up to 100 non-indexed columns, and automatically collects [multi-column statistics](/docs/stable/create-statistics.md#create-statistics-on-multiple-columns) on the columns that prefix each index. ## Synopsis ![show\_stats syntax diagram](https://mintcdn.com/cockroachlabs/R40d76ku6ZWAJ59v/images/sql-diagrams/v26.3/show_stats.svg?fit=max\&auto=format\&n=R40d76ku6ZWAJ59v\&q=85\&s=4fb44f77e9afaa72b44ed952ad0c6f48) ## Required privileges To list table statistics, the user must have any [privilege](/docs/stable/security-reference/authorization.md#managing-privileges) 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 [`timestamptz`](/docs/stable/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](/docs/stable/cost-based-optimizer.md#control-histogram-collection) used to compute statistics. | ## Examples #### Setup To follow along, run [`cockroach demo`](/docs/stable/cockroach-demo) to start a temporary, in-memory cluster with the [`movr`](/docs/stable/movr) sample 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](/docs/stable/cost-based-optimizer.md#forecasted-statistics) along with the existing table statistics. 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`](/docs/stable/delete). ## See also * [Cost-Based Optimizer](/docs/stable/cost-based-optimizer.md) * [`CREATE STATISTICS`](/docs/stable/create-statistics) * [`CREATE TABLE`](/docs/stable/create-table) * [`INSERT`](/docs/stable/insert) * [SQL Statements](/docs/stable/sql-statements.md)