Content
# ClickAdvisor
# https://clickadvisor.lovable.app
> Local-first CLI and MCP advisor for ClickHouse query optimization.
> Finds SQL antipatterns, provides formally justified rewrite recommendations, supplements them with retrieval context from KB, and estimates impact via `EXPLAIN ESTIMATE`.
- Recommendations are tied to `rule_id`, tier, and ClickHouse version;
- Tier `1A/1B/1C` clearly separates formal rewrite rules from conditional and approximate ones;
- Retrieval advisory adds documentation links but does not replace the rule engine;
- `EXPLAIN ESTIMATE` can show estimated row/mark reduction without `ANALYZE` and user data reads;
- MCP server allows calling the same local analysis from Claude Desktop, Cursor, Continue, and other agents.
## v1.0 Capabilities
- 21+ rules and detectors for ClickHouse SQL (`R-001`…`R-018`, `D-003`, `D-004`, `D-007`)
- Version-aware filtering by `--ch-version` or auto-detection via `--connect`
- Console / JSON / Markdown reports
- `--mode explain` with educational explanations
- Retrieval-based advisory via embedded Qdrant KB
- Embedding model selection during KB indexing
- Optional `EXPLAIN ESTIMATE` impact summaries
- Stdio MCP server with tools and prompts
- Synthetic benchmark and ablation experiment for embedding models
## Quick Start
### From Source
```bash
git clone https://github.com/olyannaa/clickadvisor.git
cd clickadvisor
poetry install
poetry run chadvisor analyze --sql examples/bad_query.sql
```
### pip
```bash
pip install clickadvisor # when package is published
chadvisor analyze --sql examples/bad_query.sql
```
### Docker
```bash
docker run --rm -v $(pwd):/queries \
ghcr.io/username/clickadvisor:latest \
analyze --sql /queries/query.sql
```
## CLI Usage
### Basic Analysis
```bash
poetry run chadvisor analyze --sql query.sql
```
### With ClickHouse Version Specified
```bash
poetry run chadvisor analyze --sql query.sql --ch-version 25.3
```
### Auto-Detect ClickHouse Version via HTTP API
```bash
poetry run chadvisor analyze --sql query.sql \
--connect http://host:8123 \
--ch-user default \
--ch-password secret
```
### Explanation Mode
```bash
poetry run chadvisor analyze --sql query.sql --mode explain
```
### Output Formats
```bash
poetry run chadvisor analyze --sql query.sql --output-format json
poetry run chadvisor analyze --sql query.sql --output-format markdown
```
### EXPLAIN ESTIMATE Impact
If `--connect` is specified, ClickAdvisor can execute `EXPLAIN ESTIMATE` for the original SQL and rule rewrite (`example_after`) and add an `📊 Impact` line:
```bash
poetry run chadvisor analyze --sql query.sql \
--connect http://localhost:8123 \
--explain-estimate
```
This does not run `ANALYZE` or read user data; it uses the ClickHouse planner's estimate (`rows`, `marks`).
## Retrieval Advisory KB
KB indexing creates an embedded Qdrant database `.qdrant_db` from `data/kb/chunks/`:
```bash
poetry run chadvisor index-kb
```
Re-indexing:
```bash
poetry run chadvisor index-kb --reindex
```
Embedding model selection:
```bash
poetry run chadvisor index-kb --embedding-model multilingual-e5-small
poetry run chadvisor index-kb --embedding-model minilm-l6
```
Available models:
| Key | Model | Size | Notes |
|---|---|---:|---|
| `multilingual-e5-small` | `intfloat/multilingual-e5-small` | 117 MB | default, multilingual, E5 prefixes |
| `minilm-l6` | `sentence-transformers/all-MiniLM-L6-v2` | 80 MB | english-only, faster, best MRR@3 on current English KB |
With `.qdrant_db`, the `analyze` command adds RAG findings in a separate section `📚 Relevant Documentation` by default. Management:
```bash
poetry run chadvisor analyze --sql query.sql --retrieval
poetry run chadvisor analyze --sql query.sql --no-retrieval
```
## MCP Server
ClickAdvisor provides a stdio MCP server:
```bash
poetry run chadvisor mcp-server
```
Detailed instructions for Claude Desktop, Cursor, Continue, and other clients: [`docs/MCP.md`](docs/MCP.md).
MCP tools:
- `analyze_query` — Markdown report for SQL
- `analyze_query_json` — structured JSON without RAG findings
- `list_rules` — list of rules
- `detect_ch_version` — ClickHouse version detection via HTTP API
MCP prompts:
- `analyze`
- `explain`
## Rules and Coverage
| Rule ID | Description | Tier |
|---|---|---|
| `R-001` | `COUNT(DISTINCT x)` → `uniqExact(x)` | `1A` |
| `R-002` | `COUNT(DISTINCT x)` → advisory `uniq(x)` | `1B` |
| `R-003` | `quantileExact(...)` → advisory `quantileTDigest(...)` | `1B` |
| `R-004` | `COUNT(*) FROM (SELECT DISTINCT ...)` collapse | `1A` |
| `R-005` | `toDate(col) = ...` → datetime range | `1A` |
| `R-006` | `toYYYYMM(...)` / `toStartOfMonth(...)` → range | `1A` |
| `R-007` | `toStartOfHour/Day/FifteenMinutes(...)` → range | `1A` |
| `R-008` | redundant `CAST(...)` in filter | `1C` |
| `R-009` | singleton `IN (...)` → equality | `1A` |
| `R-010` | `col = a OR col = b OR col = c` → `IN (...)` | `1A` |
| `R-011` | non-aggregate predicate in `HAVING` → `WHERE` | `1C` |
| `R-012` | constant predicate elimination | `1A` |
| `R-013` | `length(x) = 0 / > 0 / != 0` → `empty/notEmpty` | `1A` |
| `R-014` | advisory hash-based `GROUP BY` for long strings | `1B` |
| `R-015` | `DISTINCT` after equivalent `GROUP BY` removal | `1A` |
| `R-016` | `ORDER BY` in subquery without `LIMIT` | `1C` |
| `R-017` | subquery filter pushdown | `1A` |
| `R-018` | advisory `UNION` → `UNION ALL` | `1C` |
| `D-003` | top-level `SELECT *` detector | `detector` |
| `D-004` | missing `LIMIT` on non-aggregate top-level select | `detector` |
| `D-007` | costly `FINAL` modifier detector | `detector` |
## Quality Metrics
Synthetic benchmark:
```bash
poetry run python scripts/eval/run_benchmark.py
```
Current curated synthetic benchmark (`lenient` mode): precision/recall/F1 are expected to remain `1.00` for rule detection.
Embedding ablation:
```bash
poetry run python scripts/eval/ablation_embeddings.py
```
Latest 2000-chunk ablation reports MRR@3 over synthetic cases and prints a recommendation. On the English-heavy KB, `all-MiniLM-L6-v2` had the best MRR@3, but the default remains multilingual E5; see [`docs/adr/ADR-013-embedding-model-selection.md`](docs/adr/ADR-013-embedding-model-selection.md).
## Architecture
```text
SQL + Schema + optional EXPLAIN / CH version
↓
SQL Parser (sqlglot)
↓
┌──────────────────┐
│ Rule Engine │
│ ├─ Tier 1A: formally equivalent rewrites
│ ├─ Tier 1B: approximate / opt-in guidance
│ ├─ Tier 1C: conditional rewrites
│ └─ Detectors: antipattern detection
└──────────────────┘
↓
Version Filter + optional EXPLAIN ESTIMATE comparator
↓
optional Retrieval Advisor (Qdrant + embeddings)
↓
Report (console | JSON | Markdown | MCP)
```
## Development
```bash
poetry install
poetry run pytest -k 'not test_detect_version'
poetry run ruff check clickadvisor tests scripts
poetry run python scripts/eval/run_benchmark.py
```
`tests/integration/test_version.py::test_detect_version` expects an available ClickHouse HTTP endpoint on `localhost:8123` with suitable credentials.
