Open source · v0.x · MIT

Fix slow ClickHouse queries in seconds

ClickAdvisor analyzes your SQL, finds antipatterns, and gives you formally proven optimization recommendations — version-aware, local-first, no data leaves your machine.

Get Started View on GitHub

21+ RulesF1 Score 1.08,804 KB docs indexed

chadvisor — analyze

$ chadvisor analyze --sql query.sql --ch-version 25.3

╭─ ClickAdvisor — Query Analysis Report ──────────────────╮
│  ClickHouse version: 25.3 │ Rules applied: 9 │ Skipped: 0│
╰─────────────────────────────────────────────────────────╯

● HIGH   R-001 · count_distinct_to_uniqExact
  Found:    COUNT(DISTINCT user_id)
  Solution: Replace with uniqExact(user_id)  →  2–5× faster
  Before:   SELECT COUNT(DISTINCT user_id) FROM events
  After:    SELECT uniqExact(user_id) FROM events

● HIGH   R-005 · to_date_eq_to_datetime_range
  Found:    toDate(event_time) = '2024-01-15' blocks primary key index
  Solution: event_time >= '2024-01-15 00:00:00'
        AND event_time <  '2024-01-16 00:00:00'

● MEDIUM D-003 · select_star_on_wide_table
  Found:    SELECT * reads all columns from disk

📚 Relevant docs: uniqExact | ClickHouse Docs (score: 0.90)
Total: 3 findings (2 high, 1 medium)

Why not just use ChatGPT?

Generic AI (ChatGPT, etc.)

Doesn't know your ClickHouse version
Gives outdated advice (CH releases monthly)
Can hallucinate — no formal proof
Your SQL leaves your machine
No version-specific filtering

ClickAdvisor

Version-aware: rules filtered by your CH version
Always current: KB updated weekly from official docs
Formally proven: every rule has mathematical proof
Local-first: zero data egress
Explains WHY each recommendation works

“With every ClickHouse JOIN mechanism change, a bunch of old recommendations became outdated. That's why version-aware tooling matters.”

— Sergey Platonov, ClickHouse DBA Community

How it works

Parse SQL

Your SQL is parsed into an AST using sqlglot with ClickHouse dialect support.

Detect version

Auto-detected via --connect or specified with --ch-version. Rules are filtered per your exact CH version.

Apply rules

21+ formally proven rules and detectors analyze your query. Each rule has a mathematical proof.

Get report

Console, JSON, or Markdown report with before/after examples, relevant docs, and optional EXPLAIN ESTIMATE impact.

Everything you need

Version-Aware Rules

Every rule is tagged with the minimum CH version it applies to. No more outdated recommendations.

Semantic Knowledge Base

8,804 indexed chunks from official docs, Altinity KB, and GitHub releases. Semantic search surfaces the most relevant documentation.

EXPLAIN ESTIMATE

Connect to your cluster and see the actual row reduction: 'Before: 8.4B rows → After: 12M rows (700× reduction)' — from CH's own planner.

MCP Server

Works as an MCP tool inside Claude Desktop, Cursor, and Continue. Just describe the problem in chat — the agent calls ClickAdvisor automatically.

Local-First

Zero data egress. Everything runs on your machine. Critical for banking, telecom, and enterprise compliance.

Explain Mode

Use --mode explain to get educational output: understand WHY ClickHouse behaves differently, not just what to fix.

Get started in seconds

Install and run from your terminal

Install

bash

# Clone and install
git clone https://github.com/olyannaa/clickadvisor.git
cd clickadvisor
poetry install

Index knowledge base (one time)

bash

# Build the knowledge base index (takes ~15 min, one-time)
poetry run chadvisor index-kb

Analyze your query

bash

# Basic analysis
poetry run chadvisor analyze --sql query.sql

# With your ClickHouse version (recommended)
poetry run chadvisor analyze --sql query.sql --ch-version 25.3

# Auto-detect version from your cluster
poetry run chadvisor analyze --sql query.sql \
  --connect http://localhost:8123 \
  --ch-user default --ch-password secret

# With EXPLAIN ESTIMATE (shows real row reduction)
poetry run chadvisor analyze --sql query.sql \
  --connect http://localhost:8123 \
  --explain-estimate

# Educational mode (explains WHY)
poetry run chadvisor analyze --sql query.sql --mode explain

Or use Docker

bash

docker run --rm -v $(pwd):/queries \
  ghcr.io/olyannaa/clickadvisor:latest \
  analyze --sql /queries/query.sql

⭐ Star on GitHub

Built with rigor

1.0

F1 Score on synthetic benchmark

0.61

MRR@3 retrieval quality (2000 docs)

Automated tests

8,804

KB chunks indexed

Evaluated on a curated synthetic benchmark of 20 cases, one per rule. Retrieval MRR@3 measured on 2,000 knowledge base chunks.

Architecture

  ┌──────┐    ┌──────────────┐    ┌─────────────────────────┐
  │ SQL  │──▶ │ sqlglot       │──▶ │ Rule Engine             │
  └──────┘    │  Parser (AST) │    │ Tier 1A · 1B · 1C       │
              └──────────────┘    │ + Detectors             │
                                  └────────────┬────────────┘
                                               │
                                  ┌────────────▼────────────┐
                                  │ Version Filter (CH ver) │
                                  └────────────┬────────────┘
                                               │
                                  ┌────────────▼────────────┐
                                  │ Retrieval Advisor       │
                                  │  Qdrant + embeddings    │
                                  └────────────┬────────────┘
                                               │
                                       ┌───────▼───────┐
                                       │   Report      │
                                       │ console/json/ │
                                       │   markdown    │
                                       └───────────────┘

Stop guessing. Start optimizing.

View on GitHub Read the Docs

Open source · Local-first · MIT License