auditing-table-statistics

テーブル統計情報の監査

統計情報の古さ、列カバレッジの不足、行数の乖離を監査し、古い、または不完全な統計情報に起因するクエリパフォーマンスの低下を診断します。SHOW STATISTICS を使用して、DB Console へのアクセスを必要とせず、テーブルレベルおよび列レベルの統計情報の鮮度を読み取り専用 SQL で分析します。

profiling-statement-fingerprints を補完するスキルです： このスキルではオプティマイザーの統計情報の問題を診断します。過去に遅かったクエリを特定するには、profiling-statement-fingerprints を参照してください。

前提条件

• 対象テーブルに対する何らかの権限を持つ SQL 接続
• 自動統計情報収集が有効であること（デフォルト）：sql.stats.automatic_collection.enabled = true

関連スキル： 過去のクエリ分析については profiling-statement-fingerprints、ライブトリアージについては triaging-live-sql-activity を参照してください。

基本概念

CockroachDB 固有のデフォルト設定：

• 行数が約20％変化すると自動収集がトリガーされます（sql.stats.automatic_collection.fraction_stale_rows）
• 自動収集ではインデックス列グループが対象になります（デフォルトで sql.stats.multi_column_collection.enabled = true の場合）。インデックスのない列に対するアドホックな複数列統計情報には、手動で CREATE STATISTICS を実行する必要があります
• 大規模なテーブル（>10M 行）では、自動収集が遅れる場合があります
• 統計情報の古さのしきい値：7日（OLTP）または30日（OLAP）を超えた場合、あるいは行数が20～30％乖離した場合は更新します

ワークロードに応じたガイダンスについては、references/statistics-thresholds.md を参照してください。

基本的な診断クエリ

クエリ 1：統計情報が古い、または不足しているテーブルを特定する

統計情報が古いテーブル、または統計情報がまったくないテーブルを、古さの順に表示します。

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;

カスタマイズ：

• database_name.* を特定のスキーマパターン（例：mydb.public.*）に置き換えます
• 古さのしきい値を調整します。OLTP では INTERVAL '7 days'、OLAP では '30 days' を指定します
• より多くのテーブルを表示するには LIMIT の値を大きくします

主な列：

• staleness_status：統計情報の鮮度を簡易的に分類します
• stats_age：最後に収集されてからの正確な経過時間
• row_count：最後に確認されたテーブルサイズ

クエリ 2：特定のテーブルの統計情報を監査する

テーブルレベルおよび列ごとの詳細を含む、単一テーブルのすべての統計情報を表示します。

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;

カスタマイズ：

• database_name.schema_name.table_name を完全修飾テーブル名に置き換えます

主な列：

• column_names：空の {} はテーブルレベル、要素が1つの場合は列レベルを示します
• distinct_count：選択性の推定に使われるカーディナリティ
• null_count：IS NULL 述語で使われる NULL 値の件数
• has_histogram：分布データの有無

解釈：

• 1行目（column_names = '{}'）はテーブルレベルの row_count を示します
• 以降の行は列ごとの統計情報を示します
• 列が表示されない場合、その列の統計情報はまだ収集されていません

クエリ 3：行数の乖離を検出する

テーブルの現在の行数とキャッシュされた統計情報を比較し、大きな乖離を特定します。

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;

カスタマイズ：

• 両方の CTE 内のテーブル名を置き換えます
• ワークロードの許容範囲に応じて、乖離のしきい値（30％、20％、10％）を調整します

主な列：

• drift_pct：現在の行数とキャッシュされた行数の差の割合
• drift_status：優先度付けのための分類
• stats_age：統計情報が最後に更新されてからの経過時間

解釈：

• 乖離が30％超：緊急の更新を推奨します。オプティマイザーの推定値が大幅に不正確になっている可能性があります
• 乖離が20～30％：パフォーマンスの問題が発生している場合は、更新を検討してください
• 乖離が10～20％：傾向を監視してください。まもなく自動収集がトリガーされる可能性があります
• 乖離が10％未満：通常の変動範囲です。対応は不要です

クエリ 4：列レベルの統計情報が不足している箇所を特定する

統計情報がないテーブル列を検索します。特に、WHERE／JOIN 句で頻繁に使用される列に焦点を当てます。

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;

カスタマイズ：

• schema_name、table_name、database_name を対象のテーブルに置き換えます

解釈：

• 返された列にはオプティマイザー統計情報がありません
• 次の用途で使用される列の統計情報作成を優先します：
  • WHERE 句の述語（WHERE user_id = 123）
  • JOIN 条件（JOIN orders ON users.id = orders.user_id）
  • GROUP BY／ORDER BY 式

アクション： CREATE STATISTICS コマンドを生成します（クエリ 7 を参照）

クエリ 5：ヒストグラムの網羅状況を分析する

範囲クエリの最適化に向けて、ヒストグラムデータがある列とない列を特定します。

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;

カスタマイズ：

• database_name.schema_name.table_name を置き換えます

主な列：

• histogram_status：分布データが利用可能かどうかを示します
• stats_age：ヒストグラムが最後に更新されてからの経過時間

解釈：

• ヒストグラムあり：オプティマイザーは範囲スキャンの選択率（BETWEEN、>、<）を推定できます
• ヒストグラムなし：オプティマイザーは一様分布を仮定するため、精度が低くなります
• 自動収集によってヒストグラムが作成されます。存在しない場合は、列が非常に新しいか、収集が無効になっている可能性があります

クエリ 6：複数列統計情報を検出する

相関する列について、既存の複数列（複合）統計情報を特定します。

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;

カスタマイズ：

• database_name.schema_name.table_name を置き換えます

主な列：

• column_names：相関する列の配列
• column_count：複合統計情報に含まれる列の数

解釈：

• 存在する：複数列統計情報があります。インデックス列グループについて自動収集されたものか、手動で作成されたものです
• 存在しない：複数列統計情報がまだありません。インデックスの対象ではない相関列（例：市区町村＋都道府県＋郵便番号）について、手動での作成が必要な場合があります
• よくある用途：インデックスの対象になっていない相関列に対する手動統計情報

複数列統計情報の作成パターンについては、references/create-statistics-examples.md を参照してください。

クエリ 7：CREATE STATISTICS の推奨コマンドを生成する

統計情報が古い、または不足しているテーブルについて、すぐに実行できる CREATE 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;

カスタマイズ：

• database_name.* をスキーマパターンに置き換えます
• 古さのしきい値である INTERVAL '7 days' を調整します
• 推奨項目を増やすには LIMIT の値を大きくします

出力：

• create_command：コピーしてすぐに実行できる SQL コマンド
• __auto__：列の自動選択を使用します（推奨されるデフォルト）

実行：

• 生成されたコマンドを実行前に確認します
• 大規模なテーブル（>10M rows）では、トラフィックの少ない時間帯に実行します
• ジョブの進行状況を監視します（ジョブの監視についてはクエリ 6 を参照）

よくあるワークフロー

ワークフロー 1：一括ロード後の統計情報監査

シナリオ： INSERT／COPY／IMPORT の一括処理後に、統計情報が最新であることを確認します。

手順：

1. 影響を受けたテーブルを特定します：

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

2. 行数のずれを確認します（クエリ 3）： 影響を受けたテーブルごとに、ずれを検出するクエリを実行します。

3. 更新コマンドを生成して実行します（クエリ 7）：

   CREATE STATISTICS __auto__ FROM schema_name.table_name;  -- From Query 7 output
   

4. 収集ジョブを監視します：

   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;
   

5. 更新を検証します（クエリ 2）： 統計情報の監査を再実行し、created のタイムスタンプが更新されたことを確認します。

想定される結果： 統計情報の経過時間が 1 時間未満で、drift_pct が 5% 未満です。

ワークフロー 2：予期しないクエリプランの変更を診断する

シナリオ： クエリのパフォーマンスが突然低下し、EXPLAIN で異なるプランが表示されます。

手順：

1. profiling-statement-fingerprints から影響を受けたクエリを特定します： レイテンシーが急増したクエリ、またはプランハッシュが変更されたクエリを探します。

2. テーブル参照を抽出します： クエリテキストを解析し、FROM/JOIN 句のテーブルを特定します。

3. 各テーブルの統計情報を監査します（クエリ 2）： 統計情報が古くなっていないか、行数が最新かを確認します。

4. 過去と現在の行数を比較します：

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

5. 古くなった統計情報を更新します（クエリ 7）： 乖離が大きいテーブルに対して CREATE STATISTICS を実行します。

6. プランの安定性を検証します： EXPLAIN を再実行し、プランが想定どおりの構造に戻ったことを確認します。

期待される結果： プランハッシュが安定し、統計情報の更新後にレイテンシーがベースラインに戻ります。

ワークフロー 3：定期的な統計情報の健全性チェック

シナリオ： パフォーマンスが低下する前に統計情報の問題を先回りして特定するための定期監査です。

手順：

1. クラスター全体の古さをスキャンします（クエリ 1）：

   -- All databases
   SHOW STATISTICS FOR TABLE *.*;  -- Warning: May be slow on large clusters
   

2. 重要なテーブルを優先します： profiling-statement-fingerprints にある高トラフィックのテーブルに注目します。

3. 自動収集が有効になっていることを確認します：

   SHOW CLUSTER SETTING sql.stats.automatic_collection.enabled;  -- Should be true
   

4. 保留中の自動収集ジョブを確認します：

   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;
   

5. 一括更新スクリプトを生成します（クエリ 7）： 出力をファイルに保存し、スケジュール実行します。

6. メンテナンス時間帯に更新をスケジュールします： 生成された CREATE STATISTICS コマンドを、トラフィックの少ない時間帯に実行します。

頻度： OLTP は毎週、OLAP は毎月です。

安全上の考慮事項

本番環境で安全に実行できる操作

SHOW STATISTICS：

• 影響： 読み取り専用で、クラスターへの影響はありません
• 本番環境で安全に実行可能： はい。制限なくいつでも実行できます

CREATE STATISTICS：

• 影響： CPU／I/O 負荷が高く、テーブルスキャンはブロックされません
• 安全性：
  • テーブルをロックせず、書き込みもブロックしません
  • リソース（CPU、ネットワーク、ディスク I/O）を消費します
  • 大きなテーブルでは、収集中にクエリのパフォーマンスに影響する場合があります
• ベストプラクティス：
  • 1,000 万行を超えるテーブルの場合、トラフィックの少ない時間帯に実行します
  • 実行を分散させます（多数のテーブルの統計情報を同時に作成するのは避けてください）
  • ジョブの進捗状況とリソース使用率を監視します

リソース消費量

小規模なテーブル（1 万行未満）： 影響は無視できる程度で、いつでも安全に実行できます

中規模なテーブル（1 万～1,000 万行）： 数秒から数分で完了し、影響は軽微です

大規模なテーブル（1,000 万行超）： 数分から数時間かかるため、計画的に実行してください：

• メンテナンス時間帯にスケジュールします
• 収集中にクラスターのメトリクス（CPU、ディスク I/O）を監視します
• SHOW JOBS を使用して進捗状況を追跡します

キャンセルする場合：

-- 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;

一括収集のベストプラクティス

クラスターに過剰な負荷をかけないようにします：

• 統計情報を同時に収集するテーブルは、最大 3～5 個にします
• 次のバッチを開始する前に、処理が完了するまで待ちます
• バッチ間でクラスターの健全性メトリクスを監視します

実行を分散させるスクリプトの例：

# 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

一括処理の詳細なパターンについては、references/create-statistics-examples.md を参照してください。

トラブルシューティング

問題	考えられる原因	対処方法
SHOW STATISTICS の結果が空	統計情報が一度も収集されていない	CREATE STATISTICS __auto__ FROM table_name; を実行します
空ではないテーブルの row_count が 0 と表示される	統計情報が同期されていない	CREATE STATISTICS __auto__ FROM table_name; で更新します
権限拒否エラー	テーブルに対する権限がない	任意の権限を付与します：GRANT SELECT ON table_name TO user;
CREATE STATISTICS ジョブが停止している	書き込み量が多い大きなテーブル	SHOW JOBS のステータスを確認します。CANCEL JOB を実行して、トラフィックの少ない時間帯に再試行することを検討します
自動収集が実行されない	設定が無効、またはしきい値に達していない	sql.stats.automatic_collection.enabled = true を確認し、行数の乖離を確認します
統計情報はあるが、クエリプランの品質が依然として低い	統計情報が古い、または複数列統計情報がない	既存の統計情報を更新します。相関する列には複数列統計情報を作成します（クエリ 6 を参照）
作成日時が最近なのに乖離が大きい	収集の合間に書き込み量が極端に多い	自動収集のしきい値を下げるか、手動更新の頻度を上げます

防御的なクエリパターン

統計情報がない場合に対処する：

-- 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 = '{}';

乖離の計算でゼロ除算を避ける：

-- Use NULLIF to prevent divide-by-zero errors
SELECT
  ABS(actual - stats)::NUMERIC / NULLIF(stats, 0) * 100 AS drift_pct
FROM ...;

主な考慮事項

• 自動収集と手動収集： ベースラインとして自動収集を有効にしておきます。一括ロード後の臨時更新や重要なテーブルには、手動で CREATE STATISTICS を使用します
• 複数列統計情報： 自動収集ではインデックスの列グループが対象になります。相関するインデックス対象外の列をまとめてクエリする場合（例：CREATE STATISTICS city_state_stats ON city, state FROM addresses;）、手動で CREATE STATISTICS を実行する必要があります
• 大きなテーブル（>10M 行）： メンテナンス時間帯に CREATE STATISTICS を実行するようスケジュールし、SHOW JOBS WHERE job_type = 'CREATE STATS' で監視します
• 古さの調整： OLTP：3～7 日、OLAP：14～30 日、ハイブリッド：重要なテーブルは 3～7 日、アーカイブは 30 日以上
• 権限： テーブルに対する任意の権限（SELECT、INSERT など）があれば、統計情報を参照できます

詳しいガイダンスについては、references/create-statistics-examples.md と references/statistics-thresholds.md を参照してください。

参考資料

CockroachDB の公式ドキュメント：

• SHOW STATISTICS - 構文と出力スキーマの詳細
• CREATE STATISTICS - 統計情報を手動で収集するガイド
• Cost-Based Optimizer - オプティマイザーによる統計情報の利用方法
• Table Statistics - クエリ計画に対する統計情報の影響
• SHOW JOBS - ジョブの監視と管理

関連スキル：

• profiling-statement-fingerprints - 低速なクエリパターンを特定する
• triaging-live-sql-activity - リアルタイムでクエリをトリアージする

補足資料：

• Statistics Thresholds Guide - ワークロードに応じた古さと乖離のしきい値
• CREATE STATISTICS Examples - 収集パターンとバッチ戦略の詳細