Slow Query Patterns Fields

Operation: slowQueryPatternsGetList (tag: Postgres). Beta.

Before constructing a request, follow openapi-discovery.md to resolve the current path, required query params, and response schema. The reference snapshot below documents what May 2026 looked like; the live spec is authoritative.

Reference path (May 2026 snapshot):

Auth: HTTP Basic with a ClickHouse Cloud API key (username) and secret (password).

Required query params

The slow-query endpoint requires a time window:

• from_date — ISO 8601 UTC date-time.
• to_date — ISO 8601 UTC date-time.

For RCA, default to the last 15 minutes.

Useful optional query params

• sort_by — sort key. Reference values from the May 2026 snapshot: total_duration (default), avg_duration, call_count, total_blks_read, total_cpu_time, error_count, max_duration, p50_duration, p95_duration, p99_duration, total_rows, total_shared_blks_hit, total_wal_bytes. Confirm enum values from the live spec.
• sort_order — default desc.
• limit — default 20, max 500.
• db_name, db_user, db_operation, app — filters.

Request template

The API requires millisecond precision on the date-time strings (.000Z). RFC 3339 strings without milliseconds (e.g., 2026-05-29T10:00:00Z) are rejected with HTTP 400 even though the spec just says format: date-time. Use the format below:

If a request returns HTTP 400 with body BAD_REQUEST: '<your date>', the parser rejected that specific value — verify millisecond precision and the Z suffix.

Response envelope

Standard ClickHouse Cloud envelope:

Field roles (from openapi-discovery.md)

Use the role map you built during discovery. The roles you need for the heuristics:

• query_id — pattern identifier.
• query_text — normalized SQL.
• db_operation — SELECT / INSERT / UPDATE / DELETE / UTILITY.
• call_count — executions in the window.
• total_duration — aggregate runtime.
• avg_duration — mean per-call latency.
• p50, p95, p99 — percentile latencies.
• total_rows — rows returned/affected across all calls.
• blocks_read_from_disk — pages read from disk (cache misses).
• blocks_served_from_cache — pages served from cache.
• total_wal_bytes — WAL bytes generated.
• error_count — failed executions.

When citing values in your reasoning, name the resolved field (e.g., totalSharedBlksRead in the May 2026 snapshot), not the role.

Derived values

The API does not return a cache hit ratio. Compute:

Per-call IO ratio for the full-scan heuristic:

Use total blocks touched (hit + read), not just disk reads. A hot table fully resident in cache still produces a high blks_touched_per_row if every call scans it.

Expect ClickHouse Cloud internal probes in the top-N

The control plane runs its own monitoring queries against managed Postgres instances — SELECT pg_current_wal_lsn(), SELECT pg_is_in_recovery(), SHOW log_directory, and a handful of similar admin probes. They appear in the Slow Query Patterns response with high callCount (one per probe interval) but totalDurationUs ≈ 0 and zero IO. They don’t affect the diagnosis but waste top-N slots.

Two-step filter:

1. Ask for limit=10 (or higher) on the request so the user-traffic patterns survive even if internal probes fill the top slots.
2. Post-filter the response — skip any pattern where totalDurationUs is below, say, 1,000,000 (1s aggregate over the window). Real user-traffic patterns will always clear that threshold; internal probes won’t.

Then take the top 3 by totalDurationUs from what remains.

The app filter param accepts equality only (no app != bin/monitor), so server-side filtering doesn’t work for “exclude internal probes.” Post-filter is the path.

What this surface does NOT show

• No EXPLAIN plans.
• No scan-type counters.
• No table or column statistics.
• The list is filtered server-side to “slow” patterns — there may be other patterns the API doesn’t surface.

Reason from the IO and timing signal, not the plan tree.