Skip to main content
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

show_stats syntax diagram

Required privileges

To list table statistics, the user must have the SELECT privilege on the table being inspected. Users who hold only other privileges on the table, such as INSERT or MAINTAIN, cannot read its statistics. The same requirement applies to SHOW HISTOGRAM for the table that the histogram describes. If row-level security policies apply to the user on the table, SHOW STATISTICS and SHOW HISTOGRAM return an error instead of statistics or an empty result, because statistics describe every row in the table. Admin users, roles with BYPASSRLS, and the table owner are unaffected, unless the table has FORCE ROW LEVEL SECURITY set. Setting the row_security session variable to off does not bypass this check. For details, refer to Table statistics.

Parameters

Options

Output

Examples

Setup

To follow along, run cockroach demo to start a temporary, in-memory cluster with the movr sample dataset preloaded:

List table statistics

View a histogram

To view the buckets of a histogram, pass the value of the histogram_id column from SHOW STATISTICS output to SHOW HISTOGRAM. The following example uses the histogram for the city column of the rides table:
Each row is one bucket. upper_bound is the largest value in the bucket, equal_rows is the number of rows equal to upper_bound, range_rows is the number of rows strictly between the previous bucket’s upper bound and this one, and distinct_range_rows is the number of distinct values among those rows. SHOW HISTOGRAM has the same required privileges as SHOW STATISTICS: the user must have SELECT on the table that the histogram describes, and the statement returns an error if row-level security policies apply to the user on that table.

Display forecasted statistics

The WITH FORECAST option calculates and displays forecasted statistics along with the existing table statistics. The following example shows 3 historical statistics collections and the subsequent forecast:

Delete statistics

To delete statistics for all tables in all databases:
To delete a named set of statistics (e.g, one named “users_stats”), run a query like the following:
For more information about the DELETE statement, see DELETE.

See also