auditing-table-statistics

What this skill does

# 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](../profiling-statement-fingerprints/SKILL.md).

## 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](../profiling-statement-fingerprints/SKILL.md) for historical query analysis, [triaging-live-sql-activity](../triaging-live-sql-activity/SKILL.md) 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](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.

```sql
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:**
- Replace `database_name.*` with specific schema pattern (e.g., `mydb.public.*`)
- Adjust staleness threshold: `INTERVAL '7 days'` for OLTP, `'30 days'` for OLAP
- Increase `LIMIT` to see more tables

**Key columns:**
- `staleness_status`: Quick classification of statistics freshness
- `stats_age`: Exact time since last collection
- `row_count`: Last known table size

### Query 2: Audit Statistics for Specific Table

Shows all statistics for a single table, including table-level and per-column details.

```sql
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:**
- Replace `database_name.schema_name.table_name` with fully-qualified table name

**Key columns:**
- `column_names`: Empty `{}` = table-level, single element = column-level
- `distinct_count`: Cardinality for selectivity estimates
- `null_count`: NULL value count for IS NULL predicates
- `has_histogram`: Distribution data availability

**Interpretation:**
- First row (column_names = '{}') shows table-level row_count
- Subsequent rows show per-column statistics
- Missing columns indicate no statistics collected yet

### Query 3: Detect Row Count Drift

Compares current table row count against cached statistics to identify significant drift.

```sql
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:**
- Replace table name in both CTEs
- Adjust drift thresholds (30%, 20%, 10%) based on workload tolerance

**Key columns:**
- `drift_pct`: Percentage difference between current and cached row count
- `drift_status`: Classification for prioritization
- `stats_age`: Time since statistics last refreshed

**Interpretation:**
- **>30% drift**: Urgent refresh recommended, optimizer estimates likely very inaccurate
- **20-30% drift**: Consider refresh if experiencing performance issues
- **10-20% drift**: Monitor for trends, may trigger automatic collection soon
- **<10% drift**: Normal variance, no action needed

### Query 4: Identify Missing Column-Level Statistics

Finds table columns without statistics, focusing on columns frequently used in WHERE/JOIN clauses.

```sql
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:**
- Replace schema_name, table_name, and database_name with target table

**Interpretation:**
- Columns returned have no optimizer statistics
- Prioritize creating statistics for columns used in:
  - WHERE clause predicates (`WHERE user_id = 123`)
  - JOIN conditions (`JOIN orders ON users.id = orders.user_id`)
  - GROUP BY / ORDER BY expressions

**Action:** Generate CREATE STATISTICS commands (see Query 7)

### Query 5: Histogram Coverage Analysis

Identifies columns with/without histogram data for range query optimization.

```sql
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:**
- Replace database_name.schema_name.table_name

**Key columns:**
- `histogram_status`: Indicates distribution data availability
- `stats_age`: Time since histogram last updated

**Interpretation:**
- **Has histogram**: Optimizer can estimate range scan selectivity (BETWEEN, >, <)
- **Missing histogram**: Optimizer uses