chmonitorQuery Insights API
Programmatic access to slow query patterns — normalized_query_hash aggregation, calls, duration percentiles, and per-pattern executions.
Read the same normalized query-pattern data the Slow Query Patterns page shows, from two REST endpoints — a list of patterns and a per-pattern detail with recent executions.
Prop
Type
What it does
system.query_log rows are aggregated by normalized_query_hash (mirroring ClickHouse Cloud's Query Insights "Slow Patterns" table) — one row per distinct query shape, with call counts, duration percentiles (p50/p95/p99), resource usage (memory, rows/bytes read, rows/bytes written), and error counts. The list endpoint returns this aggregation; the detail endpoint returns one pattern plus the individual system.query_log rows (executions) that contributed to it, most-recent first.
Both endpoints reuse the exact SQL the Slow Query Patterns page runs (buildQueryPatternsSql in lib/query-config/queries/slow-query-patterns.ts) — the numbers always match what the page shows.
Pages
| Page | Route | What it shows | System tables |
|---|---|---|---|
| Slow Query Patterns | /slow-query-patterns | The same pattern aggregation, with filtering; clicking a row opens a detail flyout (aggregate stats, recent executions, notable runs, an Explain/Explorer shortcut, and an Advisor recommendation for that pattern) | system.query_log |
| Recent Queries | /recent-queries | Reverse-chronological, non-aggregated system.query_log — the per-execution counterpart to Slow Query Patterns, sharing the same time/user/query-kind/database/client filters | system.query_log |
Using it
List patterns
GET /api/v1/insights/query-patterns?hostId=0&range=24&sort=total_duration:descProp
Type
{
"success": true,
"data": [
{
"normalized_query_hash": "1234567890123456789",
"normalized_query": "SELECT * FROM t WHERE id = ?",
"calls": 42,
"total_duration": 12.5,
"avg_duration": 0.3,
"p50_duration": 0.25,
"p95_duration": 0.8,
"p99_duration": 1.1,
"max_duration": 1.4,
"errors": 0,
"user": "default",
"query_kind": "Select",
"database": "default"
}
],
"metadata": { "queryId": "...", "duration": 45, "rows": 1, "host": "0", "sql": "..." }
}Get one pattern + its recent executions
GET /api/v1/insights/query-patterns/1234567890123456789?hostId=0&range=24Prop
Type
{
"success": true,
"data": {
"pattern": { "normalized_query_hash": "1234567890123456789", "calls": 42, "total_duration": 12.5 },
"executions": [
{ "event_time": "2026-07-04 10:00:00", "query_id": "...", "query_duration_ms": 310, "user": "default" }
],
"notable": [
{ "event_time": "2026-07-03 22:00:00", "query_id": "...", "query_duration_ms": 4200, "reason": "slowest" }
]
},
"metadata": { "host": "0", "rangeHours": 24, "rows": 1 }
}executions is capped at 200 rows, most-recent first. notable is a separate,
server-ranked query (5 rows per category: slowest by duration, largest_result
by result rows, errored by exception, most-recent first) over the full
rangeHours window — not a client-side slice of executions — so it stays
correct for patterns with more than 200 executions in the window.
MCP / programmatic consumption
Both endpoints return plain JSON ({ success, data, metadata } / error { success: false, error: { type, message } }) with no HTML or client-only shaping, so they can be called directly by scripts, the AI agent's query tool (as system.query_log SQL), or any external MCP client that talks to /api/mcp — the AI agent itself has no dedicated tool for these endpoints (it reaches the same data with the general-purpose query tool), but the endpoints are safe to call from outside the dashboard.
Notes & limitations
Requires query logging
All data comes from system.query_log. If query logging is disabled, both endpoints return an empty pattern list / 404s.
normalized_query_hashvalues are ClickHouseUInt64— always compare/pass them as strings to avoid JS float precision loss.- The
rangewindow is capped at 90 days (2160 hours) to keep the underlyingsystem.query_logscan bounded.