/diagnosing-endpoint-performance

name: diagnosing-endpoint-performance
description: >
  Diagnose why a PostHog endpoint is slow or expensive and propose a concrete fix — bump the cache
  TTL, enable materialisation, restructure variables, or rewrite the query. Use when the user says
  "this endpoint is slow", "my endpoint times out", "we're hitting the cost cap on this one", or
  asks "should I materialise this?". Focuses on a single named endpoint, not a project-wide audit.

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).

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.

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 |

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?

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` or `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.

Step 2 — Should it be materialised?

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.

Step 3 — Does the query need rewriting?

For a SQL endpoint that isn't eligible, the rejection reason from `endpoints-materialization-preview` is the lead. That tool always resolves. Read the reason and match it to the bullets below — they summarise the common cases, not every check the server runs, so the raw reason wins when the two disagree. Propose the rewrite by hand:

• **Cohort breakdown / compare mode rejection** → regular property breakdowns materialise fine;

only cohort breakdowns are blocked. Swap a cohort breakdown for a property breakdown. Compare mode materialises for Trends endpoints; on any other query kind, 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 vari