SQL performance and troubleshooting

GraphJSON exposes ClickHouse SQL over event collections, but the notebook is still an interactive product surface. A query that scans unnecessary history, repeatedly parses JSON, or builds a huge join can be logically correct and operationally unsuitable.

Use this guide when a query is slow, fails, returns no rows, exceeds the result limit, or produces a chart that does not match the table.

Know the boundaries#

SQL is read-only; mutations are blocked.
Results receive an outer limit of 2,000 rows.
The notebook waits up to 15 minutes for a query job.
JSON properties are extracted at query time.
Wide time ranges, high-cardinality grouping, and large joins increase work.
GraphJSON may throttle sustained rates of unusually expensive unique queries.

A 15-minute maximum is a failure boundary, not a target. Dashboard and embed queries should normally be much faster.

Start with the smallest proof#

When a complex query fails, reduce it:

SELECT timestamp, json
FROM product_events
WHERE timestamp >= now() - INTERVAL 1 HOUR
LIMIT 10

Then add one step at a time:

event filter
extracted fields
grouping
aggregation
join
window function
final ordering

Run after each step. The first failing step identifies the relevant class of problem.

Filter time and event name early#

Poor:

SELECT
  JSONExtractString(json, 'plan') AS plan,
  count()
FROM product_events
GROUP BY plan

Better:

SELECT
  JSONExtractString(json, 'plan') AS plan,
  count() AS signups
FROM product_events
WHERE
  timestamp >= now() - INTERVAL 30 DAY
  AND JSONExtractString(json, 'event') = 'signup_completed'
GROUP BY plan
ORDER BY signups DESC

Always decide whether “all retained history” is genuinely required.

Extract once#

For a field used repeatedly, extract it in a CTE:

WITH events AS (
  SELECT
    timestamp,
    JSONExtractString(json, 'event') AS event,
    JSONExtractString(json, 'account_id') AS account_id,
    JSONExtractString(json, 'plan') AS plan
  FROM product_events
  WHERE timestamp >= now() - INTERVAL 30 DAY
)
SELECT
  plan,
  uniqExact(account_id) AS active_accounts
FROM events
WHERE event = 'feature_used'
GROUP BY plan
ORDER BY active_accounts DESC

This improves readability and reduces the chance that two copies use different paths or types. Whether the query engine eliminates every repeated expression is an implementation detail; the clearer contract is still valuable.

Select only required columns#

Avoid SELECT * in production notebooks. It:

returns large JSON payloads
makes table results noisy
couples the query to future columns
can hide which fields the metric actually needs

Name the result contract explicitly.

Control cardinality#

High-cardinality fields include:

user and account IDs
request IDs
raw URLs
free-form errors
timestamps
search text

Grouping by one can produce more than 2,000 rows and create unreadable charts.

Choose one:

filter to a known entity
normalize the property upstream
group by a stable category
return only the top values
aggregate at a coarser grain

Example:

SELECT
  JSONExtractString(json, 'route_template') AS route,
  count() AS requests
FROM api_events
WHERE
  JSONExtractString(json, 'event') = 'api_request_completed'
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY route
ORDER BY requests DESC
LIMIT 50

Use /accounts/:id rather than raw /accounts/acct_123.

Aggregate before joining#

Poor:

SELECT ...
FROM product_events p
JOIN billing_events b
  ON JSONExtractString(p.json, 'account_id') =
     JSONExtractString(b.json, 'account_id')

This can join every product event for an account to every billing event for the same account.

Better:

WITH activity AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    count() AS product_events
  FROM product_events
  WHERE
    timestamp >= now() - INTERVAL 30 DAY
    AND JSONExtractString(json, 'account_id') != ''
  GROUP BY account_id
),
plans AS (
  SELECT
    JSONExtractString(json, 'account_id') AS account_id,
    argMax(
      JSONExtractString(json, 'plan'),
      timestamp
    ) AS current_plan
  FROM billing_events
  WHERE JSONExtractString(json, 'account_id') != ''
  GROUP BY account_id
)
SELECT
  plans.current_plan,
  sum(activity.product_events) AS product_events
FROM activity
INNER JOIN plans USING account_id
GROUP BY plans.current_plan
ORDER BY product_events DESC

Each side becomes one row per join key before the join.

Use exact distinct counts deliberately#

uniqExact is useful when exact unique entities matter. Exact sets can consume more work than approximate distinct functions on very large populations.

For business-critical counts within ordinary product datasets, exactness may be the right choice. For exploratory high-volume telemetry, decide whether an approximate result is acceptable and document the choice.

Never switch to an approximate function merely to make a slow dashboard pass without telling readers that the definition changed.

Keep percentiles scoped#

Percentiles are valuable for latency and size distributions. Filter to:

relevant event
time window
route or service when appropriate
valid numeric values

SELECT
  quantile(0.95)(
    JSONExtractFloat(json, 'duration_ms')
  ) AS p95_duration_ms
FROM api_events
WHERE
  JSONExtractString(json, 'event') = 'api_request_completed'
  AND timestamp >= now() - INTERVAL 24 HOUR

Do not calculate a percentile across unrelated endpoints and then label it API latency without explaining the population.

Return a chart-ready shape#

Time series#

Return one row per bucket:

SELECT
  toUnixTimestamp(toStartOfHour(toDateTime(timestamp))) AS hour,
  count() AS failures
FROM webhook_events
WHERE
  JSONExtractString(json, 'event') = 'webhook_delivery_completed'
  AND JSONExtractInt(json, 'status_code') >= 500
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY hour
ORDER BY hour

Category chart#

Return one label and value per row:

SELECT
  JSONExtractString(json, 'provider') AS provider,
  count() AS failures
FROM webhook_events
WHERE
  JSONExtractInt(json, 'status_code') >= 500
  AND timestamp >= now() - INTERVAL 7 DAY
GROUP BY provider
ORDER BY failures DESC

If the table is correct and the chart is wrong, check the notebook’s x-axis, value, label, and split mappings.

Diagnose empty results#

Check in this order:

collection name and case
time range and timestamp units
event name and case
field path
value type
empty string versus missing value
environment
retention

Inspect Samples, then run:

SELECT
  JSONExtractString(json, 'event') AS event,
  count() AS rows
FROM product_events
WHERE timestamp >= now() - INTERVAL 7 DAY
GROUP BY event
ORDER BY rows DESC
LIMIT 100

This often reveals a naming or environment mismatch.

Diagnose type errors#

GraphJSON commonly uses:

JSONExtractString
JSONExtractInt
JSONExtractFloat
JSONExtractBool

Use the function that matches the producer contract. If the same property arrives as multiple types, repair the producer and isolate the affected interval. Avoid a growing tower of silent coercions.

Money should be a numeric minor-unit field plus currency. Timestamps used as event time belong in the request’s Unix-seconds timestamp.

Diagnose duplicate or inflated totals#

Check:

at-least-once retries
joins that multiply rows
multiple state-change events per object
multiple users counted as accounts
overlapping categories
a backfill overlapping live delivery

Count stable IDs:

SELECT
  count() AS rows,
  uniqExact(JSONExtractString(json, 'event_id')) AS unique_events
FROM product_events
WHERE
  JSONExtractString(json, 'event') = 'invoice_paid'
  AND timestamp >= now() - INTERVAL 30 DAY

If rows exceed unique events, follow Deduplicate mutable business events.

Query review checklist#

Time range is bounded.
Event filters are applied before expensive work.
Repeated JSON fields are named once.
Only required columns are selected.
High-cardinality groups are bounded.
Both sides are aggregated before large joins.
Result stays below 2,000 rows.
Chart mappings match result types.
Money and time units are documented.
Exact versus approximate calculations are intentional.
The query description states owner and exclusions.

For a query still exceeding interactive expectations, send GraphJSON the redacted SQL shape, collection sizes, time range, and approximate runtime. Never include the API key or customer payloads.