This feature is in preview and subject to change. To share feedback and/or issues, contact Support.
New in v26.2: The canary statistics feature improves query performance by allowing CockroachDB to try newly collected table statistics on a small number of queries before deploying them for all queries. By testing the efficiency of query plans 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 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:
CREATE TABLE t (x int) WITH (sql_stats_canary_window = '1h');
If you create statistics manually with ANALYZE or CREATE STATISTICS, they are also subject to the canary window. However, you can force manually created statistics to take effect immediately using ALTER TABLE with the RESET subcommand.
Example:
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 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. 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:
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. 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:
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 contains metrics about canary statistics on the Statement Fingerprint page. 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 chose using each statistics set. These charts only appear when canary statistics are enabled.
When using the canary statistics feature, the EXPLAIN and 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.
