From cockroachdb
Audits optimizer table statistics for staleness, missing coverage, and data quality issues using SHOW STATISTICS. Use when diagnosing poor query performance, unexpected plan changes, or after bulk data changes to identify stale statistics requiring refresh via CREATE STATISTICS.
npx claudepluginhub cockroachdb/claude-plugin --plugin cockroachdbThis skill uses the workspace's default tool permissions.
Audits optimizer table statistics for staleness, missing column coverage, and row count drift to diagnose poor query performance caused by outdated or incomplete statistics. Uses `SHOW STATISTICS` for read-only SQL analysis of table-level and column-level statistics freshness, entirely without requiring DB Console access.
Explains PostgreSQL query planner's use of pg_stats, histograms, and most-common-values to estimate selectivity and choose execution plans. Use for diagnosing row misestimations and suboptimal plans after data changes.
Audits Cloud SQL Postgres health: identifies storage bloat, invalid indexes, table statistics, autovacuum configs. Fetches query metrics via PromQL from Cloud Monitoring.
Monitors AlloyDB Postgres health: fetches database overview, column cardinality, invalid indexes, table stats, autovacuum configs, and instance details for optimization and issue identification.
Share bugs, ideas, or general feedback.
Audits optimizer table statistics for staleness, missing column coverage, and row count drift to diagnose poor query performance caused by outdated or incomplete statistics. Uses SHOW STATISTICS for read-only SQL analysis of table-level and column-level statistics freshness, entirely without requiring DB Console access.
Complement to profiling-statement-fingerprints: This skill diagnoses optimizer statistics issues; for identifying historically slow queries, see profiling-statement-fingerprints.
For historical query analysis: Use profiling-statement-fingerprints to identify slow statement patterns. For live query triage: Use triaging-live-sql-activity for immediate incident response.
sql.stats.automatic_collection.enabled = trueCheck automatic collection status:
SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled; -- Should return: true
Verify table access:
SHOW GRANTS ON TABLE database_name.table_name;
Table statistics provide the optimizer with data distribution information to estimate query costs:
| Statistic Type | Description | Impact on Optimizer |
|---|---|---|
| row_count | Total rows in table | Cardinality estimates for full scans |
| distinct_count | Unique values per column | Selectivity estimates for WHERE/JOIN predicates |
| null_count | NULL values per column | IS NULL / IS NOT NULL predicate costs |
| histogram | Value distribution buckets | Range scan selectivity (e.g., WHERE age BETWEEN 20 AND 30) |
Multi-column statistics capture correlation between columns (e.g., city + state + zip) for more accurate multi-predicate estimates.
Automatic collection (default):
Manual collection:
CREATE STATISTICS command for immediate refresh| Indicator | Definition | Recommended Action |
|---|---|---|
| Age | Time since last statistics collection | Refresh if >7 days (OLTP) or >30 days (OLAP) |
| Row count drift | Percent difference between current and cached row_count | Refresh if >20-30% drift detected |
| Missing columns | Columns without statistics | CREATE STATISTICS for frequently queried columns |
| Missing histograms | Columns without distribution data | Automatic collection handles; may need manual refresh |
See references/statistics-thresholds.md for workload-specific threshold guidance.
Default trigger: ~20% row count change (controlled by sql.stats.automatic_collection.fraction_stale_rows)
Collection schedule:
Check pending jobs:
SELECT job_id, description, status, fraction_completed
FROM [SHOW JOBS]
WHERE job_type = 'AUTO CREATE STATS'
AND status IN ('pending', 'running')
ORDER BY created DESC
LIMIT 20;
Finds tables with outdated statistics or no statistics at all, ranked by staleness.
WITH table_stats AS (
SELECT
table_catalog,
table_schema,
table_name,
column_names,
row_count,
created,
now() - created AS stats_age
FROM [SHOW STATISTICS FOR TABLE database_name.*] -- Replace database_name
WHERE column_names = '{}' -- Table-level stats only (empty array)
)
SELECT
table_schema || '.' || table_name AS full_table_name,
row_count,
created AS stats_created_at,
stats_age,
CASE
WHEN created IS NULL THEN 'Missing statistics'
WHEN stats_age > INTERVAL '30 days' THEN 'Very stale (>30d)'
WHEN stats_age > INTERVAL '7 days' THEN 'Stale (>7d)'
ELSE 'Fresh'
END AS staleness_status
FROM table_stats
WHERE stats_age > INTERVAL '7 days' OR created IS NULL -- Adjust threshold
ORDER BY stats_age DESC NULLS FIRST
LIMIT 50;
Customization:
database_name.* with specific schema pattern (e.g., mydb.public.*)INTERVAL '7 days' for OLTP, '30 days' for OLAPLIMIT to see more tablesKey columns:
staleness_status: Quick classification of statistics freshnessstats_age: Exact time since last collectionrow_count: Last known table sizeShows all statistics for a single table, including table-level and per-column details.
SELECT
column_names,
row_count,
distinct_count,
null_count,
created,
now() - created AS stats_age,
CASE
WHEN histogram_id IS NOT NULL THEN 'Yes'
ELSE 'No'
END AS has_histogram
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
ORDER BY
CASE WHEN column_names = '{}' THEN 0 ELSE 1 END, -- Table-level first
created DESC;
Customization:
database_name.schema_name.table_name with fully-qualified table nameKey columns:
column_names: Empty {} = table-level, single element = column-leveldistinct_count: Cardinality for selectivity estimatesnull_count: NULL value count for IS NULL predicateshas_histogram: Distribution data availabilityInterpretation:
Compares current table row count against cached statistics to identify significant drift.
WITH current_count AS (
SELECT count(*) AS actual_rows
FROM database_name.schema_name.table_name -- Replace with target table
),
stats_count AS (
SELECT row_count, created
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
WHERE column_names = '{}' -- Table-level stats
ORDER BY created DESC
LIMIT 1
)
SELECT
c.actual_rows,
s.row_count AS stats_rows,
s.created AS stats_created_at,
now() - s.created AS stats_age,
ABS(c.actual_rows - s.row_count) AS drift_absolute,
ROUND(
ABS(c.actual_rows - s.row_count)::NUMERIC /
NULLIF(s.row_count, 0) * 100,
2
) AS drift_pct,
CASE
WHEN ABS(c.actual_rows - s.row_count)::NUMERIC / NULLIF(s.row_count, 0) > 0.30 THEN 'High drift (>30%)'
WHEN ABS(c.actual_rows - s.row_count)::NUMERIC / NULLIF(s.row_count, 0) > 0.20 THEN 'Medium drift (>20%)'
WHEN ABS(c.actual_rows - s.row_count)::NUMERIC / NULLIF(s.row_count, 0) > 0.10 THEN 'Low drift (>10%)'
ELSE 'Minimal drift (<10%)'
END AS drift_status
FROM current_count c, stats_count s;
Customization:
Key columns:
drift_pct: Percentage difference between current and cached row countdrift_status: Classification for prioritizationstats_age: Time since statistics last refreshedInterpretation:
Finds table columns without statistics, focusing on columns frequently used in WHERE/JOIN clauses.
WITH table_columns AS (
SELECT column_name
FROM information_schema.columns
WHERE table_schema = 'schema_name' -- Replace
AND table_name = 'table_name' -- Replace
AND is_hidden = 'NO' -- Exclude internal columns
),
stats_columns AS (
SELECT UNNEST(column_names) AS column_name
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
WHERE column_names != '{}' -- Exclude table-level stats
)
SELECT
tc.column_name AS missing_column,
'No statistics available' AS status
FROM table_columns tc
WHERE tc.column_name NOT IN (SELECT column_name FROM stats_columns)
ORDER BY tc.column_name;
Customization:
Interpretation:
WHERE user_id = 123)JOIN orders ON users.id = orders.user_id)Action: Generate CREATE STATISTICS commands (see Query 7)
Identifies columns with/without histogram data for range query optimization.
SELECT
UNNEST(column_names) AS column_name,
created,
now() - created AS stats_age,
CASE
WHEN histogram_id IS NOT NULL THEN 'Has histogram'
ELSE 'Missing histogram'
END AS histogram_status
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
WHERE column_names != '{}' -- Exclude table-level stats
ORDER BY
CASE WHEN histogram_id IS NULL THEN 0 ELSE 1 END, -- Missing first
created DESC;
Customization:
Key columns:
histogram_status: Indicates distribution data availabilitystats_age: Time since histogram last updatedInterpretation:
Identifies existing multi-column (composite) statistics for correlated columns.
SELECT
column_names,
created,
now() - created AS stats_age,
row_count,
ARRAY_LENGTH(column_names, 1) AS column_count
FROM [SHOW STATISTICS FOR TABLE database_name.schema_name.table_name]
WHERE ARRAY_LENGTH(column_names, 1) > 1 -- Multi-column only
ORDER BY created DESC;
Customization:
Key columns:
column_names: Array of correlated columnscolumn_count: Number of columns in composite statisticInterpretation:
See references/create-statistics-examples.md for multi-column creation patterns.
Produces ready-to-run CREATE STATISTICS commands for tables with stale or missing statistics.
WITH stale_tables AS (
SELECT
table_schema,
table_name,
created,
now() - created AS stats_age
FROM [SHOW STATISTICS FOR TABLE database_name.*]
WHERE column_names = '{}'
AND (created IS NULL OR now() - created > INTERVAL '7 days') -- Adjust threshold
)
SELECT
table_schema || '.' || table_name AS full_table_name,
stats_age,
'CREATE STATISTICS __auto__ FROM ' || table_schema || '.' || table_name || ';' AS create_command
FROM stale_tables
ORDER BY stats_age DESC NULLS FIRST
LIMIT 50;
Customization:
database_name.* with schema patternINTERVAL '7 days' staleness thresholdLIMIT for more recommendationsOutput:
create_command: Copy-paste ready SQL command__auto__: Uses automatic column selection (recommended default)Execution:
Scenario: After bulk INSERT/COPY/IMPORT operation, validate statistics are current.
Steps:
Identify affected tables:
-- List tables modified in last 24 hours
SELECT DISTINCT table_schema || '.' || table_name AS full_table_name
FROM [SHOW TABLES]
WHERE table_schema = 'target_schema'; -- Replace
Check row count drift (Query 3): Run drift detection query for each affected table.
Generate and execute refresh commands (Query 7):
CREATE STATISTICS __auto__ FROM schema_name.table_name; -- From Query 7 output
Monitor collection job:
SELECT job_id, status, fraction_completed, running_status
FROM [SHOW JOBS]
WHERE job_type = 'CREATE STATS'
AND created > now() - INTERVAL '1 hour'
ORDER BY created DESC
LIMIT 10;
Verify refresh (Query 2):
Re-run statistics audit to confirm created timestamp updated.
Expected outcome: Statistics age <1 hour, drift_pct <5%.
Scenario: Query performance suddenly degrades; EXPLAIN shows different plan.
Steps:
Identify affected query from profiling-statement-fingerprints: Find query with latency spike or plan hash change.
Extract table references: Parse query text to identify tables in FROM/JOIN clauses.
Audit statistics for each table (Query 2): Check staleness and row count currency.
Compare historical vs current row counts:
-- Example: Check if table grew significantly
SELECT row_count, created
FROM [SHOW STATISTICS FOR TABLE users]
WHERE column_names = '{}'
ORDER BY created DESC
LIMIT 5; -- Last 5 collections
Refresh stale statistics (Query 7): Execute CREATE STATISTICS for tables with high drift.
Validate plan stability: Re-run EXPLAIN to verify plan returns to expected structure.
Expected outcome: Plan hash stabilizes, latency returns to baseline after statistics refresh.
Scenario: Periodic audit to proactively identify statistics issues before performance degrades.
Steps:
Run cluster-wide staleness scan (Query 1):
-- All databases
SHOW STATISTICS FOR TABLE *.*; -- Warning: May be slow on large clusters
Prioritize critical tables: Focus on high-traffic tables from profiling-statement-fingerprints.
Check automatic collection is enabled:
SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled; -- Should be true
Review pending auto-collection jobs:
SELECT job_id, description, status, fraction_completed
FROM [SHOW JOBS]
WHERE job_type = 'AUTO CREATE STATS'
AND status IN ('pending', 'running')
ORDER BY created DESC;
Generate batch refresh script (Query 7): Save output to file for scheduled execution.
Schedule refresh during maintenance window: Execute generated CREATE STATISTICS commands during low-traffic period.
Frequency: Weekly for OLTP, monthly for OLAP.
SHOW STATISTICS:
CREATE STATISTICS:
Small tables (<10K rows): Negligible impact, safe anytime
Medium tables (10K-10M rows): Seconds to minutes, minor impact
Large tables (>10M rows): Minutes to hours, plan accordingly:
SHOW JOBS to track progressCancellation (if needed):
-- Find job ID
SELECT job_id, status, fraction_completed
FROM [SHOW JOBS]
WHERE job_type = 'CREATE STATS' AND status = 'running';
-- Cancel job (non-destructive, existing statistics remain)
CANCEL JOB 123456789012345678;
Avoid overwhelming cluster:
Example staggered script:
# Collect statistics in batches with delays
for table in table1 table2 table3; do
cockroach sql -e "CREATE STATISTICS __auto__ FROM $table;" &
done
wait # Wait for batch to complete
sleep 60 # Delay between batches
for table in table4 table5 table6; do
cockroach sql -e "CREATE STATISTICS __auto__ FROM $table;" &
done
wait
See references/create-statistics-examples.md for detailed batch patterns.
| Issue | Likely Cause | Fix |
|---|---|---|
| SHOW STATISTICS returns empty | No statistics ever collected | Run CREATE STATISTICS __auto__ FROM table_name; |
| row_count shows 0 for non-empty table | Statistics out of sync | Refresh: CREATE STATISTICS __auto__ FROM table_name; |
| Permission denied error | No privileges on table | Grant any privilege: GRANT SELECT ON table_name TO user; |
| CREATE STATISTICS job stuck | Large table with high write volume | Check SHOW JOBS status; consider CANCEL JOB and retry during low-traffic period |
| Automatic collection not triggering | Setting disabled or threshold not met | Verify sql.stats.automatic_collection.enabled = true and check row count drift |
| Statistics exist but query plans still poor | Stale statistics or missing multi-column stats | Refresh existing; create multi-column for correlated columns (see Query 6) |
| High drift but recent created timestamp | Extreme write volume between collections | Lower automatic collection threshold or increase manual refresh frequency |
Handle missing statistics:
-- Use COALESCE for NULL created timestamps
SELECT COALESCE(created, '1970-01-01'::TIMESTAMP) AS stats_created_at
FROM [SHOW STATISTICS FOR TABLE table_name]
WHERE column_names = '{}';
Avoid division by zero in drift calculations:
-- Use NULLIF to prevent divide-by-zero errors
SELECT
ABS(actual - stats)::NUMERIC / NULLIF(stats, 0) * 100 AS drift_pct
FROM ...;
Automatic (default):
Manual:
Default retention: Controlled by sql.stats.persisted_rows.max (default ~10 million rows across cluster)
Historical analysis:
-- View statistics history for a table
SELECT column_names, row_count, created
FROM [SHOW STATISTICS FOR TABLE table_name]
WHERE column_names = '{}'
ORDER BY created DESC
LIMIT 10; -- Last 10 collections
What histograms optimize:
WHERE age BETWEEN 20 AND 30WHERE price > 100ORDER BY created_at LIMIT 10What histograms don't optimize:
WHERE id = 123 (uses distinct_count instead)When to create:
Creation:
-- Example: Correlated columns
CREATE STATISTICS city_state_stats ON city, state FROM addresses;
Limitation: Only manual creation (automatic collection does NOT create multi-column statistics)
See references/create-statistics-examples.md for comprehensive patterns.
Large table strategies:
AS OF SYSTEM TIME for historical analysis without impacting live trafficResource monitoring during collection:
-- Check running statistics jobs
SELECT job_id, description, status, fraction_completed, running_status
FROM [SHOW JOBS]
WHERE job_type IN ('CREATE STATS', 'AUTO CREATE STATS')
AND status = 'running';
OLTP workloads:
OLAP/Analytics workloads:
Hybrid workloads:
See references/statistics-thresholds.md for detailed guidance.
Required: Any privilege on table (SELECT, INSERT, UPDATE, DELETE, or admin role)
Comparison to other diagnostics:
Grant example:
-- Grant SELECT (least privileged) for statistics visibility
GRANT SELECT ON TABLE database_name.table_name TO diagnostics_user;
Official CockroachDB Documentation:
Related Skills:
Supplementary References: