What problem does it solve?
Slow ClickHouse and HogQL queries in PostHog cause timeouts, out-of-memory errors, and sluggish insights, and it is hard to know whether the fix belongs in the query runner, the HogQL printer, or a schema migration. This Skill provides a structured workflow to extract the actual ClickHouse SQL, identify common performance smells, measure rewrites against a real cluster, and apply the fix at the correct layer.
Core Features & Use Cases
- Smell detection: Scans generated ClickHouse SQL for known anti-patterns such as
FROM ... FINAL, JSONExtract over raw properties, missing skip indexes, self-joins on events, and inlined CTE blow-ups.
- Measurement-driven optimization: Guides running EXPLAIN variants and timing original versus rewritten queries on a test cluster, reporting
query_duration_ms, read_bytes, and memory_usage from system.query_log.
- Layered fixes: Directs the fix to the right layer — query runner, new HogQL aggregation function, printer rule, or ClickHouse migration — and includes a case-study learnings log and an investigation playbook for root-causing individual slow queries.
- Use Case: A trends insight times out in production. Pull the slow query from
query_log_archive, find it wraps the timestamp sort key in a function that defeats granule pruning, confirm with a side-by-side EXPLAIN diff, and fix the printer rule with a snapshot test.
Quick Start
Ask the agent to optimize a slow HogQL query or insight by providing the query text or a slow query ID from the production query log.
