skills.rest
PostHog
PostHogOFFICIAL
Explore all skills by this author →
Website𝕏@PostHogGitHub

clickhouse-autoresearch-campaign

Runs autonomous ClickHouse query optimization campaigns with baseline capture, correctness checks, and lane-based hypothesis testing.

713|118|Updated Aug 11, 2020
Search Tags:#benchmarking#sql#performance tuning#Database#Query Optimization#clickhouse#AutoResearch
One-click install
npx skills add https://github.com/PostHog/posthog-foss --skill clickhouse-autoresearch-campaign
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: clickhouse-autoresearch-campaign
Source: https://github.com/PostHog/posthog-foss/tree/main/tools/query-performance-ai/query_performance_ai/sandboxed_autoresearch_agent/pi_plugin/skills/clickhouse-autoresearch-campaign
Command: npx skills add https://github.com/PostHog/posthog-foss --skill clickhouse-autoresearch-campaign

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Slow ClickHouse queries require tedious manual profiling, EXPLAIN analysis, and iterative rewriting. This Skill automates that investigation by running a disciplined optimization campaign on one git branch, systematically testing hypotheses and keeping only validated improvements.

Core Features & Use Cases

  • Baseline capture and correctness checking: Captures baseline metrics and result sets, then compares every candidate query against them so faster-but-wrong rewrites are caught automatically.
  • Lane and hypothesis management: Organizes optimization ideas into lanes (e.g., index usage, predicate ordering, JSON function choice) with stagnation-aware reviews that decide when to continue, pivot, or close a direction.
  • Timeout handling via range narrowing: Halves the query time range until it runs in 1-10 seconds, optimizes there, and escalates back to the full range after each kept win.
  • Use Case: A PostHog query runner generates a slow events query. The Skill initializes a workspace, profiles the query with EXPLAIN and system.query_log, tests rewrites like replacing replaceRegexpAll(JSONExtractRaw(...)) with JSONExtractString, and records schema-level suggestions (materialized columns, skip indexes) it cannot apply in the read-only sandbox.

Quick Start

Run a ClickHouse optimization campaign on the slow query in this repository, starting by reading orchestration.md and initializing the .clickhouse-autoresearch workspace.

Frequently Asked Questions about clickhouse-autoresearch-campaign

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I optimize a slow ClickHouse query automatically?▼

Initialize a campaign workspace with ch_campaign_init.py, capture a baseline with ch_capture_baseline.py, then run the experiment loop via autoresearch.py. The campaign tests hypotheses as query rewrites, keeps only results that are both faster and correct against the saved baseline result set.

How to handle ClickHouse queries that time out during benchmarking?▼

Use range narrowing: copy the query to query/narrowed.sql and halve the time range repeatedly until it completes in 1-10 seconds. Optimize against the narrowed baseline, then run escalation checks against the original range after every kept improvement.

Can this run schema changes like materialized columns or skip indexes?▼

No. The target cluster enforces readonly = 2, so ALTER, CREATE, and other writes fail. Schema-level opportunities are recorded in out-of-scope-suggestions.md with evidence and expected impact for the orchestrator to harvest as a deliverable.

What ClickHouse profiling data is available for query analysis?▼

The campaign user can read system.query_log, system.query_thread_log, system.text_log, and system.trace_log for its own queries, plus all EXPLAIN variants including EXPLAIN PLAN with indexes and EXPLAIN PIPELINE. Row policies hide other users' rows.

When does a query optimization campaign stop?▼

A campaign closes when the iteration budget is exhausted, the environment becomes unusable, or a campaign-level review finds no significant improvement within the stagnation window and no meaningful new hypotheses. Lane reviews can pause lanes but never end the campaign.

Related Skills

Explore More Data & Analytics Skills→
ntk148vntk148v
42

altinity-expert-clickhouse-reporting

Diagnose slow ClickHouse queries by analyzing system.query_log and related system views.

Community
Advanced
sayasaya8039sayasaya8039
2

postgres-patterns

Optimize PostgreSQL schemas, queries, indexes, and Row Level Security policies.

Community
Intermediate
incidentfoxincidentfox
654

metrics-analysis

Analyzes Prometheus/Grafana metrics with PromQL queries to identify latency, error rates, and resource usage.

Official
Intermediate
vecearvecear

sql-query-optimizer

Analyze EXPLAIN plans and rewrite SQL queries for PostgreSQL and MySQL.

Community
Advanced