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

# Canary 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 <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

New in v26.2: The *canary statistics* feature improves <InternalLink path="performance-best-practices-overview">query performance</InternalLink> by allowing CockroachDB to try newly collected <InternalLink path="cost-based-optimizer#table-statistics">table statistics</InternalLink> on a small number of queries before deploying them for all queries. By testing the efficiency of <InternalLink path="cost-based-optimizer">query plans</InternalLink> 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` <InternalLink path="with-storage-parameter#table-parameters">table storage parameter</InternalLink> 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 <InternalLink path="create-statistics">`ANALYZE` or `CREATE STATISTICS`</InternalLink>, they are also subject to the canary window. However, you can force manually created statistics to take effect immediately using <InternalLink path="alter-table">`ALTER TABLE`</InternalLink> with the <InternalLink path="alter-table#reset-storage-parameter">`RESET`</InternalLink> 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 <InternalLink path="cost-based-optimizer">query optimizer</InternalLink> 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 <InternalLink path="cluster-settings">`sql.stats.canary_fraction` cluster setting</InternalLink>. 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` <InternalLink path="session-variables#supported-variables">session variable</InternalLink>. 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 <InternalLink path="ui-overview">DB Console</InternalLink> contains metrics about canary statistics on the <InternalLink path="ui-statements-page#charts">**Statement Fingerprint** page</InternalLink>. 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 <InternalLink path="cost-based-optimizer">query optimizer</InternalLink> chose using each statistics set. These charts only appear when canary statistics are enabled.

When using the canary statistics feature, the <InternalLink path="explain">`EXPLAIN`</InternalLink> and <InternalLink path="explain-analyze">`EXPLAIN ANALYZE`</InternalLink> 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

* <InternalLink path="cost-based-optimizer">Cost-Based Optimizer</InternalLink>
* <InternalLink path="performance-best-practices-overview">SQL Performance Best Practices</InternalLink>
* <InternalLink path="with-storage-parameter">`WITH (storage parameter)`</InternalLink>
* <InternalLink path="cluster-settings">Cluster Settings</InternalLink>
* <InternalLink path="session-variables">Session Variables</InternalLink>
* <InternalLink path="explain">`EXPLAIN`</InternalLink>
* <InternalLink path="explain-analyze">`EXPLAIN ANALYZE`</InternalLink>
* <InternalLink path="ui-statements-page">Statements Page</InternalLink>