optimizing-clickhouse-and-hogql-queries

ClickHouse と HogQL クエリの最適化

ClickHouse と HogQL クエリ (HogQL は ClickHouse にコンパイルされます) を最適化します。Postgres / Django ORM ではありません。アプリ DB のクエリ (Model.objects.filter(...)) の場合は、いったん止まって profiling-slow-api-endpoints を使用してください。Step 0 にトリアージの全体像があります。

HogQL ではなく ClickHouse SQL を起点に作業してください。 クエリが生成する ClickHouse SQL を取得し、それを最適化してから、その変更を HogQL クエリ、クエリランナー、プリンター、またはマイグレーションに逆変換します。HogQL だけを対象に考えると、ClickHouse が実際に実行する内容が見えなくなります。

HogQL を書けることを前提としています。新しいクエリをゼロから書く場合は /writing-clickhouse-queries を、マイグレーションの仕組みについては /clickhouse-migrations を使用してください。

チームが所有するすべてのクエリを最適化する

「チーム X のクエリをすべて最適化する」という作業では、まず完全なインベントリを作成しないと、いくつかを見落とします。

1. /establishing-code-ownership でチームをその所有パスに解決します。 所有パスはバックエンドの Python および frontend/src/... にまたがり、多くの場合複数のプロダクトにわたります。無言で 1 つのプロダクトやバックエンドだけに絞り込まないでください。
2. すべてのパスにわたってクエリを見つけます。 ほとんどは Python (*QueryRunner、execute_hogql_query、生の sync_execute) ですが、かなりの数がクライアント側で構築され /query に POST されます。frontend/ で次を grep します:
   • api.queryHogQL(...)、HogQLQueryString、hogql`...` テンプレート
   • NodeKind.HogQLQuery / kind: 'HogQLQuery' と query: 文字列の組み合わせ
   • ClickHouse にコンパイルされるノード: DataTableNode、EventsQuery、TrendsQuery/InsightVizNode、PropertyFilterType.HogQL
   • SELECT ... FROM events を含むリテラル、またはプロダクトのマーカー ('survey sent'、$survey_id)

同じクエリが二重に実装されていることがあります (バックエンドのランナーとフロントエンドの HogQL、多くの場合停滞したマイグレーション)。どちらも対象です。バックエンドのプリンター/関数の修正では、手作業で構築されたフロントエンドの文字列には届きません。

ステップ 0: 正しいレイヤーにいることを確認する

クエリがどのように構築されているかを確認します。

目にするもの	送信先	使用するもの
execute_hogql_query(...)、HogQLQuery、*QueryRunner、インサイト、HogQL の .ambr	HogQL プリンター経由の ClickHouse	このスキル
sync_execute(...)、client.execute(...)、手書きの SELECT ... FROM events、マイグレーション	ClickHouse に直接 (HogQL なし)	このスキル (ステップ 2〜5、ステップ 1 はスキップ)
Model.objects.filter(...)、.raw(...)、クエリセット、アプリ DB に対する RawSQL	Django ORM 経由の Postgres	このスキルではありません。profiling-slow-api-endpoints
personhog_client.*、get_personhog_client()、get_person_by_*	personhog (gRPC、Postgres)	このスキルではありません。posthog/personhog_client/README.md

コーディネーター / オーケストレーター / Celery タスク / Temporal ワークフロー / 管理コマンドを指し示された場合、ClickHouse クエリは通常 1 つ内側のレイヤー (ディスパッチされたアクティビティ、子ワークフロー、または apply_async のターゲット) にあります。ディスパッチをたどって、HogQL を構築するレイヤーまで進んでください。1 つのファイルが Postgres (作業の選別) と HogQL (作業の実行) を混在させている場合があります。それらは別々のタスクとして扱ってください。

低速なクエリが本番コード内の生の ClickHouse SQL である場合 (f-string / 文字列を sync_execute、client.execute、client.read_query に渡しており、プリンターの出力ではない)、その旨を指摘してから続行してください。プリンターは HogQL のマテリアライズドカラムの置換、プロパティグループのディスパッチ、レイジージョイン、team-id ガードを無償で提供しますが、生の SQL はそれぞれを再実装するか失うことになります。構造上の修正はそれを HogQL で表現することですが、それはより大きな作業です。両方の選択肢を提示してください (今はローカル修正、後で HogQL へ移行)。

シングルチームかマルチチームかが、生の SQL が許容されるかどうかを決めます。 execute_hogql_query はチームスコープです (1 つのチーム、team_id ガードを注入します)。

• 1 つのチームならほぼ常に HogQL にすべきです。 手書きのシングルチーム team_id = X クエリは、マテリアライゼーション、レイジージョイン、team-id ガードを漏らします。生のシングルチーム sync_execute は臭いものとして扱ってください。最も明確な兆候は、手作りのマテリアライズドカラムのルックアップ (たとえば JSONExtract フォールバックを伴う get_materialized_column_for_property(...)) です。これはプリンターの仕事を手作業で行っているものです。
• 多くのチームの場合は免除されます。 クロスチーム / グローバルなジョブ (エンリッチメント、請求のロールアップ、team_id がない、または team_id IN (...) を使った「X であるすべてのチームを見つける」) は execute_hogql_query を使用できません。生のままにしてその場で最適化してください。手作りのマテリアライズドカラムのルックアップはここでは想定内であり、臭いものではありません。

つまり、1 つのチームなら HogQL を推奨し、多くのチームなら生の SQL を直接最適化します。

INSERT は読み取り側を免除しません。 HogQL には INSERT がないため、SELECT を HogQL で構築し、それを出力して INSERT INTO <table> <printed_select> に連結します (生なのはラッパーだけです)。手書きの SELECT (生のカラム、properties に対する JSONExtract、手動の team_id) を伴うシングルチームの INSERT ... SELECT も同じ臭いです。SELECT を HogQL に移し、INSERT INTO ラッパーは生のままにしてください。本当に生で問題ないもの: Python の行の INSERT ... VALUES、マルチチームの INSERT ... SELECT、マイグレーション、ワンショットスクリプト。

背景: 最初に一度読んでください

ハンドブック(概念モデル): query-performance-optimization.md(遅いクエリの発見と修正)、hogql-python.md(プリンターパイプライン、HogQL の駆動)、clickhouse-queries-new-products.md(テーブル/ランナーの設計)。

テーブルスキーマは、ORDER BY / PARTITION BY / INDEX / マテリアライズドカラムを拾い読みしてください(行単位で読まないでください):

• Events: posthog/models/event/sql.py
• Sessions v3: sessions_v3.py(v2: sessions_v2.py)
• Persons: posthog/models/person/sql.py;overrides: person_overrides/sql.py
• Cohorts(cohortpeople のメンバーシップ): products/cohorts/backend/models/sql.py。Postgres の Cohort 定義(cohort.py)は Postgres の関心事です(ステップ 0)。
• その他のテーブル(app_metrics2、session_replay_events、log_entries、heatmaps、プロダクトテーブル): posthog/models/*/sql.py 配下、またはそれらを作成したマイグレーションで探してください。

HogQL 側: クエリのエントリーポイント query.py;プリンター clickhouse.py / base.py;ヘルパー utils.py;関数 posthog/hogql/functions/(集計は aggregations.py);スキーマ posthog/hogql/database/schema/。

マテリアライゼーション(プロパティアクセスを JSONExtract から自動的に書き換えます):

• レジストリ: ee/clickhouse/materialized_columns/columns.py、get_materialized_columns(table) / get_enabled_materialized_columns(table)、接続中の ClickHouse に対して 15 分間キャッシュされます。
• プロパティグループ: posthog/clickhouse/property_groups.py。
• プリンターの差し替え: base.py の _get_materialized_property_source_for_property_type() / visit_property_type()(約 1260、約 1354)、ClickHouse でのオーバーライドは clickhouse.py(約 412)。各プロパティアクセス時に、プリンターは接続中の ClickHouse に対して利用可能な最適な形式(直接カラム、プロパティグループ、または JSONExtract へのフォールバック)を出力します。

これが、同じ HogQL がテスト(疎なマテリアライゼーション)と本番(密なマテリアライゼーション)で異なる出力になる理由です。ルックアップは接続中の ClickHouse に対して実行されます。プリンターパス経由のプロパティアクセスはマテリアライズされると想定してください。プリンターをバイパスする手書きの SQL のみが、独自にルックアップを行う必要があります。

クラスタトポロジー(シャード、レプリカ、インジェストノードとデータノード): posthog/clickhouse/migrations/CLAUDE.md、マイグレーションを提案する前に読んでください。

ステップ 1: ClickHouse SQL を取得する

生の ClickHouse クエリですか? すでに SQL がある場合は、ステップ 2 に進んでください。HogQL の場合は、3 つの方法があります:

• Python: execute_hogql_query() (query.py) は response.clickhouse を返します。SQL のみで実行は不要です: prepare_and_print_ast(..., dialect="clickhouse") (utils.py)。準備済み AST の場合は print_prepared_ast を使用します。
• スナップショット: posthog/hogql_queries/test/__snapshots__/ (および各プロダクトのテストディレクトリ) 配下の .ambr ファイルに、代表的な入力に対して生成された ClickHouse SQL が保存されています。
• 本番環境 (最も情報量が多い): clusterAllReplicas(posthog, system, query_log) に対して /querying-production-databases-via-metabase を実行します (約 4 時間より古い行には posthog.query_log_archive を使用します。保持期間は約 22 日で、lc_* 型のカラムがあるため、こちらを推奨します)。is_initial_query、type = 'QueryFinish'、query_duration_ms > <threshold> でフィルタリングします。

ステップ 2: よくある臭いをスキャンする

ツールに頼る前に、ClickHouse SQL を目視で確認し、以下の点をチェックしてください。代わりに、特定の遅いクエリのランタイムコスト (バイト数 vs CPU vs 実行時間、高カーディナリティの内訳、関数でラップされたソートキー、比率メトリクスの二重スキャン、ソースコードへのトレース、EXPLAIN) から逆算して調査する場合は、references/investigation-playbook.md を参照してください。

FROM <table> FINAL

ReplacingMergeTree / CollapsingMergeTree / AggregatingMergeTree (person、groups、cohortpeople など) に対する FINAL は、読み取るすべてのパートにわたってオンザフライのマージを強制し、ソートキーの行ごとに最新バージョンへ重複排除します。これは並列読み取りを妨げ、メモリを爆発させ、パート数に対して悪いスケーリングをします。大規模またはシャーディングされたテーブルでは、ほとんど正しい選択ではありません。書き換え方:

• 行ごとの argMax: SELECT properties FROM person FINAL WHERE ... id IN (...) は SELECT argMax(properties, version) FROM person WHERE ... id IN (...) GROUP BY id になります。
• ORDER BY version DESC を伴う LIMIT 1 BY は、単調な version カラムを持つグループごとに 1 行を取得する場合に使用します。
• FINAL の前にフィルタリングする (本当に必要な場合): WHERE をソートキーのプレフィックスに対して選択的にし、マージされるパートを少なくします。

PostHog 固有: CLAUDE.md に従い、新しい person/group アクセスは生の person / groups ではなく、personhog (get_personhog_client) を経由すべきです。新しいコードにおける生の FROM person FINAL は、通常チューニングではなく personhog への移行を求めるサインです。

プロパティに対する JSON 操作

生の properties / person_properties / group_properties に対する JSONExtractString/Float(...)、JSONHas(...) などは、行ごとに JSON blob をパースします。直接マテリアライズされた（mat_* / dmat_*）カラムより最大約 100 倍遅く、プロパティグループの読み取りより約 10 倍遅くなります。

プリンターパスのクエリ（バックエンドの parse_select / execute_hogql_query / *QueryRunner、フロントエンドの api.queryHogQL / hogql`...`）：すべての JSONExtract*(properties, 'X') を properties.X に置き換えてください（文字列以外の場合は toFloat(...) / toInt(...) でラップします）。すべて を変換してください。何がマテリアライズされているかを判断しようとしないでください。 プリンターは出力時にルックアップを行い、最適な形式（マテリアライズされたカラム、プロパティグループ、DMAT スロット、または JSONExtract フォールバック）を出力します。properties.X は手書きの形式より 決して劣ることはなく、後でカラムがマテリアライズされると自動的に改善されます。

マイグレーション / レジストリ / DESCRIBE を読んで取捨選択しないでください。それはプリンターを再実装することになり、誤った結果を招きます。マテリアライズはマイグレーション由来でないことが多く、プロパティグループには専用カラムがなく、そのセットは環境ごと、時間ごとに変化します。部分的な変換では、何の利益もなくクエリが不整合になるだけです。すべてを変換し、プリンターに判断させてください。

例外として、プリンターをバイパスする生の SQL（マルチチームの sync_execute、マイグレーション、手組みの temporal activity 文字列）：プリンターも properties.X 構文もないため、マテリアライズされたカラムを直接参照します。正しいのはそこだけであり、そこでのみ行います。戦略（そのケースおよび背景向け。プリンターパスには不要）：直接マテリアライズされた materialized_columns.py、プロパティグループ property_groups.py、DMAT スロット posthog/models/dmat_slot_assignments/ と event/sql.py の EVENTS_TABLE_DYNAMICALLY_MATERIALIZED_COLUMNS()。新しい ClickHouse JSON 型は試験導入中です。最近の migrations を確認してください。

テストでは、posthog/test/base.py の materialized() コンテキストマネージャー（create_minmax_index、create_bloom_filter_index、小文字バリアント）を使って、ブロックのプロパティをマテリアライズします。

ソースで properties.X を使用している .ambr スナップショット内の JSONExtract は、テストフィクスチャのフォールバックにすぎません（本番ではマテリアライズされた読み取りを出力する可能性があります）。バグではないので、「修正」しないでください。

プライマリキーとスキップインデックス

WHERE が ORDER BY のプレフィックスをカバーしているか確認してください。events のソートキーは (team_id, toDate(timestamp), event, cityHash64(distinct_id), cityHash64(uuid)) であるため、自明でない events クエリでは、文書化された理由（例：すべてのイベントを必要とするコホート計算）がない限り、timestamp と event をフィルタリングする必要があります。

スキップインデックスの使用は get_index_from_explain / get_indexes_from_explain（posthog/test/base.py、EXPLAIN PLAN indexes=1, json=1 を実行）でアサートし、プリンターの変更によって気づかないうちに無効化されないようにしてください。インデックスが使用されない一般的な理由：nullIf/ラッパーがマテリアライズされたカラムを隠している、文字列化された NULL との比較、またはフィクスチャにカラムが存在しない（materialized(..., create_minmax_index=True) を使用します）。

events の自己結合

events（または任意の大きなテーブル）を自身に結合すると、処理が二倍になり、プライマリキーの順序が失われます。1 パスと条件付き集約に書き換えてください：sumIf(amount, event='purchase')、uniqIf(distinct_id, event='pageview')、uniqMapIf(...)。相関行（「コンバージョン前の最初のイベント」）の場合、順序付けられた groupArray に対する arrayFilter / arrayFirst / ウィンドウ関数が自己結合より優れています。集約関数が見つかりませんか？ aggregations.py に追加してください。

クエリ後の Python での行のフィルタリング

ClickHouse が行を返した後に Python で行を削除したり、重複排除したり、再チェックしたりしてから、ページを埋め直すために過剰取得 (または再度ページング) するのは、最適化ではありません。これは述語を最も遅いレイヤーに移動させ、レイテンシがページサイズではなくミス率に比例して増大するようにし、LIMIT/has_next を近似的なものにします。セッションリプレイの録画一覧がこれをやっており、パフォーマンス問題になりました。述語はクエリ内で表現してください。同じ行に対する WHERE/HAVING、その行が持つキーに対する集合メンバーシップの IN、または条件付き集約として表現します。テーブルが許容できない結合なしではクエリで表現できない場合は、その旨を述べ、別の設計を選んでください。Python でのパスにフォールバックしてはいけません。

CTE

ClickHouse の WITH name AS (SELECT ...) CTE はインライン展開され、マテリアライズされません: 2 回参照されれば 2 回実行され、ネストすると乗算的に展開されます。「プランナーが妙なことをしている」最も一般的な原因です。WITH ... AS MATERIALIZED が出荷されるまで (CH のリリースノートを確認してください) は、条件付き集約による単一パスに書き換えるか、FROM サブクエリを介して 1 回の実行を強制してください。

ステップ 3: EXPLAIN を実行する

EXPLAIN は代表的なデータがない開発用 ClickHouse でも動作します (プランナーの出力には行は必要ありません):

• EXPLAIN PLAN indexes=1, actions=1, json=1 SELECT ... — プライマリキー + スキップインデックスの使用状況を確認
• EXPLAIN QUERY TREE SELECT ... — アナライザー通過後の論理ツリーを確認
• EXPLAIN PIPELINE SELECT ... — プロセッサパイプラインを確認
• EXPLAIN ESTIMATE SELECT ... — パートごとの行/マークの推定値を確認
• EXPLAIN SYNTAX SELECT ... — 正規化された SQL を確認

ClickHouse EXPLAIN ドキュメント。並べて差分を取る手法 (疑わしいバリアントと修正済みバリアントで、Granules / ReadType / Prewhere 対プライマリキーを差分する) については、references/investigation-playbook.md を参照してください。

ステップ 4: 実際に測定する

EXPLAIN は意図を示しますが、リライトが速くなるかどうかを知るには、代表的なデータに対して両方のバージョンを実行します。

ローカル ClickHouse(正確性と EXPLAIN 用であり、ノイズが多すぎるためタイミング用ではありません): hogli dev:demo-data が合成データを投入し、hogli db:ch がクライアントを開きます。スキップインデックス / マテリアライズドカラム / スキーマ変更をローカル(単一ノード)で 試す ことはできますが、まずユーザーに確認してください。また、本番はマルチノードであるため、構造変更は /clickhouse-migrations を経由して往復する必要があることを覚えておいてください。ALTER TABLE ... MATERIALIZE INDEX ... は既存データに対して新しいインデックスを構築します。読み取りバイト数(FORMAT JSON、またはローカルの system.query_log)は、実時間よりもノイズの少ない代替指標です。

テストクラスター(タイミング): Metabase をフロントエンドとし、チーム 2 のデータの読み取り専用スナップショットで、ノイズの多い隣人はいません。/querying-production-databases-via-metabase を使用します。まず本番クエリを適合させます: team_id を差し替え、重複する日付範囲を選び、チーム 2 にないプロパティ/機能に依存するブランチを置き換えるかスキップします(判断が必要です)。タイミングを計る前に本番のマテリアライズドカラムを適用してください: DESCRIBE <table> は実際の pmat_*(events)/ pmat_* / mat_*(persons, groups)を一覧表示します。それらに JSONExtract(...) を差し替えないと、プリンターが決して出力しない形を計測することになります。SETTINGS use_uncompressed_cache=0 を設定し、5 回の中央値を取得し、Metabase のリクエスト時間ではなく system.query_log から query_duration_ms / read_rows / read_bytes / memory_usage / ProfileEvents を読み取ってください。

提案する前に測定してください。 どのリライトについても、同じチーム 2 適合クエリで元のバージョンと候補を実行し、前後の query_duration_ms、read_bytes、memory_usage を報告してください。数字のない提案は推測です。測定できなかった場合(クラスターが利用できない、適合しない、スキーマのみの変更)、その旨を伝えてください。クラスターは読み取り専用なので、スキーマ変更はローカルでプロトタイプを作成してください。

難易度の高いケース向けの自動リサーチ(パワーツール): tools/query-performance-ai/ は pi-autoresearch をラップし、テストクラスターに対してループで最適化します。セットアップは簡単ではありません(Docker サンドボックス、ANTHROPIC_API_KEY、Metabase の DB ID)。ユーザーに実行してもらってください。その README と coordinator を参照してください。

ステップ 5: 最適化を適用する

HogQL がより高速な ClickHouse SQL を、影響範囲が最小のレイヤーで出力するようにします:

• クエリランナー(最も低コスト): リライトが異なる HogQL クエリ(集約、結合順序、条件付き集約への CTE)である場合、posthog/hogql_queries/ または products/*/backend/ 配下のランナーを編集し、.ambr 経由でスナップショットを取得します。
• 新しい HogQL 関数: HogQLFunctionMeta(name, min_args, max_args, aggregate=True) を付けて aggregations.py(または functions/ 内の適切なファイル)に追加します。
• プリンターの変更: SQL レベルのリライトでプリンターが自動的に適用すべき場合。_get_optimized_materialized_column_equals_operation(約 574 行目、clickhouse.py)がテンプレートです。スナップショットテストと get_index_from_explain アサーションを追加します。
• マイグレーション: スキーマ変更(スキップインデックス、マテリアライズドカラム、プロジェクション、エンジン)の場合。/clickhouse-migrations を使用します。本番はデータ用と取り込み用のロールを持つマルチシャード/レプリカであるため、node_roles=[...]、sharded=True、is_alter_on_replicated_table=True が重要です。ON CLUSTER は決して使わないでください。きれいな例: 0250_property_values_lowercase_text_index.py。

チーム固有のヒューリスティクス

ある書き換えが特定のチームには役立っても、別のチームには害になることがあります（ファネルの書き換えは、ステップ 1 から 2 への大きな離脱がある場合には効果的でしたが、ステップ 1 がほぼすべてのイベントに一致する場合には遅くなりました）。このような非対称性については、投機的にコミットするのではなく、実行時ヒューリスティクス（ステップごとにイベントをカウントし、比率が有利な場合にのみ適用する）を提案してください。形はユーザーの判断です。

テストの規律

プリンタールール / クエリランナーを変更したり、関数を追加したりする場合は、生成される ClickHouse SQL を .ambr にスナップショットし、その効果が特定のインデックスや書き換えに依存する場合は EXPLAIN ベースのアサーションを追加してください。修正後にグリーンになったからといって証明にはなりません。変更をオフに切り替えて、テストが本当にあなたのパスを通っていることを確認してください。

学びのログ

references/learnings.md には、ここにある匂いにより微妙な判断が必要になったケーススタディがあります。どれかに強く依存する前に読んでください。そして測定した内容を追記してください。顧客データは不可（公開リポジトリ）: 生の person/group/distinct_id の値、カスタムプロパティの名前/値、チーム/組織名、行のサンプル、正確な規模は含めないでください。プレースホルダー（<custom_property>、<team_id>）や形状（数千万行）を使用してください。チーム 2 は名前を出しても問題ありませんが、それ以外のチーム ID は伏せてください。