BigQueryクエリのパフォーマンスを分析し、2倍以上の性能改善を目標とした最適化を提案します。
BigQueryクエリを実行してボトルネックを分析し、2倍以上の性能改善を目指す最適化を提案します。実行結果に基づき、具体的な改善手法を提案・検証します。
/plugin marketplace add syou6162/claude-code-commands/plugin install syou6162-plugin@syou6162-marketplacegcloud config get-value projectで設定済み)FROM句にバッククオートの付与は禁止
.sql)前提: $ARGUMENTSは最適化対象のSQLクエリが記述された.sqlファイルのパスです
cat "$ARGUMENTS" | bq query --nosync --use_legacy_sql=false --use_cache=false --format=json | jq -r '.jobReference.jobId'JOB_IDとして以降の分析で使用JOB_IDをシェル変数として設定する必要はありませんbq wait "<JOB_ID>"でジョブが完了するまで待機bq query --use_legacy_sql=false --format=json --parameter="job_id:STRING:<JOB_ID>" "
SELECT query, total_slot_ms, total_bytes_processed
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = @job_id AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
" | tee /tmp/job_info.json | head -n 10
cat /tmp/job_info.json | jq -r '.[0].query' | tee /tmp/original_query.sql目的: 全体のスロット時間の80%以上を占める真のボトルネックを見つける
bq query --use_legacy_sql=false --format=pretty --parameter="job_id:STRING:<JOB_ID>" "
SELECT
stage.name as stage_name,
CAST(stage.slot_ms AS INT64) as slot_ms,
ROUND(100.0 * stage.slot_ms / SUM(stage.slot_ms) OVER(), 1) as pct_of_total
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
UNNEST(job_stages) AS stage
WHERE job_id = @job_id
AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY stage.slot_ms DESC
LIMIT 5
"
この結果から上位1-3ステージ(合計80%以上)を特定し、以降はそれらのみを分析対象とする
前提: セクション2で特定したボトルネックステージ(TOP1-3)のみを詳細分析する
bq query --use_legacy_sql=false --format=pretty --parameter="job_id:STRING:<JOB_ID>" "
SELECT
stage.name,
CAST(stage.slot_ms AS INT64) as slot_ms,
ROUND(stage.wait_ratio_max * 100, 1) as wait_pct,
ROUND(stage.read_ratio_max * 100, 1) as read_pct,
ROUND(stage.compute_ratio_max * 100, 1) as compute_pct,
ROUND(stage.write_ratio_max * 100, 1) as write_pct,
ROUND(CAST(stage.shuffle_output_bytes AS INT64) / 1048576, 1) as shuffle_mb
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
UNNEST(job_stages) AS stage
WHERE job_id = @job_id
AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
AND stage.name IN ('特定されたボトルネックステージ名1', '名前2', '名前3')
ORDER BY stage.slot_ms DESC
"
bq query --use_legacy_sql=false --format=pretty --parameter="job_id:STRING:<JOB_ID>" "
SELECT
stage.name,
CASE
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'READ') THEN 'READ処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'WRITE') THEN 'WRITE処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'COMPUTE') THEN 'COMPUTE処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'FILTER') THEN 'FILTER処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'JOIN') THEN 'JOIN処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'AGGREGATE') THEN 'AGGREGATE処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'ANALYTIC') THEN 'ANALYTIC処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'SORT') THEN 'SORT処理'
WHEN EXISTS(SELECT 1 FROM UNNEST(stage.steps) AS step WHERE step.kind = 'LIMIT') THEN 'LIMIT処理'
ELSE 'その他'
END as primary_operation
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT,
UNNEST(job_stages) AS stage
WHERE job_id = @job_id
AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
ORDER BY stage.slot_ms DESC
LIMIT 5
"
cat "$ARGUMENTS"で元クエリの内容を表示/tmp/bottleneck_analysis.mdを以下の内容で作成:## 特定されたボトルネックステージ:
<!-- 例: - S02_Join+: 99000ms (全体の45%) -->
<!-- 例: - S03_Aggregate: 55000ms (全体の25%) -->
## 根本原因分析:
<!-- 例: - S02_Join+の支配的要素: Write (shuffle_mb: 151MB), wait_pct: 5%, compute_pct: 35% -->
<!-- 例: - S03_Aggregateの支配的要素: Compute (compute_pct: 80%), read_pct: 15% -->
## 対応するSQL箇所:
<!-- 例: - S02_Join+に対応: INNER JOIN users u ON c.user_id = u.id (行11-12) -->
<!-- 例: - S03_Aggregateに対応: GROUP BY category, date (行18) -->
特定されたボトルネック要因に基づいて、2倍改善を狙える最適化パターンを選択
最適化パターンの選択と記録:
/tmp/bottleneck_analysis.mdの内容を確認/tmp/applied_optimizations.mdを以下の内容で作成:## 適用する最適化パターン
<!-- 例: 1. JOIN前データ削減: users テーブルの事前フィルタリング (期待効果: 60%削減) -->
<!-- 例: 2. JOIN順序最適化: 小テーブル first (期待効果: 25%削減) -->
cat "$ARGUMENTS"で元クエリの内容を確認/tmp/optimized_query.sqlに最適化後のクエリを保存-- 例: JOIN前データ削減
WITH filtered_users AS (
SELECT * FROM users WHERE active = true
)
SELECT ...
FROM comments c
INNER JOIN filtered_users u ON c.user_id = u.id
cat /tmp/optimized_query.sqlで保存した最適化クエリを表示BIT_XORとFARM_FINGERPRINTは行の順序に依存しないため、結果の同一性を検証できるBigQueryチェックサムによる検証手順:
JOB_ID(セクション1で取得済み)から以下のコマンドを実行し、DESTINATION_TABLEとして設定
bq query --use_legacy_sql=false --format=json --parameter="job_id:STRING:<JOB_ID>" "SELECT destination_table FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE job_id = @job_id AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)" | jq -r '.[0].destination_table | [.project_id, .dataset_id, .table_id] | join(".")'DESTINATION_TABLEに対して、チェックサムを計算
bq query --use_legacy_sql=false --format=json "SELECT BIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(t))) as checksum FROM <DESTINATION_TABLE> AS t" | jq -r '.[0].checksum'NEW_JOB_ID(セクション6で取得)から以下のコマンドを実行し、DESTINATION_TABLEとして設定
bq query --use_legacy_sql=false --format=json --parameter="job_id:STRING:<NEW_JOB_ID>" "SELECT destination_table FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT WHERE job_id = @job_id AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)" | jq -r '.[0].destination_table | [.project_id, .dataset_id, .table_id] | join(".")'DESTINATION_TABLEに対して、チェックサムを計算
bq query --use_legacy_sql=false --format=json "SELECT BIT_XOR(FARM_FINGERPRINT(TO_JSON_STRING(t))) as checksum FROM <DESTINATION_TABLE> AS t" | jq -r '.[0].checksum'cat /tmp/optimized_query.sql | bq query --nosync --use_legacy_sql=false --use_cache=false --format=json | jq -r '.jobReference.jobId'NEW_JOB_IDとして以降の分析で使用NEW_JOB_IDをシェル変数として設定する必要はありませんbq wait "<NEW_JOB_ID>"でジョブが完了するまで待機cat /tmp/job_info.json | jq -r '.[0].total_slot_ms'bq query --use_legacy_sql=false --format=json --parameter="job_id:STRING:<NEW_JOB_ID>" "
SELECT total_slot_ms
FROM region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id = @job_id AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
" | jq -r '.[0].total_slot_ms'
bcコマンドなどは使用せず、Agent自身が計算を行う目的: 2倍改善達成の根拠と再現可能な手順を記録
cat /tmp/job_info.json | jq -r '.[0].total_slot_ms'で取得/tmp/optimization_report.mdを以下の構造で作成:# BigQuery最適化レポート
## 実行サマリー
- **元クエリファイル**: <$ARGUMENTSの値>
- **元ジョブID**: <JOB_ID>
- **元スロット時間**: <ORIGINAL_SLOT_MS>ms
- **最終改善率**: <IMPROVEMENT_RATIO>x
- **目標達成**: <達成状況(✅ 達成 or ❌ 未達成)>
## 特定されたボトルネック
<bottleneck_analysis.mdの内容をReadツールで読み込んで転記>
## 適用した最適化手法
<applied_optimizations.mdの内容をReadツールで読み込んで転記>
## 最適化後のクエリ
\`\`\`sql
<optimized_query.sqlの内容をReadツールで読み込んで転記>
\`\`\`
## 検証結果
- **結果一致**: ✅ チェックサム一致で完全同一
- **性能改善**: <ORIGINAL_SLOT_MS>ms → <NEW_SLOT_MS>ms
cat /tmp/optimization_report.mdでレポート内容を表示完了条件: