diagnosing-endpoint-performance

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.

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.

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

Optional AI accelerator (only if the tools resolve). When your catalog includes endpoint-materialization-suggestion — it is gated behind the endpoints-ai-materialization-fix feature and needs the org's AI data processing approval — you can let PostHog draft the rewrite instead. It rewrites the query into a semantically equivalent form and validates it against the live checks before returning it. ok means the rewrite passes the checks plus variable- and output-column parity, but semantic equivalence is the model's claim, not proven. Before applying, run the original and the rewrite with the same representative variable values (via execute-sql or the endpoint playground) and compare the results; only then apply it with endpoint-update (creates a new version), then confirm with endpoint-materialization-status. cannot_fix means no equivalent rewrite exists (e.g. an OR {variables.x} = 'all' optional-variable idiom) — say so rather than forcing a change in behaviour. To reason about the rewrite yourself, call endpoint-materialization-conditions (same feature gate) — it returns the actual source code of the checks this instance enforces plus the rewrite contract; treat that as authoritative over the bullets above. If neither tool is in your catalog, the feature is off — stay on the manual path above and say the AI rewrite is unavailable.

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:

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