diagnosing-endpoint-performance

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

Diagnosing endpoint performance

诊断端点性能

This skill walks through a specific endpoint that is slow, expensive, or unreliable, and produces a concrete recommendation. It is the deep-dive counterpart to

auditing-endpoints

(which finds candidates).

本技能针对运行缓慢、成本高昂或不可靠的特定端点进行分析，并给出具体建议。它是

auditing-endpoints

（用于筛选待优化端点）的深度分析配套技能。

When to use this skill

何时使用此技能

"This endpoint is slow / timing out"
"Why is my endpoint hitting the cost cap?"
"Should I materialise X?"
An endpoint surfaced from
```
auditing-endpoints
```
as a failing materialisation or expensive caller
The user has a specific endpoint in mind and wants advice

If the question is project-wide ("what should I clean up?"), use

auditing-endpoints

first.

“这个端点运行缓慢/超时了”
“为什么我的端点触达成本上限？”
“我应该物化X吗？”
经
```
auditing-endpoints
```
筛选出的物化失败或调用成本高昂的端点
用户针对某个特定端点寻求优化建议

如果问题是项目范围的（比如“我应该清理哪些内容？”），请先使用

auditing-endpoints

技能。

Available tools

可用工具

Tool	Purpose
`endpoint-get`	Full endpoint config: query, current version, `data_freshness_seconds` , materialisation status
`endpoint-versions`	History of every version (query + materialisation state); which version is current
`endpoint-materialization-status`	Whether materialisation is eligible, current state, last run, last error
`endpoints-materialization-preview`	What the materialised query would look like, plus the rejection reason if ineligible
`endpoints-last-execution-times`	When was it last called (endpoint-level sanity-check that it is in active use)
`execute-sql`	Query `query_log` for endpoint-level call frequency and per-call duration/bytes

工具名称	用途
`endpoint-get`	获取完整端点配置：查询语句、当前版本、 `data_freshness_seconds` 、物化状态
`endpoint-versions`	获取所有版本的历史记录（查询语句+物化状态）；查看哪个版本是当前生效版本
`endpoint-materialization-status`	查看是否符合物化条件、当前状态、上次运行时间、上次错误信息
`endpoints-materialization-preview`	查看物化后的查询语句样式，若不符合条件则返回拒绝原因
`endpoints-last-execution-times`	查看端点上次调用时间（端点级别的 sanity-check，确认其处于活跃使用状态）
`execute-sql`	查询 `query_log` 获取端点的调用频率、单次调用时长/读取字节数

The decision tree

决策树

When deciding what to recommend, walk these in order — the first one that applies is the cheapest fix.

在确定优化建议时，请按以下顺序执行——第一个适用的方案是成本最低的修复方式。

Step 1 — Is it cached at all?

步骤1 — 是否已启用缓存？

Fetch the endpoint and look at

data_freshness_seconds

(it sets both the cache TTL and, when materialised, the refresh cadence). If the user's traffic calls the same parameters repeatedly within that window, every call after the first is a cache hit and effectively free.

TTL is at the default (24h / 86400s) and the data really doesn't need fresher than that → done, no change needed.
TTL is at the 900s floor (15 min) and the user is hitting the endpoint many times per minute → bump the TTL. This is almost always the cheapest first move. (
```
data_freshness_seconds
```
is an enum: 900, 1800, 3600, 21600, 43200, 86400, 604800 — there is no sub-15-minute value.)
TTL is at the floor because the data must be fresh (e.g. real-time dashboard) → cache won't help, skip to step 2.

The shape of the variables matters here: if every call passes different

user_id

date_from

values, the cache has many distinct keys and a higher TTL helps less. If almost every call uses the same handful of parameter combinations, the cache helps a lot.

获取端点信息并查看

data_freshness_seconds

（该参数同时设置缓存TTL和物化后的刷新频率）。如果用户的流量在该时间窗口内重复调用相同参数，那么第一次调用后的所有请求都会命中缓存，几乎没有成本。

TTL为默认值（24小时/86400秒）且数据确实不需要更频繁的更新→无需修改，任务完成。
TTL为最低值900秒（15分钟）且用户每分钟多次调用该端点→提高TTL。这几乎总是成本最低的首选方案。（
```
data_freshness_seconds
```
是枚举值：900、1800、3600、21600、43200、86400、604800——没有低于15分钟的选项。）
TTL设为最低值是因为数据必须保持实时更新（比如实时仪表盘）→缓存无法起到帮助，跳至步骤2。

变量的结构很重要：如果每次调用都传入不同的

user_id

或

date_from

值，缓存会有大量不同的键，提高TTL的帮助有限。如果几乎所有调用都使用少量固定的参数组合，缓存的作用会非常显著。

Step 2 — Should it be materialised?

步骤2 — 是否应该启用物化？

Materialisation pre-computes the query into a saved view that's refreshed on a schedule. Reads become near-instant — at the cost of staleness equal to the refresh interval, plus storage and compute for the materialisation itself.

Call

endpoints-materialization-preview

. The response tells you:

Eligible + clean transform → strong candidate. Recommend enabling, especially for endpoints with predictable filter shapes (variables, breakdowns).
Not eligible, with a rejection reason → cannot materialise. The reason often hints at the next step (see step 3 — rewrite).
Eligible but the transform is gnarly (lots of range pairs, complex aggregation re-derivation) → materialisation will work but may not save much. Worth flagging before flipping the switch.

When materialisation is enabled, callers must pass all materialised variables — calls without them are rejected (security: prevents returning unfiltered data). Pair the recommendation with a note about which variables become required.

物化会预先计算查询结果并保存为视图，按计划刷新。读取操作会变得近乎即时——代价是数据新鲜度会滞后于刷新间隔，同时需要为物化视图支付存储和计算成本。

调用

endpoints-materialization-preview

。返回结果会告知：

符合条件且转换逻辑简洁→强烈推荐启用，尤其是对于具有可预测筛选结构（变量、细分维度）的端点。
不符合条件并附带拒绝原因→无法进行物化。原因通常会提示下一步操作（见步骤3——重写查询）。
符合条件但转换逻辑复杂（包含大量范围对、复杂聚合重推导）→物化可以生效，但节省的成本有限。在启用前需向用户说明这一点。

启用物化后，调用者必须传入所有物化变量——未传入变量的请求会被拒绝（安全要求：防止返回未过滤的数据）。给出建议时需附带说明哪些变量会变为必填项。

Step 3 — Does the query need rewriting?

步骤3 — 是否需要重写查询？

If the endpoint isn't eligible for materialisation, the rejection reason from

endpoints-materialization-preview

is usually the lead:

Cohort breakdown / compare mode rejection → regular property breakdowns materialise fine; only cohort breakdowns and compare mode are blocked. Swap a cohort breakdown for a property breakdown, or drop compare mode (expose the comparison window as a variable instead).
JOINs combined with variables → a top-level
```
JOIN
```
plus a variable filter is rejected for materialisation, because applying the variable changes the joined row cardinality and silently produces wrong results (e.g.
```
LEFT JOIN
```
non-matches lose the variable column). Restructure so the variable filters a single table — push the filter into a subquery/CTE that's then joined, rather than filtering across the join. This is the most common "looks fine but won't materialise" trap.
"Missing variables" / unbounded scan → the query reads too much data without a filter. Encourage adding a required time-window variable (e.g.
```
date_from
```
,
```
lookback_days
```
).
HogQL with
*
/ non-deterministic functions → narrow the columns selected, replace
```
now()
```
/
```
today()
```
with a variable when possible.

Check

endpoint-versions

to see whether the query was recently changed. Often the regression came from a specific commit and reverting that version is faster than rewriting.

如果端点不符合物化条件，

endpoints-materialization-preview

返回的拒绝原因通常是突破口：

Cohort breakdown / compare mode 拒绝→常规属性细分可以正常物化；仅用户群细分和对比模式会被阻止。将用户群细分替换为属性细分，或者移除对比模式（将对比窗口作为变量暴露）。
JOIN 与变量结合→顶层
```
JOIN
```
加上变量筛选会被拒绝物化，因为应用变量会改变连接后的行基数，导致结果错误（比如
```
LEFT JOIN
```
不匹配的行会丢失变量列）。重构查询，让变量仅筛选单个表——将筛选逻辑推入子查询/CTE后再进行连接，而非跨连接筛选。这是最常见的“看似正常但无法物化”的陷阱。
“缺少变量” / 无界扫描→查询未经过滤读取过多数据。建议添加必填的时间窗口变量（比如
```
date_from
```
、
```
lookback_days
```
）。
HogQL 使用
*
/ 非确定性函数→缩小选择的列范围，尽可能用变量替代
```
now()
```
/
```
today()
```
。

查看

endpoint-versions

确认查询是否最近被修改过。性能退化通常来自特定的提交，回滚到之前的版本比重写查询更快。

Step 4 — Is the slow version even the one being called?

步骤4 — 被调用的是否是慢版本？

Only the latest version runs by default; older versions run only when a caller pins

?version=N

. So the version to tune is almost always the current one — unless a pinned older version is the culprit. Call

endpoint-versions

and read each version's

last_executed_at

to see which versions have been hit recently; a materialised version with a null or long-stale

last_executed_at

is a candidate to unmaterialise or delete rather than tune (confirm first — that signal only counts API-key runs and can be sparse).

For endpoint-level call frequency and per-call cost, query

query_log

with

execute-sql

— it carries

query_duration_ms

read_rows

, and

read_bytes

, handy for confirming how heavy the endpoint's calls actually are:

sql

SELECT count() AS calls, max(query_start_time) AS last_called, avg(query_duration_ms) AS avg_ms
FROM query_log
WHERE name = '<endpoint_name>' AND endpoint LIKE '%/endpoints/%' AND is_personal_api_key_request

默认情况下只有最新版本会运行；旧版本仅在调用者指定

?version=N

时才会运行。因此需要优化的几乎总是当前版本——除非某个被固定的旧版本是问题根源。调用

endpoint-versions

查看每个版本的

last_executed_at

，确认哪些版本最近被调用过；如果某个物化版本的

last_executed_at

为null或已过期很久，可以考虑取消物化或删除该版本（需先确认——该信号仅统计个人API密钥的调用，可能数据稀疏）。

要获取端点的调用频率和单次调用成本，使用

execute-sql

查询

query_log

——其中包含

query_duration_ms

、

read_rows

和

read_bytes

，可用于确认端点调用的实际负载：

sql

SELECT count() AS calls, max(query_start_time) AS last_called, avg(query_duration_ms) AS avg_ms
FROM query_log
WHERE name = '<endpoint_name>' AND endpoint LIKE '%/endpoints/%' AND is_personal_api_key_request

Workflow

工作流程

Identify the endpoint by name. If the user gave a URL, parse the name from
```
/api/projects/{team_id}/endpoints/{name}/run
```
.
```
endpoint-get
```
to fetch full config. Note:
```
data_freshness_seconds
```
,
```
is_materialized
```
, current version, query kind.
```
endpoint-materialization-status
```
to see materialisation health (failed? eligible?).
```
endpoint-versions
```
to see the query history and which version is current (the one callers hit unless they pin
```
?version=N
```
).
If the user gave specific symptoms ("times out", "memory limit", "very slow"), walk the decision tree above. Otherwise, present the four checkpoints and ask which matches.
End with a concrete recommendation — bump TTL, materialise, rewrite, or rollback — and confirm before applying it.

通过名称识别端点。如果用户提供了URL，从
```
/api/projects/{team_id}/endpoints/{name}/run
```
中解析出名称。
调用
```
endpoint-get
```
获取完整配置。注意：
```
data_freshness_seconds
```
、
```
is_materialized
```
、当前版本、查询类型。
调用
```
endpoint-materialization-status
```
查看物化健康状态（是否失败？是否符合条件？）。
调用
```
endpoint-versions
```
查看查询历史和当前生效版本（调用者默认使用该版本，除非指定
```
?version=N
```
）。
如果用户提供了具体症状（“超时”“内存限制”“非常慢”），按照上述决策树分析。否则，列出四个检查点并询问用户符合哪一种情况。
最后给出具体建议——提高TTL、启用物化、重写查询或回滚版本，并在应用前获得用户确认。

Example interaction

示例交互

text

User: "monthly_active_users is timing out for some callers"

Agent steps:
- endpoint-get monthly_active_users → HogQL endpoint, data_freshness_seconds=900,
  is_materialized=false, current version v4
- endpoint-materialization-status monthly_active_users → not materialised; eligible: true
- endpoint-versions monthly_active_users
  → v4 is current; v1–v3 are prior query revisions (callers hit v4 unless pinned)
- endpoints-materialization-preview → eligible, clean transform with one range pair

- "Three things stand out:
   1. Cache TTL is 900 seconds (the 15-min floor), but the data is
      aggregating a month — that aggressive a refresh probably isn't
      necessary. Bumping to 1 hour (3600s) would dramatically reduce
      ClickHouse load.
   2. It's eligible for materialisation with a clean transform. If
      callers can tolerate 5-15 minute staleness, materialisation
      would make reads near-instant.
   3. v4 is the current version, so callers hit it by default — the
      tuning target is clear.

   Quickest win: bump TTL to 3600s. Bigger win: materialise.

   Which trade-off matches your needs? If you need real-time freshness,
   neither helps and we'd need to rewrite the query — likely narrowing
   the aggregation window."

text

用户：“monthly_active_users 端点对部分调用者出现超时情况”

Agent 步骤：
- 调用endpoint-get monthly_active_users → HogQL端点，data_freshness_seconds=900，is_materialized=false，当前版本v4
- 调用endpoint-materialization-status monthly_active_users → 未物化；符合条件：是
- 调用endpoint-versions monthly_active_users
  → v4是当前版本；v1–v3是历史查询版本（调用者默认使用v4，除非指定版本）
- 调用endpoints-materialization-preview → 符合条件，转换逻辑简洁，包含一个范围对

- “有三点需要注意：
   1. 缓存TTL为900秒（15分钟最低值），但该端点聚合的是月度数据——如此高频的刷新可能并无必要。将TTL提高到1小时（3600秒）会大幅降低ClickHouse的负载。
   2. 该端点符合物化条件且转换逻辑简洁。如果调用者可以接受5-15分钟的数据延迟，启用物化会让读取操作近乎即时。
   3. v4是当前生效版本，所以调用者默认使用该版本——优化目标明确。

   最快的解决方案：将TTL提高到3600秒。更彻底的解决方案：启用物化。

   哪种方案更符合你的需求？如果需要实时数据新鲜度，这两种方案都无效，我们需要重写查询——可能需要缩小聚合窗口。”

Important notes

重要注意事项

Cache is almost always the first fix. It's free, instantly reversible, and doesn't change data semantics. Resist jumping to materialisation if a higher TTL would do.
Materialisation has hidden costs. Storage of the materialised view, refresh compute, and the requirement that callers pass all variables.
Don't rewrite the query without the user. A query change creates a new version and may break callers!!! Surface the suggested change, get sign-off, then apply.
Three usage signals.
```
endpoint-get
```
's
```
last_executed_at
```
is endpoint-level recency;
```
endpoint-versions
```
gives each version's own
```
last_executed_at
```
;
```
query_log
```
(via
```
execute-sql
```
) gives endpoint-level call frequency and per-call cost. All count only personal-API-key calls, and per-version recency can be sparse — confirm with the user before calling a version dead.
The "right" fix depends on the SLA, not the query. Always ask the user about acceptable staleness before recommending materialisation. A 15-minute-stale materialised view is wrong for a real-time dashboard, regardless of how cheap it'd be.
Tell PostHog what's missing. If the diagnosis runs into a product limitation (an eligibility rule, the TTL enum, required variables), nudge the team via
```
agent-feedback
```
.

缓存几乎总是首选修复方案。它零成本、可立即回滚，且不会改变数据语义。如果提高TTL就能解决问题，不要急于启用物化。
物化存在隐性成本。包括物化视图的存储成本、刷新计算成本，以及要求调用者传入所有变量的限制。
不要未经用户同意重写查询。查询修改会创建新版本，可能导致调用者出错！先向用户展示建议的修改内容，获得确认后再应用。
三个使用信号。
```
endpoint-get
```
中的
```
last_executed_at
```
是端点级别的最近调用时间；
```
endpoint-versions
```
提供每个版本的
```
last_executed_at
```
；
```
query_log
```
（通过
```
execute-sql
```
）提供端点级别的调用频率和单次调用成本。这些信号仅统计个人API密钥的调用，且每个版本的最近调用时间数据可能稀疏——在判定某个版本已停用前需与用户确认。
“正确”的修复方案取决于SLA，而非查询本身。在推荐物化前，务必询问用户可接受的数据延迟。对于实时仪表盘来说，15分钟延迟的物化视图是不合适的，无论其成本有多低。
向PostHog反馈缺失功能。如果诊断过程中遇到产品限制（比如 eligibility规则、TTL枚举值、必填变量要求），请通过
```
agent-feedback
```
向团队反馈。