generating-clickhouse-query-performance-reports

ClickHouse クエリパフォーマンスレポートの生成

このスキルは、遅い ClickHouse クエリを調査し、パフォーマンスレポートを作成するための 方法論 です。これは querying-production-databases-via-metabase と対をなしており、そちらが 仕組み（SSO で保護された認証と hogli metabase:query）です。このスキル内のすべてのクエリは、そちらを通して実行してください。

レポート自体は公開されていません。存在する場合、プライベートな PostHog/query-performance-analysis リポジトリに過去のレポートとサンプルのクエリ ID が保持されており、このリポジトリにはツールと方法論のみが含まれています。そのリポジトリは通常、posthog のチェックアウトの 兄弟フォルダ としてチェックアウトされます（たとえば、リポジトリルートからの ../query-performance-analysis、または同じ親ディレクトリの下に並んで配置されます）。日付付きレポートを格納した analysis/ フォルダを含む、query-performance-analysis という名前の兄弟ディレクトリを探してください。見つかった場合は、新しいレポートを analysis/ の下に新しい markdown ファイルとしてそこに追加 し、<YYYY-MM-DD>-<topic>.md という名前を付けます（既存の命名に合わせてください。例: 2026-05-27-slow-queries-14d.md）。

兄弟リポジトリが存在しないこともありますが、それで問題ありません。 見つからない場合は、公開されている posthog リポジトリに書き込まないでください。また、そこでブロックしないでください。代わりにレポートを一時フォルダに書き込み（例: /tmp/<YYYY-MM-DD>-<topic>.md）、どこに置いたかをユーザーに伝え、手順 9 の前回レポートとの比較をスキップしてください（比較対象となる履歴がありません）。

データソース: posthog.query_log_archive (system.query_log ではありません)

本番クラスタの system.query_log は数時間分しか保持しないため、複数日にわたる質問には答えられません。代わりに分散アーカイブテーブルを使用してください:

FROM posthog.query_log_archive

これは約3週間分を保持し、log_comment を型付きカラムとして公開しているため、JSONExtract をスキップできます。直接クエリしてください(すでにクラスタ全体にファンアウトします)。分散サブクエリが二重にカウントされないよう、常に is_initial_query でフィルタリングしてください。ウィンドウを信頼する前に、日ごとの count() で現在の保持期間を確認してください(references/query-patterns.md を参照)。

主要なカラム(完全なリストは system.columns WHERE table='query_log_archive' で取得):

Column	Meaning
team_id (Int64)	テナント。0 / 空は内部または帰属不明を意味します。
lc_kind	クエリの発行方法: request (同期 API/ウェブ)、celery (非同期リフレッシュ)、temporal、cohort_calculation、dagster。
lc_product	product_analytics、warehouse、experiments、messaging、web_analytics、replay、llm_analytics、cohorts など。
lc_access_method	personal_api_key、oauth、sharing_token、または空 (ログイン済みウェブ)。
lc_query__kind	プロダクトのクエリ種別: TrendsQuery、FunnelsQuery、RetentionQuery、HogQLQuery など。
lc_workload	Workload.OFFLINE / ONLINE。
lc_feature、lc_temporal__workflow_type、lc_route_id、lc_api_key_label	帰属のための発行元の詳細。
lc_dashboard_id、lc_insight_id、lc_experiment_id、lc_cohort_id	クエリをそれをトリガーしたオブジェクトに紐付けます。
query、query_duration_ms、read_bytes、read_rows、memory_usage、exception_code	クエリとそのコスト。

両リージョンにアーカイブがあります。US と EU は異なるワークロードとマテリアライズドカラムを持つ別々のクラスタであるため、クロスリージョンの比較は両方に対して実行してください。リージョンごとの現在の ClickHouse データベース id は hogli metabase:databases で確認してください(id は安定していません)。あるリージョンの ONLINE と OFFLINE の Metabase 接続は同じ論理クラスタにファンアウトするため、同じ query_log_archive データを返すことに注意してください。

何がスロークエリと見なされるか

query_duration_ms > 30000 OR exception_code IN (159, 160, 241)

コード	意味
159	TIMEOUT_EXCEEDED
160	TOO_SLOW
241	MEMORY_LIMIT_EXCEEDED

type = 'QueryFinish' を追加してはいけません。OOM とタイムアウトの行は type = 'ExceptionWhileProcessing' であるため、 このフィルターはすべての失敗を暗黙的に除外してしまいます。duration/exception の述語はすでに QueryStart の行（duration 0）を除外しています。クラスタヘルスポーリングのクエリは normalized_query_hash で除外してください （パターンは references/query-patterns.md にあります）。

レポートの作成

標準的なワークフローは、粗い粒度から具体的な粒度へと進みます。各ステップの SQL は references/query-patterns.md にあります。

ステップ 9 まで過去のレポートを読まないでください。 ステップ 1〜8 は生データに対して新鮮な目で実行するべきです。 そうすることで、前回のレポートの調査結果をなぞるのではなく、分析がより広い範囲を捉えることができます。過去のレポートを早い段階で読むと、 そのカテゴリに引きずられてしまい、そこに言及されていない新しい問題を見逃しやすくなります。独立したパスが完了してから初めて、履歴と差分を取ってください。

1. ウィンドウを確認します。 対象範囲に対して日ごとの count() を実行し、アーカイブが実際にその範囲をカバーしていることを検証します（保持期間は想定より短いことがあります）。
2. 全体サマリー。 スロークエリの合計、クラスターのクエリ時間の合計、読み取りバイト数、影響を受けたチーム、および成功したが遅い / タイムアウト / OOM / その他の内訳を把握します。さらに、すべてのクエリにわたるクラスター全体の合計（スローセットだけでなく）も記録します。合計クエリ秒数、合計 CPU 秒数（Map 参照ではなく、型付きの ProfileEvents_OSCPUVirtualTimeMicroseconds カラム）、合計読み取りバイト数、合計 OOM 数です（references/query-patterns.md §1b）。スローセットの合計は偏ったサブセットです。全クエリの合計こそが、「今期はより忙しいか / より多く読み取っているか？」に対する正直な分母であり、今後のレポートが差分を取る際のベースラインです。ウィンドウが保持期間を過ぎて古くなると、これらはバックフィルできません。そのため毎回の実行で記録してください。
3. 日付分布。 日ごとのスロー件数、タイムアウト、OOM 数です。ここでインシデントが自らを明らかにします。平坦なベースラインに対する複数日にわたる OOM やタイムアウトの急増です。
4. 分類。 lc_kind × lc_product × lc_access_method でグループ化します。これによりバックグラウンド処理（データモデリング、dagster の事前集計、バッチエクスポート）と同期的なユーザー向けクエリを分離できます。
5. 帰属を特定。 最悪のカテゴリを team_id で掘り下げます。合計クラスター時間（sum(query_duration_ms)）と OOM 数で別々にランク付けします。何かをシステム全体の問題と呼ぶ前に、1 つのチームまたは 1 つの API キーが指標を支配していないか確認します。personal_api_key 経由でクエリを実行する単一の連携がクラスター OOM の大部分を占めることがあり、その場合の「インシデント」は実質的に 1 つのテナントです。まず team_id + lc_api_key_label で帰属を特定します。次に、すべてのクエリにわたるトップコンシューマービュー（スローセットだけでなく）を追加します。バイト数、CPU 秒数、ウォールタイムでランク付けしたトップチーム、トップ API キー（lc_api_key_label）、トップツール（lc_product）です（references/query-patterns.md §4c）。ここで重いが速いコンシューマーが現れます。あるテナントや連携が、数百万の安価なクエリを通じてクラスターの CPU やバイト数を支配しながら、スローのしきい値を一度も超えないことがあり、そのためスローセットのランキングでは見えません。行ごとの CPU:ウォール比は、コンピュートバウンドな負荷と待機/IO バウンドな負荷を区別します。
6. ユーザー向けの遅さを特徴づけます。 lc_access_method が空（ログイン済み Web）の lc_kind='request' AND lc_product='product_analytics' について、lc_query__kind で内訳を出し、breakdown_value の使用と person_properties に対する JSONExtract にフラグを立てます。これが製品として対応可能なバケットです。常に JSON から抽出したプロパティの内訳（references/query-patterns.md §7）を含めます。スローセット内の JSON ブロブから取得したトップのイベントおよび person プロパティ名と、それぞれをどのチームが使用しているかです。これらはマテリアライゼーションの候補であり、必須のレポート出力です。HogQLQuery（ユーザーおよび AI が作成した任意の SQL）は独自の深掘りに値し、そのうちどれだけが AI によって書かれたか、なぜ遅いのかを含みます。references/hogql-deep-dive.md を参照してください。
7. 最悪の違反者の根本原因を特定。 上位の所見について、「チーム X が遅い」で止めてはいけません。完全なクエリを取得し、なぜ なのかの仮説を立てて、EXPLAIN で検証します。個々のクエリの根本原因特定は optimizing-clickhouse-and-hogql-queries スキルの役割です。その references/investigation-playbook.md がプレイブックです（完全なクエリの取得、バイト数 vs CPU vs 実行時間、実行時の原因、オリジンの追跡、EXPLAIN）。有用な所見には理由が含まれます（「時間フィルターが関数でラップされていてグラニュールをプルーニングできないため、全履歴をスキャンする」など）。たとえ仮説として述べる場合でもです。
8. 例 + 書き上げ。 各所見の最悪の違反者について query_id + event_date を記録し、レポートを書きます（構成は後述）。system.query_log の保持期間は短いため、例は古い Metabase ルックアップカードではなく query_log_archive（WHERE query_id = '…' AND event_date = '…'）から解決します。各例を共有可能な自己完結型の Metabase URL（references/query-patterns.md の query_link レシピ）にリンクし、読者がクエリへ直接クリックして移動できるようにします。推奨事項を起草する際は、バックグラウンドのリサーチエージェントを起動して 調査可能なものをコードに基づかせ（後述の「推奨事項をコードに基づかせる」を参照）、「X を監査する」と言うのではなく、推奨事項が実際のファイルと変更を指すようにします。
9. 前回のレポートと差分を取る（存在する場合は最後にこれを行います）。 兄弟リポジトリの query-performance-analysis が存在しない場合は、このステップを完全にスキップします。存在する場合は、ここに至って初めて、上記の独立したパスの後に、その analysis/ フォルダ内で最も新しい日付のレポートを読みます（ファイル名の日付でソート）。新しいレポートに短い デルタ セクションを追加し、以下を扱います。前回以降に動いたもの（新しいインシデント、拡大または解消した所見、全体数値の増減）、および前回のレポートが対応が必要としてフラグを立てたもの（推奨されたマテリアライゼーション、注視すべきチーム、インクリメンタル化すべきパイプライン）に対する フォローアップチェック。前回の各フォローアップについて、解消済み、未対応のまま、または悪化のいずれであるかを、現在の数値を根拠として述べます。これを最後に行うのは意図的です。新鮮な分析を偏らせずに保ちつつ、履歴に対してループを閉じることができます。デルタを引用する前にウィンドウを比較可能にします。 前回のレポートが同じウィンドウ長を使用していたことを確認します（ここでの両レポートは直近の now() - INTERVAL N DAY を使用するため、長さは等しいものの、端の日が重複したり部分的だったりします）。2 つの直近ウィンドウ間の全体合計は、通常、一方のウィンドウには入り他方には入らない単発のインシデントによって支配されるため、大きな減少が構造的な改善であることはまれです。デルタを誤読しないよう、常に インシデント除外ベースライン（例: 急増日を除いた 1 日あたりの OOM 数）も比較し、合計が動いた理由がインシデントのウィンドウへの出入りである場合は明示的に述べます。合計指標（読み取りバイト数、クラスター時間）は スローセットのみ を対象とし、クラスター全体の I/O ではないことを忘れないでください。そのため、重いバックグラウンドジョブの実行が 30 秒のしきい値を超えたり超えなくなったりすることでも動きます。大きなバイト数/時間の変動は、クラスター全体の変化として報告するのではなく、特定のカテゴリ（通常は 1 つか 2 つのバックグラウンドパイプライン）に帰属させてください。

コードに基づく根拠づけの推奨事項

「パイプライン X を監査する」とか「プロパティ Y をマテリアライズする」といった推奨事項は、実際のコードを指し示しているときに、はるかに有用になります。PostHog のコードベース内の具体的な箇所に対応する各推奨事項について、バックグラウンド調査エージェントを起動し（Agent ツール、run_in_background: true、subagent_type: general-purpose または Explore）、ソースを読んで次を返させます。すなわち、関連するコードが現在どのように動作しているか、変更すべき具体的なファイル / 関数、あらゆる制約、そしてより優れたメカニズムがすでに存在するかどうかです。調査可能な推奨事項ごとに 1 つのエージェントを、推奨事項を起草したらすぐに、すべて 1 つのメッセージで起動して並行して実行させます。あなたが差分（ステップ 9）を行い、最終的なレポートをまとめている間にそれらを走らせておき、その後で各調査結果を対応する推奨事項に織り込みます。「X を監査する」を「X は <file> に <current behavior> として実装されています。変更点は <specific> です」に置き換え、人間がすぐに該当箇所へ飛べるようにファイルパスを引用します。エージェントは調査と報告のみを行い、コードは変更しません。

すべての推奨事項がこの方法で調査できるわけではありません。ソースコードが信頼できる情報源である場合にのみエージェントを起動し、運用 / インフラに関する項目はスキップします。

推奨事項の形	調査可能か?	エージェントが読むもの
遅いインサイト / クエリの形を書き換える	はい	posthog/hogql_queries/ 配下のクエリランナー、それが生成する HogQL
プロパティ X をマテリアライズする	はい	マテリアライズドカラムのレジストリ（ee/clickhouse/materialized_columns/）
パイプライン Y をインクリメンタルにする	はい	それを構築する dagster / temporal ジョブ
キーごとにメモリを制限する / クエリガードを追加する	はい	ClickHouse の SETTINGS とキーごとのスロットリングが適用される箇所
内訳のカーディナリティガードを追加する	はい	trends / 内訳のクエリランナー
インフラ障害の時間帯を調査する	いいえ	該当なし（デプロイ、ノードの健全性、クラスタの状態）
テナントの想定負荷を監視 / 確認する	いいえ	該当なし（人間による判断）

各エージェントには焦点を絞ったプロンプトを与えます。すなわち、推奨事項、具体的な質問、そしてファイルパス + 現在の動作 + 正確な変更箇所を返し、何も変更しないという指示です。エージェントは posthog リポジトリ（このスキルが置かれている場所）を読みます。レポート自体は別の query-performance-analysis リポジトリに書き込まれます。

結果の解釈

• 「遅いクエリ」には 2 つの集団が存在します。 タイトなタイムアウトによる API ノイズ（低い max_execution_time に対して約 10 秒でエラーになるクエリ、通常は personal_api_key）は、実際の計算を表すことなく生のカウントを膨らませます。本当に高コストな処理は、総クラスタ時間と OOM 数で測るほうが適切です。常にこの区別を指摘してください。タイムアウトの量を遅さの代名詞にしないでください。
• 読み取りバイト数が最も真実に近いコストシグナルであり、所要時間（キャッシュやクラスタ負荷によって変動します）よりも重要です。少ない行数に対して高いバイト数は、重いカラム、ほとんどの場合は properties ブロブに対する JSONExtract を意味します。個々のクエリの根本原因を特定するには、optimizing-clickhouse-and-hogql-queries スキルを参照してください。
• バックグラウンドパイプラインは通常、生のクラスタ時間を支配します（データモデリングの DAG、ウェブ分析の事前集計）。これは想定内です。ユーザー向けのレイテンシとは別に、そのスキャン量が必要かどうかで重みづけして評価してください。

レポート構成

レポートには、次の内容をこの順序で含める必要があります。

1. 一行のスコープ: 対象リージョン、対象期間、および使用した slow の定義 / 除外条件。
2. ヘッドライン数値の表 + クラスタ全体の合計 (すべてのクエリ) の表 (合計クエリ秒数、 CPU 秒数、読み取りバイト数、OOM 数) + 2 つの母集団に関する注意書き。
3. 日次分布の表 (インシデント期間があればフラグを立てる)。
4. 所見を、最も深刻なものから順に記載します。すべての所見には、少なくとも 1 つの具体的な query_id + event_date を、共有可能な query_link URL 経由でリンクする必要があります (references/query-patterns.md を参照)。これにより、読者は該当するクエリへ直接クリックして移動できます。加えて、なぜ遅いのかに関する仮説 (optimizing-clickhouse-and-hogql-queries スキルの調査プレイブックに基づく) も記載します。所見は、その性質ごとにグループ化します。すなわち、特定テナントのインシデント、クラスタ時間の最も重い消費元、ユーザー向けインサイトの遅さ、タイトなタイムアウトの API ノイズです。
5. リソース別のトップ消費元セクション (slow だけでなくすべてのクエリ): トップチーム、トップ API キー、およびトップツール (lc_product) を、バイト数 / CPU / ウォールタイム順でランク付けします (references/query-patterns.md §4c)。slow しきい値に決して引っかからない消費元や、コンピュートバウンドとウェイトバウンドの内訳を明示します。
6. JSON から抽出したプロパティの表: slow セット内の JSON ブロブから取得した、上位のイベントプロパティ名と人物プロパティ名を、それぞれを使用しているチームとともに示します (references/query-patterns.md §7)。これらがマテリアライズの候補です。
7. 各所見に対応する具体的な推奨事項 (プロパティ X をマテリアライズする、API キーごとにメモリを上限設定する、パイプライン Y を増分化する、など)。調査可能なものはコードに根拠を置きます (「コードに根拠を置く推奨事項」を参照): ファイル / 関数と具体的な変更を引用し、単に「X を監査する」とだけ書かないでください。
8. 前回のレポートとの差分 (ステップ 9): 前回から何が変わったか、および前回のレポートが推奨した各アクションに対するフォローアップ確認 (解決済み / 未解決のまま / 退行、数値付き)。前回のレポートが存在しない場合は、このセクションを省略します。

完成したレポートは、兄弟リポジトリである query-performance-analysis 内の analysis/<YYYY-MM-DD>-<topic>.md として保存し、公開されている posthog リポジトリには決して保存しないでください。そのリポジトリが存在しない場合は、一時フォルダ (例: /tmp/<YYYY-MM-DD>-<topic>.md) に保存し、ユーザーにそのパスを伝えてください。

参照

• references/query-patterns.md: 上記の各ステップに対応する、すぐに実行できる SQL を、query_log_archive に対して実行します。
• references/materialization-analysis.md: マテリアライズすべきプロパティと削除すべきカラムを見つけ、US と EU の両方で実行します。
• references/hogql-deep-dive.md: HogQLQuery (任意のユーザー / AI による SQL) を特に対象とした分析。AI が書いた HogQL を特定する方法 (ai_query_source ではなく lc_product/lc_feature) や、アドホッククエリや AI クエリが遅くなる原因も含みます。

関連スキル

このスキルはフリートレベルです: すべてのチームにわたって slow クエリを見つけてランク付けし、レポートを作成します。所見が、説明または修正したい 1 つのクエリを指し示したら、optimizing-clickhouse-and-hogql-queries に切り替えてください。このスキルは、個々のクエリの根本原因の特定 (references/investigation-playbook.md) と、適切なレイヤー (プリンタ、クエリランナー、または ClickHouse マイグレーション) での修正の適用を担当します。