optimizing-clickhouse-and-hogql-queries

优化 ClickHouse 和 HogQL 查询

优化 ClickHouse 和 HogQL 查询（HogQL 会编译为 ClickHouse），而不是 Postgres / Django ORM。对于应用数据库查询（Model.objects.filter(...)），请停下来使用 profiling-slow-api-endpoints；第 0 步中有完整的分诊流程。

基于 ClickHouse SQL 工作，而不是 HogQL。 获取查询生成的 ClickHouse SQL，优化它，然后将变更翻译回 HogQL 查询、查询运行器、printer 或迁移。仅对 HogQL 进行推理会掩盖 ClickHouse 实际执行的内容。

假设你能编写 HogQL。对于从零开始编写新查询，请使用 /writing-clickhouse-queries；对于迁移机制，请使用 /clickhouse-migrations。

优化团队拥有的每个查询

当任务是"优化团队 X 的所有查询"时，请先构建完整清单，否则你会遗漏一些。

1. 将团队解析为其拥有的路径，使用 /establishing-code-ownership。拥有的路径涵盖后端 Python 以及 frontend/src/...，通常跨越多个产品；不要悄悄缩小到某一个产品或后端。
2. 在每个路径中查找查询。 大多数是 Python（*QueryRunner、execute_hogql_query、原始 sync_execute），但很多是在客户端构建并 POST 到 /query。在 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，通常是一次停滞的迁移）。两者都在范围内；后端 printer/函数修复不会触及手工构建的前端字符串。

第 0 步：确认你处于正确的层级

检查查询是如何构建的：

你看到的内容	它去往哪里	使用什么
execute_hogql_query(...)、HogQLQuery、*QueryRunner、某个 insight、HogQL .ambr	通过 HogQL 打印器发往 ClickHouse	本技能
sync_execute(...)、client.execute(...)、手写的 SELECT ... FROM events、某个迁移	直接发往 ClickHouse（无 HogQL）	本技能（第 2-5 步；跳过第 1 步）
Model.objects.filter(...)、.raw(...)、某个 queryset、针对应用数据库的 RawSQL	通过 Django ORM 发往 Postgres	不是本技能。profiling-slow-api-endpoints
personhog_client.*、get_personhog_client()、get_person_by_*	personhog（gRPC，Postgres）	不是本技能。posthog/personhog_client/README.md

如果你被指向某个 coordinator / orchestrator / Celery 任务 / Temporal 工作流 / 管理命令，ClickHouse 查询通常内嵌一层（被派发的 activity、子工作流或 apply_async 目标）。顺着派发链找到构建 HogQL 的那一层。一个文件可能混合 Postgres（挑选工作）和 HogQL（执行工作）；将它们视为独立的任务。

如果慢查询是生产代码中的原始 ClickHouse SQL（使用 f-string / 字符串传给 sync_execute、client.execute、client.read_query，而非打印器输出），请标记它，然后继续。打印器会免费提供 HogQL 物化列替换、属性分组派发、惰性 join 和 team-id 守卫；而原始 SQL 会重新实现或丢失每一项。结构性修复是将其用 HogQL 表达，但工作量更大；提供两种选项（现在做局部修复，之后再迁移到 HogQL）。

单 team 与多 team 决定了原始 SQL 是否可被谅解。 execute_hogql_query 是 team 作用域的（单个 team，注入 team_id 守卫）。

• 单个 team 几乎总是应该用 HogQL。 手写的单 team team_id = X 查询会泄露物化、惰性 join 和 team-id 守卫。将原始的单 team sync_execute 视为一种坏味道。最明显的迹象：手搓的物化列查找（例如带 JSONExtract 回退的 get_materialized_column_for_property(...)），这是本该由打印器完成的工作被手工完成。
• 多个 team 可豁免。 跨 team / 全局作业（enrichment、billing 汇总、"找出所有满足 X 的 team"，且没有 team_id 或使用 team_id IN (...)）无法使用 execute_hogql_query；保持原始形式并就地优化。此处手搓的物化列查找是预期内的，而不是坏味道。

因此：单个 team，推荐 HogQL；多个 team，直接优化原始 SQL。

INSERT 不会豁免读的那一半。 HogQL 没有 INSERT，所以用 HogQL 构建 SELECT，打印它，然后拼接进 INSERT INTO <table> <printed_select>（只有外层包装是原始的）。单 team 的 INSERT ... SELECT 搭配手写的 SELECT（原始列、对 properties 使用 JSONExtract、手动 team_id）是同样的坏味道：将 SELECT 迁移到 HogQL，保持 INSERT INTO 包装为原始。真正的原始可用场景：Python 行的 INSERT ... VALUES、多 team 的 INSERT ... SELECT、迁移、一次性脚本。

背景：先读一遍这些内容

手册（概念模型）：query-performance-optimization.md（发现/修复慢查询），hogql-python.md（printer 流水线，驱动 HogQL），clickhouse-queries-new-products.md（表/runner 设计）。

表结构，浏览其中的 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 相关事项（Step 0）。
• 其他表（app_metrics2、session_replay_events、log_entries、heatmaps、产品表）：可在 posthog/models/*/sql.py 下或创建它们的迁移中找到。

HogQL 侧：查询入口 query.py；printer clickhouse.py / base.py；辅助函数 utils.py；函数 posthog/hogql/functions/（聚合函数在 aggregations.py）；schema 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。
• Printer 替换：_get_materialized_property_source_for_property_type() / visit_property_type() 位于 base.py（约 1260、约 1354 行），ClickHouse 中的覆盖位于 clickhouse.py（约 412 行）。每次属性访问时，printer 会针对所连接的 ClickHouse 输出可用的最佳形式（直接列、属性组或 JSONExtract 回退）。

这就是为什么同一段 HogQL 在测试环境（物化稀疏）与生产环境（物化密集）中打印结果不同：查找是针对所连接的 ClickHouse 运行的。假定经由 printer 路径的属性访问会被物化；只有绕过 printer 的手写 SQL 才必须自行查找。

集群拓扑（分片、副本、摄取节点与数据节点）：posthog/clickhouse/migrations/CLAUDE.md，在提出任何迁移之前先阅读。

第 1 步：获取 ClickHouse SQL

已经是原始 ClickHouse 查询？ 你已经有 SQL 了，直接跳到第 2 步。对于 HogQL，有三种方式：

• 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 会强制对读取的每个 part 进行即时合并，按排序键去重到每行的最新版本。它会破坏并行读取、使内存暴涨、随 part 数量增加而严重劣化；在大表/分片表上很少是正确选择。改写方式：

• 用 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，在版本列单调的情况下每组取一行。
• 在 FINAL 之前过滤（如果确实需要）：让 WHERE 对排序键前缀具有选择性，这样被合并的 part 就很少。

PostHog 特定说明：根据 CLAUDE.md，新的人员/分组访问应通过 personhog（get_personhog_client），而不是直接访问原始的 person / groups。新代码中出现原始的 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 活动字符串）：没有打印机，也没有 properties.X 语法，所以直接引用物化列。仅在这种情况下正确。策略（适用于该情况以及作为背景；打印机路径不需要）：直接物化 materialized_columns.py；属性组 property_groups.py；DMAT 槽位 posthog/models/dmat_slot_assignments/ 以及 event/sql.py 中的 EVENTS_TABLE_DYNAMICALLY_MATERIALIZED_COLUMNS()。新的 ClickHouse JSON 类型正在试用中；请查看最近的迁移。

在测试中，使用 posthog/test/base.py 中的 materialized() 上下文管理器为该块物化属性（create_minmax_index、create_bloom_filter_index，以及小写变体）。

.ambr 快照中的 JSONExtract，如果其来源使用了 properties.X，那只是测试夹具的回退（生产环境可能会输出物化读取）。这不是 bug；不要"修复"它。

主键和跳数索引

检查 WHERE 是否覆盖了 ORDER BY 前缀。events 的排序键是 (team_id, toDate(timestamp), event, cityHash64(distinct_id), cityHash64(uuid))，因此非平凡的 events 查询应过滤 timestamp 和 event，除非有文档说明的原因（例如 cohort 计算需要所有事件）。

使用 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（或任何大表）与自身连接会使工作量翻倍，并丢失主键顺序。改写为一次遍历加条件聚合：sumIf(amount, event='purchase')、uniqIf(distinct_id, event='pageview')、uniqMapIf(...)。对于相关行（"转化前的第一个事件"），在有序 groupArray 上使用 arrayFilter / arrayFirst / 窗口函数，胜过自连接。缺少聚合函数？把它添加到 aggregations.py。

在查询之后用 Python 过滤行

在 ClickHouse 返回行之后，再用 Python 丢弃、去重或重新检查行，然后过度获取（或再次分页）以补满页面，这是一种优化。它把谓词移到了最慢的一层，让延迟随未命中数量而不是页面大小增长，并使 LIMIT/has_next 只是近似值。会话回放录像列表就是这么做的，结果造成了性能问题。要把谓词表达在查询里：作为同一行上的 WHERE/HAVING、作为该行所携带某个键上的集合成员判断 IN，或作为条件聚合。如果查询无法在不做该表承受不起的 join 的情况下表达它，就如实说明并选择另一种设计；不要退回到用 Python 遍历。

CTE

ClickHouse 的 WITH name AS (SELECT ...) CTE 是内联的，而不是物化的：被引用两次就会执行两次，嵌套会成倍放大。这是「查询规划器行为怪异」的最常见原因。在 WITH ... AS MATERIALIZED 发布之前（查看 CH 发布说明），改写为用条件聚合的单次遍历，或通过 FROM 子查询强制只执行一次。

第 3 步：运行 EXPLAIN

EXPLAIN 在没有代表性数据的开发 ClickHouse 上也能用（规划器输出不需要行）：

• EXPLAIN PLAN indexes=1, actions=1, json=1 SELECT ... 用于查看主索引 + 跳过索引的使用情况
• EXPLAIN QUERY TREE SELECT ... 用于查看分析器之后的逻辑树
• EXPLAIN PIPELINE SELECT ... 用于查看处理器流水线
• EXPLAIN ESTIMATE SELECT ... 用于查看每个 part 的行/mark 估算
• EXPLAIN SYNTAX SELECT ... 用于查看规范化后的 SQL

ClickHouse EXPLAIN 文档。关于并排对比技巧（可疑变体 vs 修复变体，对比 Granules / ReadType / Prewhere-vs-primary-key），参见 references/investigation-playbook.md。

第 4 步：真实测量

EXPLAIN 展示的是意图；要判断某次重写是否更快，需在具有代表性的数据上运行两个版本。

本地 ClickHouse（用于验证正确性和 EXPLAIN，不用于计时，因为计时噪声太大）：hogli dev:demo-data 会填充合成数据；hogli db:ch 会打开一个客户端。你 可以 在本地（单节点）尝试跳过索引 / 物化列 / schema 变更，但请先询问用户，并记住生产环境是多节点的，因此结构性变更必须经过 /clickhouse-migrations 来回流转；ALTER TABLE ... MATERIALIZE INDEX ... 会在现有数据上构建新索引。读取字节数（FORMAT JSON，或本地的 system.query_log）是比墙钟时间噪声更小的代理指标。

Test Cluster（用于计时）：由 Metabase 作为前端，是 team 2 数据的只读快照，没有嘈杂的邻居。使用 /querying-production-databases-via-metabase。先调整生产查询：替换 team_id，选择重叠的日期范围，替换/跳过依赖 team 2 所缺少的属性/特性的分支（需自行判断）。在计时前应用生产物化列：DESCRIBE <table> 会列出真实的 pmat_*（events）/ pmat_* / mat_*（persons、groups）；将它们替换掉 JSONExtract(...)，否则你计时的就是打印机从不输出的形态。设置 SETTINGS use_uncompressed_cache=0，取 5 次的中位数，并从 system.query_log 读取 query_duration_ms / read_rows / read_bytes / memory_usage / ProfileEvents，而不是 Metabase 的请求时间。

先测量，再提建议。 对于任何重写，在同一个适配 team 2 的查询上运行原始版本与候选版本，并报告前后 query_duration_ms、read_bytes、memory_usage。没有数字的建议只是猜测；如果你无法测量（集群不可用、无法适配、仅 schema 变更），请说明。该集群是只读的，因此在本地原型验证 schema 变更。

Autoresearch（强力工具） 适用于困难场景：tools/query-performance-ai/ 封装了 pi-autoresearch，以循环方式针对 Test Cluster 进行优化。搭建并非易事（Docker 沙箱、ANTHROPIC_API_KEY、Metabase DB ID）；让用户来运行它。参见其 README 和 coordinator。

第 5 步：应用优化

让 HogQL 在影响范围最小的层级上生成更快的 ClickHouse SQL：

• 查询运行器（成本最低）：如果重写是采用不同的 HogQL 查询（聚合、连接顺序、将 CTE 改为条件聚合），请编辑 posthog/hogql_queries/ 或 products/*/backend/ 下的运行器；通过 .ambr 做快照。
• 新增 HogQL 函数：在 aggregations.py（或 functions/ 中对应的文件）中添加，使用 HogQLFunctionMeta(name, min_args, max_args, aggregate=True)。
• 打印机变更：适用于打印机应自动应用的 SQL 级重写。_get_optimized_materialized_column_equals_operation（约第 574 行，clickhouse.py）是一个模板。添加一个快照测试以及一个 get_index_from_explain 断言。
• 迁移：适用于 schema 变更（跳过索引、物化列、投影、引擎）。使用 /clickhouse-migrations；生产环境是多分片/副本，且具有数据与摄取角色，因此 node_roles=[...]、sharded=True、is_alter_on_replicated_table=True 很重要；切勿使用 ON CLUSTER。简洁示例：0250_property_values_lowercase_text_index.py。

团队特定的启发式方法

有些改写对一个团队有帮助，却会损害另一个团队（一次 funnels 改写在大幅的 step-1-to-2 流失时效果很好，但当 step 1 几乎匹配每个事件时反而更慢）。对于这类不对称情况，建议采用运行时启发式方法（统计每个步骤的事件数，仅在比例有利时应用），而不是投机性地直接采用；具体形态由用户决定。

测试规范

当你修改打印机规则 / 查询运行器 / 添加函数时，将生成的 ClickHouse SQL 快照保存到 .ambr 中，并在收益依赖特定索引或改写时，添加基于 EXPLAIN 的断言。修复后通过并不能证明什么；把改动关掉，以确认测试确实覆盖了你的代码路径。

经验记录

references/learnings.md 中收录了一些案例研究，其中这里的某些坏味道需要更细致的处理；在严重依赖某一条之前先阅读它，并把你测量到的结果追加进去。不得包含客户数据（公开仓库）：不得包含原始 person/group/distinct_id 值、自定义属性名/值、团队/组织名称、行样本或精确规模。使用占位符（<custom_property>、<team_id>）或概数（tens of millions of rows）。可以点名 Team 2；其他团队 ID 需脱敏。