> ## 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. # CREATE STATISTICS Use the `CREATE STATISTICS` [statement](/docs/stable/sql-statements.md) to [generate table statistics for the cost-based optimizer](/docs/stable/cost-based-optimizer.md#table-statistics) to use. Once you [create a table](/docs/stable/create-table.md) and load data into it (e.g., [`INSERT`](/docs/stable/insert), [`IMPORT INTO`](/docs/stable/import-into)), table statistics can be generated. Table statistics help the cost-based optimizer determine the cardinality of the rows used in each query, which helps to predict more accurate costs. For compatibility with PostgreSQL, CockroachDB supports the `ANALYZE`/`ANALYSE` statement as an alias for `CREATE STATISTICS`. For syntax, see [Aliases](#aliases). 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](#create-statistics-on-multiple-columns) on columns that prefix each index. As a result, most users do not need to directly issue `CREATE STATISTICS` statements. ## Syntax ![create\_stats syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/create_stats.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=08931e7a399b99e456f2bdc6ef65064f) ## Parameters | Parameter | Description | | - | - | | `statistics_name` | The name of the set of statistics you are creating. | | `opt_stats_columns` | The name of the column(s) to create statistics for. | | `create_stats_target` | The name of the table to create statistics for. | | `opt_as_of_clause` | Create historical stats using the [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time) clause. For instructions, see [Create statistics as of a given time](#create-statistics-as-of-a-given-time). | ## Required privileges The user must have the [`MAINTAIN` privilege](/docs/stable/security-reference/authorization#maintain) or the `SELECT` privilege on the target table. [Owners and admins](/docs/stable/security-reference/authorization.md#object-ownership) implicitly have this privilege. ## Aliases For [PostgreSQL compatibility](/docs/stable/postgresql-compatibility.md), CockroachDB supports `ANALYZE` and `ANALYSE` as aliases for `CREATE STATISTICS`. ### Alias syntax ![analyze syntax diagram](https://mintcdn.com/cockroachlabs/VQ_w3c6MHYdiS7Tn/images/sql-diagrams/common/analyze.svg?fit=max\&auto=format\&n=VQ_w3c6MHYdiS7Tn\&q=85\&s=ab603a9014954edbe095d1559848407a) ### Alias parameters | Parameter | Description | | - | - | | `analyze_target` | The name of the table to create statistics for. | ## 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 ``` ### Create statistics on a single column ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE STATISTICS revenue_stats ON revenue FROM rides; ``` ```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__ | {city} | 2022-04-20 22:43:08.851613 | 500 | 9 | 0 | 12 | 755053982033936385 __auto__ | {id} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 26 | 755053982039703553 __auto__ | {city,id} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 37 | NULL __auto__ | {rider_id} | 2022-04-20 22:43:08.851613 | 500 | 50 | 0 | 17 | 755053982050910209 __auto__ | {city,rider_id} | 2022-04-20 22:43:08.851613 | 500 | 50 | 0 | 29 | NULL __auto__ | {vehicle_city} | 2022-04-20 22:43:08.851613 | 500 | 9 | 0 | 11 | 755053982061690881 __auto__ | {vehicle_id} | 2022-04-20 22:43:08.851613 | 500 | 15 | 0 | 17 | 755053982067392513 __auto__ | {vehicle_city,vehicle_id} | 2022-04-20 22:43:08.851613 | 500 | 15 | 0 | 28 | NULL __auto__ | {start_address} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 25 | 755053982080991233 __auto__ | {end_address} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 25 | 755053982087544833 __auto__ | {start_time} | 2022-04-20 22:43:08.851613 | 500 | 30 | 0 | 7 | 755053982093443073 __auto__ | {end_time} | 2022-04-20 22:43:08.851613 | 500 | 367 | 0 | 7 | 755053982099472385 __auto__ | {revenue} | 2022-04-20 22:43:08.851613 | 500 | 100 | 0 | 6 | 755053982105337857 revenue_stats | {revenue} | 2022-04-20 22:35:42.279266 | 500 | 100 | 0 | 6 | 755052518729449473 (14 rows) ``` Statistics are automatically collected for **all columns**, making the `revenue_stats` statistics a duplicate of the statistics automatically collected on the `revenue` column. ### Create statistics on multiple columns ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE STATISTICS city_revenue_stats ON city, revenue FROM rides; ``` ```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__ | {city} | 2022-04-20 22:43:08.851613 | 500 | 9 | 0 | 12 | 755053982033936385 __auto__ | {id} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 26 | 755053982039703553 __auto__ | {city,id} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 37 | NULL __auto__ | {rider_id} | 2022-04-20 22:43:08.851613 | 500 | 50 | 0 | 17 | 755053982050910209 __auto__ | {city,rider_id} | 2022-04-20 22:43:08.851613 | 500 | 50 | 0 | 29 | NULL __auto__ | {vehicle_city} | 2022-04-20 22:43:08.851613 | 500 | 9 | 0 | 11 | 755053982061690881 __auto__ | {vehicle_id} | 2022-04-20 22:43:08.851613 | 500 | 15 | 0 | 17 | 755053982067392513 __auto__ | {vehicle_city,vehicle_id} | 2022-04-20 22:43:08.851613 | 500 | 15 | 0 | 28 | NULL __auto__ | {start_address} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 25 | 755053982080991233 __auto__ | {end_address} | 2022-04-20 22:43:08.851613 | 500 | 500 | 0 | 25 | 755053982087544833 __auto__ | {start_time} | 2022-04-20 22:43:08.851613 | 500 | 30 | 0 | 7 | 755053982093443073 __auto__ | {end_time} | 2022-04-20 22:43:08.851613 | 500 | 367 | 0 | 7 | 755053982099472385 __auto__ | {revenue} | 2022-04-20 22:43:08.851613 | 500 | 100 | 0 | 6 | 755053982105337857 revenue_stats | {revenue} | 2022-04-20 22:45:40.272665 | 500 | 100 | 0 | 6 | 755054478211481601 city_revenue_stats | {city,revenue} | 2022-04-20 22:45:46.925799 | 500 | 372 | 0 | 18 | NULL (15 rows) ``` Multi-column statistics are automatically collected for **all columns that prefix an index**. In this example, `city` and `revenue` are not an index prefix, making the `city_revenue_stats` statistics unique for the table. ### Create statistics on a default set of columns The `CREATE STATISTICS` statement shown below automatically figures out which columns to get statistics on. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE STATISTICS users_stats FROM users; ``` This statement creates statistics identical to the statistics that CockroachDB creates automatically. ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > SHOW STATISTICS FOR TABLE users; ``` ``` statistics_name | column_names | created | row_count | distinct_count | null_count | avg_size | histogram_id ------------------+---------------+----------------------------+-----------+----------------+------------+----------+--------------------- __auto__ | {city} | 2022-04-20 22:43:08.819069 | 50 | 9 | 0 | 12 | 755053981927636993 __auto__ | {id} | 2022-04-20 22:43:08.819069 | 50 | 50 | 0 | 27 | 755053981933273089 __auto__ | {city,id} | 2022-04-20 22:43:08.819069 | 50 | 50 | 0 | 38 | NULL __auto__ | {name} | 2022-04-20 22:43:08.819069 | 50 | 49 | 0 | 16 | 755053981944184833 __auto__ | {address} | 2022-04-20 22:43:08.819069 | 50 | 50 | 0 | 26 | 755053981949001729 __auto__ | {credit_card} | 2022-04-20 22:43:08.819069 | 50 | 50 | 0 | 12 | 755053981954015233 users_stats | {city} | 2022-04-20 22:46:14.878975 | 50 | 9 | 0 | 12 | 755054591614943233 users_stats | {id} | 2022-04-20 22:46:14.878975 | 50 | 50 | 0 | 27 | 755054591622447105 users_stats | {city,id} | 2022-04-20 22:46:14.878975 | 50 | 50 | 0 | 38 | NULL users_stats | {name} | 2022-04-20 22:46:14.878975 | 50 | 49 | 0 | 16 | 755054591638634497 users_stats | {address} | 2022-04-20 22:46:14.878975 | 50 | 50 | 0 | 26 | 755054591645712385 users_stats | {credit_card} | 2022-04-20 22:46:14.878975 | 50 | 50 | 0 | 12 | 755054591652691969 (12 rows) ``` ### Create statistics as of a given time To create statistics as of a given time (in this example, 1 minute ago to avoid interfering with the production workload), run a statement like the following: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > CREATE STATISTICS vehicle_stats_1 FROM vehicles AS OF SYSTEM TIME '-1m'; ``` For more information about how the `AS OF SYSTEM TIME` clause works, including supported time formats, see [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time). ### Create partial statistics using extremes CockroachDB supports [partial statistics](/docs/stable/cost-based-optimizer.md#partial-statistics), which collect statistics on a subset of table data to provide more up-to-date information without scanning the entire table. To create [partial statistics](/docs/stable/cost-based-optimizer.md#partial-statistics) that collect statistics on the highest and lowest index values: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE STATISTICS rides_extremes_stats FROM rides USING EXTREMES; ``` This creates partial statistics on all single-column prefixes of non-inverted indexes in the `rides` table by scanning only the highest and lowest index values, rather than performing a full table scan. You can also create extremes statistics on specific columns, provided there is an index with the specified column as the first key column: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE STATISTICS city_extremes_stats ON city FROM rides USING EXTREMES; ``` ### Create partial statistics on specific data To create [partial statistics](/docs/stable/cost-based-optimizer.md#partial-statistics) on a specific column and values matching specific conditions, ensure the column is indexed: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE INDEX ON rides (revenue); ``` Partial statistics can target any subset of data that matches specific conditions. For example, to create statistics on high-value rides: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE STATISTICS high_value_rides_stats ON revenue FROM rides WHERE revenue > 50; ``` This statement creates partial statistics that cover only high-value rides. ### 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). ### View statistics jobs Every time the `CREATE STATISTICS` statement is executed, it starts a background job. This is true for queries issued by your application as well as queries issued for [automatically generated statistics](/docs/stable/cost-based-optimizer.md#table-statistics). To view statistics jobs, there are two options: 1. Use [`SHOW JOBS`](/docs/stable/show-jobs) to see all statistics jobs that were created by user queries (i.e., someone entering `CREATE STATISTICS` at the SQL prompt or via application code): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW JOBS) SELECT * FROM x WHERE job_type LIKE '%CREATE STATS%'; ``` ``` job_id | job_type | description | statement | user_name | status | running_status | created | started | finished | modified | fraction_completed | error | coordinator_id | trace_id | last_run | next_run | num_runs | execution_errors ---------------------+--------------+--------------------------------------------------------------------------------------------------+-----------+-----------+-----------+----------------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------+---------------------+----------------------------+----------------------------+----------+------------------- 755053959919108097 | CREATE STATS | CREATE STATISTICS revenue_stats ON revenue FROM movr.public.rides | | demo | succeeded | NULL | 2022-04-20 22:43:02.104229 | 2022-04-20 22:43:02.109754 | 2022-04-20 22:43:02.123381 | 2022-04-20 22:43:02.122569 | 1 | | 1 | 2935658651779633570 | 2022-04-20 22:43:02.109755 | 2022-04-20 22:43:32.109755 | 1 | {} 755054478158364673 | CREATE STATS | CREATE STATISTICS revenue_stats ON revenue FROM movr.public.rides | | demo | succeeded | NULL | 2022-04-20 22:45:40.25829 | 2022-04-20 22:45:40.26361 | 2022-04-20 22:45:40.275882 | 2022-04-20 22:45:40.275032 | 1 | | 1 | 3941365223642966402 | 2022-04-20 22:45:40.263611 | 2022-04-20 22:46:10.263611 | 1 | {} 755054499947053057 | CREATE STATS | CREATE STATISTICS city_revenue_stats ON city, revenue FROM movr.public.rides | | demo | succeeded | NULL | 2022-04-20 22:45:46.907672 | 2022-04-20 22:45:46.912906 | 2022-04-20 22:45:46.929632 | 2022-04-20 22:45:46.928409 | 1 | | 1 | 6666823949040131150 | 2022-04-20 22:45:46.912906 | 2022-04-20 22:46:16.912906 | 1 | {} 755054591559172097 | CREATE STATS | CREATE STATISTICS users_stats FROM movr.public.users | | demo | succeeded | NULL | 2022-04-20 22:46:14.865479 | 2022-04-20 22:46:14.869725 | 2022-04-20 22:46:14.895666 | 2022-04-20 22:46:14.89469 | 1 | | 1 | 6731618360724088456 | 2022-04-20 22:46:14.869726 | 2022-04-20 22:46:44.869726 | 1 | {} 755055101600661505 | CREATE STATS | CREATE STATISTICS vehicle_stats_1 FROM movr.public.vehicles WITH OPTIONS AS OF SYSTEM TIME '-1m' | | demo | succeeded | NULL | 2022-04-20 22:48:50.517787 | 2022-04-20 22:48:50.523002 | 2022-04-20 22:48:50.552397 | 2022-04-20 22:48:50.551333 | 1 | | 1 | 8797094911788373312 | 2022-04-20 22:48:50.523003 | 2022-04-20 22:49:20.523003 | 1 | {} (5 rows) ``` 2. Use `SHOW AUTOMATIC JOBS` to see statistics jobs that were created by [automatically generated statistics](/docs/stable/cost-based-optimizer.md#table-statistics): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} > WITH x AS (SHOW AUTOMATIC JOBS) SELECT * FROM x WHERE job_type LIKE '%CREATE STATS%'; ``` ``` job_id | job_type | description | statement | user_name | status | running_status | created | started | finished | modified | fraction_completed | error | coordinator_id | trace_id | last_run | next_run | num_runs | execution_errors ---------------------+-------------------+---------------------------------------------------------------------+--------------------------------------------------------------------------------------------+-----------+-----------+----------------+----------------------------+----------------------------+----------------------------+----------------------------+--------------------+-------+----------------+---------------------+----------------------------+----------------------------+----------+------------------- 755046859132338177 | AUTO CREATE STATS | Table statistics refresh for movr.public.vehicle_location_histories | CREATE STATISTICS __auto__ FROM [109] WITH OPTIONS THROTTLING 0.9 AS OF SYSTEM TIME '-30s' | root | succeeded | NULL | 2022-04-20 22:06:55.116073 | 2022-04-20 22:06:55.126215 | 2022-04-20 22:06:55.81112 | 2022-04-20 22:06:55.809757 | 1 | | 1 | 7300707583932918060 | 2022-04-20 22:06:55.126216 | 2022-04-20 22:07:25.126216 | 1 | {} 755046861432094721 | AUTO CREATE STATS | Table statistics refresh for movr.public.user_promo_codes | CREATE STATISTICS __auto__ FROM [111] WITH OPTIONS THROTTLING 0.9 AS OF SYSTEM TIME '-30s' | root | succeeded | NULL | 2022-04-20 22:06:55.817903 | 2022-04-20 22:06:55.825208 | 2022-04-20 22:06:55.858624 | 2022-04-20 22:06:55.857442 | 1 | | 1 | 944378671132247270 | 2022-04-20 22:06:55.825209 | 2022-04-20 22:07:25.825209 | 1 | {} 755046861588529153 | AUTO CREATE STATS | Table statistics refresh for movr.public.rides | CREATE STATISTICS __auto__ FROM [108] WITH OPTIONS THROTTLING 0.9 AS OF SYSTEM TIME '-30s' | root | succeeded | NULL | 2022-04-20 22:06:55.865648 | 2022-04-20 22:06:55.871917 | 2022-04-20 22:06:56.772123 | 2022-04-20 22:06:56.770818 | 1 | | 1 | 107998367520189635 | 2022-04-20 22:06:55.871917 | 2022-04-20 22:07:25.871917 | 1 | {} 755046864579690497 | AUTO CREATE STATS | Table statistics refresh for movr.public.vehicles | CREATE STATISTICS __auto__ FROM [107] WITH OPTIONS THROTTLING 0.9 AS OF SYSTEM TIME '-30s' | root | succeeded | NULL | 2022-04-20 22:06:56.778476 | 2022-04-20 22:06:56.785127 | 2022-04-20 22:06:56.887308 | 2022-04-20 22:06:56.886099 | 1 | | 1 | 1370771572055208171 | 2022-04-20 22:06:56.785128 | 2022-04-20 22:07:26.785128 | 1 | {} 755046864953376769 | AUTO CREATE STATS | Table statistics refresh for movr.public.promo_codes | CREATE STATISTICS __auto__ FROM [110] WITH OPTIONS THROTTLING 0.9 AS OF SYSTEM TIME '-30s' | root | succeeded | NULL | 2022-04-20 22:06:56.892515 | 2022-04-20 22:06:56.898684 | 2022-04-20 22:06:57.416186 | 2022-04-20 22:06:57.414741 | 1 | | 1 | 3756312875539405235 | 2022-04-20 22:06:56.898685 | 2022-04-20 22:07:26.898685 | 1 | {} 755046866689851393 | AUTO CREATE STATS | Table statistics refresh for movr.public.users | CREATE STATISTICS __auto__ FROM [106] WITH OPTIONS THROTTLING 0.9 AS OF SYSTEM TIME '-30s' | root | succeeded | NULL | 2022-04-20 22:06:57.422449 | 2022-04-20 22:06:57.428999 | 2022-04-20 22:06:57.573797 | 2022-04-20 22:06:57.57256 | 1 | | 1 | 6690084610338235566 | 2022-04-20 22:06:57.428999 | 2022-04-20 22:07:27.428999 | 1 | {} (6 rows) ``` ## Known limitations The `ANALYZE` alias of [`CREATE STATISTICS`](/docs/stable/create-statistics) does not support specifying an [`AS OF SYSTEM TIME`](/docs/stable/as-of-system-time) timestamp. `ANALYZE` statements use `AS OF SYSTEM TIME '-0.001ms'` automatically. For more control over the statistics interval, use the `CREATE STATISTICS` syntax instead. ## See also * [Cost-Based Optimizer](/docs/stable/cost-based-optimizer.md) * [`SHOW STATISTICS`](/docs/stable/show-statistics) * [`CREATE TABLE`](/docs/stable/create-table) * [`INSERT`](/docs/stable/insert) * [`IMPORT INTO`](/docs/stable/import-into) * [`SHOW JOBS`](/docs/stable/show-jobs) * [SQL Statements](/docs/stable/sql-statements.md)