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

# Canary Statistics

> [!NOTE]
>
> **This feature is in [preview](/docs/releases/cockroachdb-feature-availability.md)** and subject to change. To share feedback and/or issues, contact [Support](https://support.cockroachlabs.com).

The *canary statistics* feature improves [query performance](/docs/stable/performance-best-practices-overview.md) by allowing CockroachDB to try newly collected [table statistics](/docs/stable/cost-based-optimizer.md#table-statistics) on a small number of queries before deploying them for all queries. By testing the efficiency of [query plans](/docs/stable/cost-based-optimizer.md) made with new statistics on a fraction of queries, canary statistics blocks bad plans based on inaccurate statistics from impacting the full workload.

## The canary window

When CockroachDB generates new statistics on a table, they begin in canary status. The `sql_stats_canary_window` [table storage parameter](/docs/stable/with-storage-parameter.md#table-parameters) sets a *canary window* defining how long the statistics remain in canary status before being promoted to stable status. However, if new statistics are generated, the previous statistics are automatically promoted to stable status even if their canary window has not yet elapsed. If `sql_stats_canary_window` is set to 0, the canary statistics feature is disabled.

Example usage:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
CREATE TABLE t (x int) WITH (sql_stats_canary_window = '1h');
```

If you create statistics manually with [`ANALYZE` or `CREATE STATISTICS`](/docs/stable/create-statistics), they are also subject to the canary window. However, you can force manually created statistics to take effect immediately using [`ALTER TABLE`](/docs/stable/alter-table) with the [`RESET`](/docs/stable/alter-table#reset-storage-parameter) subcommand.

Example:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
ANALYZE t;
-- reset the canary window to 0, causing queries to use the new stats immediately
ALTER TABLE t RESET (sql_stats_canary_window);
-- wait 1 hour for the skipped canary widow to elapse, then set the window again
ALTER TABLE t SET (sql_stats_canary_window = '1h');
```

## Queries with canary statistics

When the [query optimizer](/docs/stable/cost-based-optimizer.md) makes a plan for a query, it must determine whether to use canary statistics or stable statistics in its calculations. This determination is based on a probability configured in the [`sql.stats.canary_fraction` cluster setting](/docs/stable/cluster-settings). Queries do not mix stastistics types, so if a query is selected to use canary statistics, it uses them for all tables. Setting `sql.stats.canary_fraction` to `0` disables canary statistics across all queries.

Example usage:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET CLUSTER SETTING sql.stats.canary_fraction = 0.2;
```

You can override the configured probability of using canary statistics for a particular session using the `canary_stats_mode` [session variable](/docs/stable/session-variables.md#supported-variables). Set `canary_stats_mode` to `force_canary` to use only canary statistics for the duration of the session, or set it to `force_stable` to use only stable statistics. This variable is useful for debugging or troubleshooting specific queries.

Example usage:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SET canary_stats_mode = 'force_canary';
```

## Analytical tools for canary statistics

Analyzing canary statistics allows you to visualize how new statistics affect query performance and catch problematic statistics before they impact your full workload. The [DB Console](/docs/stable/ui-overview.md) contains metrics about canary statistics on the [**Statement Fingerprint** page](/docs/stable/ui-statements-page.md#charts). The **Canary vs Stable Statement Times** chart shows planning and execution latency for each statistics set, and the **Canary vs Stable Plan Distribution** chart shows which plans the [query optimizer](/docs/stable/cost-based-optimizer.md) chose using each statistics set. These charts only appear when canary statistics are enabled.

When using the canary statistics feature, the [`EXPLAIN`](/docs/stable/explain) and [`EXPLAIN ANALYZE`](/docs/stable/explain-analyze) statements show the table's current canary window and whether a query is using canary statistics or stable statistics. These fields only appear when canary statistics are enabled.

## See also

* [Cost-Based Optimizer](/docs/stable/cost-based-optimizer.md)
* [SQL Performance Best Practices](/docs/stable/performance-best-practices-overview.md)
* [`WITH (storage parameter)`](/docs/stable/with-storage-parameter)
* [Cluster Settings](/docs/stable/cluster-settings.md)
* [Session Variables](/docs/stable/session-variables.md)
* [`EXPLAIN`](/docs/stable/explain)
* [`EXPLAIN ANALYZE`](/docs/stable/explain-analyze)
* [Statements Page](/docs/stable/ui-statements-page.md)