# Generating Clickhouse Query Performance Reports > Produce and structure slow-query performance reports for PostHog's production ClickHouse (US and EU). Use when asked for a slow query report, query performance analysis over the last N days, per-team query cost, OOM or timeout investigation, cluster cost/memory regressions, or materialization candidates. Covers the modern `query_log_archive` source (typed `lc_*` columns, multi-day retention), how to categorize and attribute slow queries, root-cause patterns (unmaterialized JSONExtract, high-cardinality breakdowns, heavy joins), and the report structure. Runs queries via the `querying-production-databases-via-metabase` skill. - Skill: `gabrielmoreira/generating-clickhouse-query-performance-reports` (Agent Skill, multi-file: 4 files) - Install (CLI): `npx skillmds@latest add gabrielmoreira/generating-clickhouse-query-performance-reports` - Raw SKILL.md: https://api.skillmd.com/api/skills/gabrielmoreira/generating-clickhouse-query-performance-reports/raw - Safety review: pending (external: skill-scanner PASS, skillspector PASS) - Works with: Claude Code, Claude.ai, OpenAI Codex - Category: Docs & Writing - Author: gabrielmoreira (https://skillmd.com/u/gabrielmoreira) - Updated: 2026-09-09 - Page: https://skillmd.com/skills/gabrielmoreira/generating-clickhouse-query-performance-reports --- # Generating ClickHouse query performance reports This skill is the _methodology_ for investigating slow ClickHouse queries and writing up a performance report. It pairs with [`querying-production-databases-via-metabase`](../querying-production-databases-via-metabase/SKILL.md), which is the _mechanism_ (SSO-gated auth and `hogli metabase:query`). Run every query in this skill through that one. Reports themselves are not public. When it exists, the private `PostHog/query-performance-analysis` repo holds the historical reports and example query IDs; this repo holds only the tooling and methodology. That repo is usually checked out as a **sibling folder** to the posthog checkout (e.g. `../query-performance-analysis` relative to the repo root, or alongside it under the same parent directory). Look for a sibling directory named `query-performance-analysis` containing an `analysis/` folder of dated reports. If you find it, **add the new report there as a new markdown file** under `analysis/`, named `-.md` (match the existing naming, e.g. `2026-05-27-slow-queries-14d.md`). **The sibling repo may not exist, and that is fine.** If you cannot find it, do not write into the public posthog repo and do not block on it: write the report to a temp folder instead (e.g. `/tmp/-.md`), tell the user where you put it, and skip the previous-report comparison in step 9 (there is no history to diff against). ## Data source: `posthog.query_log_archive` (not `system.query_log`) `system.query_log` on the production clusters retains only a few **hours**, so it cannot answer a multi-day question. Use the Distributed archive table instead: ```sql FROM posthog.query_log_archive ``` It retains roughly three weeks and exposes `log_comment` as typed columns, so you skip `JSONExtract`. Query it directly (it already fans out across the cluster). Always filter `is_initial_query` so distributed sub-queries are not double-counted. Confirm current retention with a per-day `count()` before trusting a window (see `references/query-patterns.md`). Key columns (full list via `system.columns WHERE table='query_log_archive'`): | Column | Meaning | | ----------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- | | `team_id` (Int64) | Tenant. `0` / empty means internal or unattributed. | | `lc_kind` | How the query was issued: `request` (sync API/web), `celery` (async refresh), `temporal`, `cohort_calculation`, `dagster`. | | `lc_product` | `product_analytics`, `warehouse`, `experiments`, `messaging`, `web_analytics`, `replay`, `llm_analytics`, `cohorts`, ... | | `lc_access_method` | `personal_api_key`, `oauth`, `sharing_token`, or empty (logged-in web). | | `lc_query__kind` | Product query type: `TrendsQuery`, `FunnelsQuery`, `RetentionQuery`, `HogQLQuery`, ... | | `lc_workload` | `Workload.OFFLINE` / `ONLINE`. | | `lc_feature`, `lc_temporal__workflow_type`, `lc_route_id`, `lc_api_key_label` | Origin detail for attribution. | | `lc_dashboard_id`, `lc_insight_id`, `lc_experiment_id`, `lc_cohort_id` | Link a query back to the object that triggered it. | | `query`, `query_duration_ms`, `read_bytes`, `read_rows`, `memory_usage`, `exception_code` | The query and its cost. | Both regions have the archive. US and EU are separate clusters with different workloads and materialized columns; run cross-region comparisons against both. Discover the current ClickHouse database id per region with `hogli metabase:databases` (ids are not stable). Note that the ONLINE and OFFLINE Metabase connections for a region fan out to the same logical cluster, so they return the same `query_log_archive` data. ## What counts as a slow query ```sql query_duration_ms > 30000 OR exception_code IN (159, 160, 241) ``` | Code | Meaning | | ---- | --------------------- | | 159 | TIMEOUT_EXCEEDED | | 160 | TOO_SLOW | | 241 | MEMORY_LIMIT_EXCEEDED | Do **not** add `type = 'QueryFinish'`: OOM and timeout rows are `type = 'ExceptionWhileProcessing'`, so that filter silently drops every failure. The duration/exception predicate already excludes `QueryStart` rows (duration 0). Exclude the cluster health-poll query by `normalized_query_hash` (pattern in `references/query-patterns.md`). ## Producing the report The standard workflow, building from coarse to specific. Each step's SQL is in `references/query-patterns.md`. **Do not read previous reports until step 9.** Steps 1-8 should run against the raw data with fresh eyes, so the analysis captures the largest surface area rather than re-walking last report's findings. Reading the prior report early anchors you to its categories and makes it easy to miss a new problem it never mentioned. Diff against history only after the independent pass is done. 1. **Confirm the window.** Per-day `count()` over the intended range to verify the archive actually covers it (retention can be shorter than you expect). 2. **Headline summary.** Total slow queries, total cluster query-hours, bytes read, teams touched, and the split across succeeded-but-slow / timeouts / OOMs / other. Also capture the **cluster-wide totals across all queries** (not just the slow set): total query-seconds, total CPU-seconds (typed `ProfileEvents_OSCPUVirtualTimeMicroseconds` column, not the `Map` lookup), total bytes read, and total OOMs (`references/query-patterns.md` §1b). The slow-set sums are a biased subset; the all-query totals are the honest "busier / reading more this period?" denominator and the baseline future reports diff against. They cannot be backfilled once a window ages past retention, so record them every run. 3. **Date distribution.** Slow count, timeouts, and OOMs per day. This is where incidents announce themselves: a multi-day OOM or timeout surge against a flat baseline. 4. **Categorize.** Group by `lc_kind` × `lc_product` × `lc_access_method`. This separates background work (data modeling, dagster pre-aggregation, batch exports) from synchronous user-facing queries. 5. **Attribute.** Drill into the worst categories by `team_id`. Rank by **total cluster-hours** (`sum(query_duration_ms)`) and by **OOM count** separately. Before calling anything systemic, check whether one team or one API key dominates a metric: a single integration querying via a `personal_api_key` can account for the large majority of cluster OOMs, and the "incident" is then really one tenant. Attribute by `team_id` + `lc_api_key_label` first. Then add a **top-consumers view over all queries** (not just the slow set): top teams, top API keys (`lc_api_key_label`), and top tools (`lc_product`) ranked by **bytes, CPU-seconds, and wall-time** (`references/query-patterns.md` §4c). This is where the heavy-but-fast consumers show up: a tenant or integration can dominate cluster CPU or bytes through millions of cheap queries while never crossing the slow threshold, so it is invisible to the slow-set ranking. The CPU:wall ratio per row separates compute-bound from wait/IO-bound load. 6. **Characterize user-facing slowness.** For `lc_kind='request' AND lc_product='product_analytics'` with empty `lc_access_method` (logged-in web), break down by `lc_query__kind` and flag `breakdown_value` usage and JSONExtract over `person_properties`. This is the product-actionable bucket. Always include the **JSON-extracted property breakdown** (`references/query-patterns.md` §7): the top event vs person property names pulled from JSON blobs in the slow set, and which teams use each. These are the materialization candidates and a required report output. `HogQLQuery` (arbitrary user- and AI-authored SQL) deserves its own deep dive, including how much is AI-written and why it is slow; see `references/hogql-deep-dive.md`. 7. **Root-cause the worst offenders.** For the top findings, do not stop at "team X is slow": pull the full query and form a hypothesis for _why_, then test it with EXPLAIN. Root-causing an individual query is the [`optimizing-clickhouse-and-hogql-queries`](../optimizing-clickhouse-and-hogql-queries/SKILL.md) skill's job; its [`references/investigation-playbook.md`](../optimizing-clickhouse-and-hogql-queries/references/investigation-playbook.md) is the playbook (pull the full query, bytes vs CPU vs duration, the runtime causes, origin tracing, EXPLAIN). A useful finding includes a why ("scans full history because the time filter is function-wrapped and can't prune granules"), even if stated as a hypothesis. 8. **Examples + write-up.** Capture `query_id` + `event_date` for the worst offenders in each finding, then write the report (structure below). Because `system.query_log` retention is short, examples are resolved from `query_log_archive` (`WHERE query_id = '…' AND event_date = '…'`), not the old Metabase lookup card. Link each example to a shareable self-contained Metabase URL (the `query_link` recipe in `references/query-patterns.md`) so a reader clicks straight through to the query. When you draft the recommendations, **ground the researchable ones in code** by spawning background research agents (see "Grounding recommendations in code" below) so a recommendation points at the actual file and change rather than saying "audit X". 9. **Diff against the previous report (do this last, if there is one).** If the sibling `query-performance-analysis` repo is not present, skip this step entirely. Otherwise, only now, after the independent pass above, read the most recent dated report in its `analysis/` folder (sort by filename date). Add a short **delta** section to the new report covering: what moved since last time (new incidents, findings that grew or resolved, headline numbers up or down), and a **follow-up check** on anything the previous report flagged as needing action (a materialization that was recommended, a team to watch, a pipeline to make incremental). For each prior follow-up, state whether it is resolved, still open, or regressed, with the current numbers as evidence. Doing this last is deliberate: it keeps the fresh analysis unbiased while still closing the loop on history. **Make the windows comparable before quoting a delta:** confirm the previous report used the same window length (both reports here use a trailing `now() - INTERVAL N DAY`, so equal length but with overlapping and partial edge days). Headline totals between two trailing windows are usually dominated by whichever one-off incident sits inside one window and not the other, so a large drop is rarely a structural improvement. Always also compare an **incident-excluded baseline** (e.g. OOMs/day with the spike days removed) so the delta is not misread, and say explicitly when a total moved because an incident aged into or out of the window. Remember the summed metrics (bytes read, cluster-hours) cover the **slow set only**, not total cluster I/O, so they also move when a heavy background job's runs cross or stop crossing the 30s threshold; attribute a big bytes/hours swing to specific categories (it is usually one or two background pipelines) rather than reporting it as a cluster-wide change. ## Grounding recommendations in code A recommendation like "audit pipeline X" or "materialize property Y" is far more useful when it points at the actual code. For each recommendation that maps to a concrete place in the PostHog codebase, **spawn a background research agent** (the `Agent` tool, `run_in_background: true`, `subagent_type: general-purpose` or `Explore`) to read the source and return: how the relevant code works today, the specific file / function to change, any constraints, and whether a better mechanism already exists. Spawn **one agent per researchable recommendation**, all in a single message so they run in parallel, as soon as the recommendations are drafted. Let them run while you do the delta (step 9) and finalize the write-up, then fold each finding into its recommendation: replace "audit X" with "X is implemented in `` as ``; the change is ``", and cite the file paths so the human can jump straight in. The agents research and report only; they do not change code. Not every recommendation is researchable this way. Spawn an agent only where source code is the source of truth; skip operational / infra items: | Recommendation shape | Researchable? | What the agent reads | | ---------------------------------------- | ------------- | ------------------------------------------------------------------------ | | Rewrite a slow insight / query shape | yes | the query runner under `posthog/hogql_queries/`, the HogQL it emits | | Materialize property X | yes | the materialized-column registry (`ee/clickhouse/materialized_columns/`) | | Make pipeline Y incremental | yes | the dagster / temporal job that builds it | | Cap memory / add a query guard per key | yes | where ClickHouse SETTINGS and per-key throttling are applied | | Add a breakdown cardinality guard | yes | the trends / breakdown query runner | | Investigate an infra incident window | no | n/a (deploys, node health, cluster state) | | Watch / confirm a tenant's intended load | no | n/a (a judgement call for a human) | Give each agent a focused prompt: the recommendation, the specific question, and an instruction to return file paths + current behavior + the precise change point and to change nothing. The agents read the posthog repo (where this skill lives); the report itself is written to the separate `query-performance-analysis` repo. ## Interpreting the results - **Two populations live in "slow queries."** Tight-timeout API noise (queries erroring at ~10s against a low `max_execution_time`, usually `personal_api_key`) inflates the raw count without representing real compute. Genuinely expensive work is better measured by total cluster-hours and OOM count. Always call this distinction out; do not let timeout volume masquerade as slowness. - **Bytes read is the truest cost signal**, more than duration (which varies with cache and cluster load). High bytes against low rows means heavy columns, almost always JSONExtract over a `properties` blob. For root-causing individual queries, see the [`optimizing-clickhouse-and-hogql-queries`](../optimizing-clickhouse-and-hogql-queries/SKILL.md) skill. - **Background pipelines usually dominate raw cluster-time** (data-modeling DAGs, web-analytics pre-aggregation). That is expected; weigh them by whether their scan volume is necessary, separately from user-facing latency. ## Report structure A report should contain, in order: 1. One-line scope: region, window, and the slow definition / exclusions used. 2. Headline numbers table + the **cluster-wide totals (all queries)** table (total query-seconds, CPU-seconds, bytes read, OOMs) + the two-populations caveat. 3. Daily distribution table (flag any incident window). 4. Findings, worst first. **Every finding needs at least one concrete `query_id` + `event_date`, linked via the shareable `query_link` URL** (see `references/query-patterns.md`) so a reader clicks straight through to the exact query, plus a **hypothesis for why it is slow** (from the [`optimizing-clickhouse-and-hogql-queries`](../optimizing-clickhouse-and-hogql-queries/SKILL.md) skill's investigation playbook). Group findings by what they are: a per-tenant incident, the heaviest cluster-time consumers, user-facing insight slowness, and tight-timeout API noise. 5. A **top-consumers-by-resource section** (all queries, not just slow): top teams, top API keys, and top tools (`lc_product`) ranked by bytes / CPU / wall-time (`references/query-patterns.md` §4c), calling out consumers that never trip the slow threshold and the compute-bound vs wait-bound split. 6. A **JSON-extracted property table**: the top event and person property names pulled from JSON blobs in the slow set, with the teams using each (`references/query-patterns.md` §7). These are the materialization candidates. 7. Concrete recommendations tied to each finding (materialize property X, cap memory per API key, make pipeline Y incremental, ...). Ground the researchable ones in code (see "Grounding recommendations in code"): cite the file / function and the specific change, not just "audit X". 8. A **delta vs the previous report** (step 9): what changed since last time, plus a follow-up check on each action the previous report recommended (resolved / still open / regressed, with numbers). Omit this section when there is no previous report. Save the finished report as `analysis/-.md` in the sibling `query-performance-analysis` repo, never in the public posthog repo; if that repo is not present, save to a temp folder (e.g. `/tmp/-.md`) and tell the user the path. ## References - `references/query-patterns.md`: ready-to-run SQL for every step above, against `query_log_archive`. - `references/materialization-analysis.md`: finding properties to materialize and columns to drop, run across both US and EU. - `references/hogql-deep-dive.md`: analyzing `HogQLQuery` (arbitrary user/AI SQL) specifically, including how to identify AI-written HogQL (`lc_product`/`lc_feature`, not `ai_query_source`) and the causes that make ad-hoc and AI queries slow. ## Related skills This skill is fleet-level: it finds and ranks slow queries across all teams and writes the report. Once a finding points at one query you want to explain or fix, switch to [`optimizing-clickhouse-and-hogql-queries`](../optimizing-clickhouse-and-hogql-queries/SKILL.md) — it owns root-causing an individual query (its `references/investigation-playbook.md`) and applying the fix at the right layer (printer, query runner, or ClickHouse migration).