CtrlK
BlogDocsLog inGet started
Tessl Logo

optimizing-clickhouse-and-hogql-queries

Workflow for optimizing ClickHouse and HogQL queries. Use when a HogQL query, query runner, insight, or report is too slow; when a hand-written ClickHouse query (via `sync_execute` or in a migration) is too slow; when ClickHouse times out or hits memory limits; when investigating a slow `system.query_log` row; or when reviewing a proposed HogQL printer change for performance. Covers extracting the ClickHouse SQL, common smells (`FROM ... FINAL`, `JSONExtract` over properties, missing skip indexes, self-joins, CTE blow-up), measuring against a real cluster, and applying the fix at the right layer (printer, query runner, or migration). Does NOT cover Postgres / Django ORM / app-database queries; those need pganalyze and the Postgres section of `query-performance-optimization.md`.

76

Quality

96%

Does it follow best practices?

Run evals on this skill

Adds up to 20 points to the overall score

View guide

SecuritybySnyk

Critical

Do not install without reviewing

SKILL.md
Quality
Evals
Security

Quality

Content

92%Weight 40%Scale 1-5

Reviews the quality of instructions and guidance provided to agents. Good implementation is clear, handles edge cases, and produces reliable results.

A high-quality, highly actionable optimization playbook with a clear sequenced workflow and well-structured progressive disclosure; the only weak spot is mild redundancy in the materialization rationale that affects conciseness.

Suggestions

Consolidate the repeated materialization/printer-lookup rationale (currently restated in the Background block, the JSON-operations section, and the primary-key section) into one place and cross-reference it.

The Background section lists many file paths inline; consider whether the per-table schema inventory belongs in a reference file to further reduce main-skill tokens.

DimensionReasoningScore

Conciseness

Dense and expert-targeted with no basic-concept padding, but the materialization/printer rationale is restated across several sections (e.g. JSON operations, primary-key, and the Background block) and could be consolidated to trim tokens.

4 / 5

Actionability

Provides concrete, near copy-paste-ready guidance throughout — specific rewrites (`argMax`, `sumIf`, `LIMIT 1 BY`), exact commands (`EXPLAIN PLAN indexes=1, actions=1, json=1`, `SETTINGS use_uncompressed_cache=0`, median of 5), and file paths with line numbers.

5 / 5

Workflow Clarity

Clear Step 0→5 sequence with an explicit triage table and validation checkpoints ('Measure before suggesting', flip-the-change-off confirmation, EXPLAIN-based assertions, median-of-5 protocol, and a fallback when measurement is impossible).

5 / 5

Progressive Disclosure

The body is an overview that signals two real one-level-deep bundle files (`references/investigation-playbook.md`, `references/learnings.md`, both present) at the right points, keeping deep-dive material out of the main flow while leaving essentials inline.

5 / 5

Total

19

/

20

Passed

Description

100%Weight 40%Scale 1-5

Based on the skill's description, can an agent find and select it at the right time? Clear, specific descriptions lead to better discovery.

An exemplary description: concrete actions, natural trigger terms, explicit what/when guidance, and a clear boundary against the adjacent Postgres skill. No meaningful gap to anchor below 5 on any dimension.

DimensionReasoningScore

Specificity

Lists multiple concrete actions — extracting the ClickHouse SQL, scanning for named smells (`FROM ... FINAL`, `JSONExtract`, missing skip indexes, self-joins, CTE blow-up), measuring against a real cluster, and applying the fix at the printer/runner/migration layer — giving comprehensive coverage.

5 / 5

Completeness

Explicitly answers both 'what' (workflow covering extract/smells/measure/apply) and 'when' (multiple concrete 'Use when...' triggers), and adds an explicit 'Does NOT cover' boundary, matching the top anchor.

5 / 5

Trigger Term Quality

Uses the natural phrases a developer would say — 'too slow', 'times out', 'hits memory limits', 'query runner, insight, or report', 'sync_execute', 'migration', 'system.query_log row', 'HogQL printer change' — with strong synonym coverage.

5 / 5

Distinctiveness Conflict Risk

Occupies a clear niche (ClickHouse/HogQL query performance) and explicitly excludes Postgres/Django with a pointer to a different doc, minimizing overlap with sibling skills.

5 / 5

Total

20

/

20

Passed

Validation

93%

Checks the skill against the spec for correct structure and formatting. All validation checks must pass before discovery and implementation can be scored.

Validation15 / 16 Passed

Validation for skill structure

CriteriaDescriptionResult

relative_links

Relative link issues: 49 suspicious

Warning

Total

15

/

16

Passed

Repository
PostHog/posthog
Reviewed

Table of Contents

Is this your skill?

If you maintain this skill, you can claim it as your own. Once claimed, you can manage eval scenarios, bundle related skills, attach documentation or rules, and ensure cross-agent compatibility.