auditing-table-statistics

Auditing Table Statistics

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.

Prerequisites

• SQL connection with any privilege on target tables
• Automatic statistics collection enabled (default): sql.stats.automatic_collection.enabled = true

Related skills: profiling-statement-fingerprints for historical query analysis, triaging-live-sql-activity for live triage.

Core Concepts

CockroachDB-specific defaults:

• Automatic collection triggers at ~20% row count change (sql.stats.automatic_collection.fraction_stale_rows)
• Auto-collection covers index column groups (when sql.stats.multi_column_collection.enabled = true, the default); ad-hoc multi-column stats on non-indexed columns require manual CREATE STATISTICS
• Large tables (>10M rows) may have delayed auto-collection
• Staleness thresholds: refresh if >7 days (OLTP) or >30 days (OLAP), or >20-30% row count drift

See references/statistics-thresholds.md for workload-specific guidance.

Core Diagnostic Queries

Query 1: Identify Tables with Stale or Missing Statistics

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;