diagnosing-endpoint-performance

Files (1)

• SKILL.md

SKILL.md (first lines)

# 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 variable when possible.
- **No bullet matches** → the list above is not exhaustive; the live checks reject more shapes than
  it names (a variable inside an `OR`, a variable compared against another variable, a variable in a
  `HAVING` clause, an unsupported operator, a query kind that can't be materialised). Quote the
  rejection reason to the user verbatim and reason from it directly rather than forcing the nearest
  bullet. Some reasons have no equivalent rewrite at all — the `OR {variables.x} = 'all'`
  optional-variable idiom is one — so say that instead of changing the query's behaviour.

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.

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

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

From PostHog/skills, MIT.