Investigate using approx_top_count for other high-cardinality queries

[trieloff]

Background

In PR #116 (lambda enhancements), we solved an OOM issue on high-cardinality breakdown refinement queries by using ClickHouse's approx_top_count function. The technique uses bounded memory to find top-N candidates, then computes exact counts via an IN filter, with a UNION ALL to get full-dataset totals.

The query pattern:

WITH top_dims AS (
  SELECT tupleElement(pair, 1) AS dim
  FROM (
    SELECT arrayJoin(approx_top_count(N)(col)) AS pair
    FROM table WHERE ...
  )
)
SELECT col AS dim, count() AS cnt, ...
FROM table WHERE ... AND col IN (SELECT dim FROM top_dims)
GROUP BY dim ORDER BY cnt DESC LIMIT N
UNION ALL
SELECT '' AS dim, count() AS cnt, ...
FROM table WHERE ...

This ran in ~4s on 244M rows with 83M unique message values, vs the regular GROUP BY which OOMed at the 4GB memory limit.

Investigation Areas

• Main CDN dashboard high-cardinality breakdowns: The main dashboard has high-cardinality facets (hosts, URLs, user agents, client IPs, referers, forwarded hosts, redirect locations) that currently use sampling for both initial and refinement passes. Could approx_top_count replace the refinement sampling for these too, giving exact counts without OOM risk?

• Facet search: facet-search-initial.sql and facet-search-pattern.sql do GROUP BY queries that could benefit from bounded-memory top-N for very high-cardinality columns.

• Log investigation queries: investigate-facet.sql does GROUP BY on arbitrary facets — high-cardinality ones could benefit.

• Preview breakdowns: Time range selection preview queries currently use sampling. Could approx_top_count provide better accuracy for short preview windows?

Current Implementation

• SQL template: sql/queries/breakdown-approx-top.sql
• Routing logic: isApproxTopRefinement() in js/breakdowns/index.js
• Totals extraction: extractApproxTopTotals() in js/breakdowns/index.js
• Tests: js/breakdowns/approx-top.test.js

[langswei]

This is interesting and I have a suggestion based on some lab work.

Per the documentation:

If performing inner joins, it is often more optimal to write these as sub-queries using the IN clause.

On the contrary, for our situation I found INNER JOIN to take about half as long as a subquery. Note the commented -- lines in each:

subquery 0.990s

WITH top_dims AS (
  SELECT tupleElement(pair, 1) AS dim
  FROM (
    SELECT arrayJoin(approx_top_count(5)(message)) AS pair
    FROM helix_logs_production.lambda_logs
    WHERE toStartOfMinute(timestamp) BETWEEN toStartOfMinute(toDateTime('2026-02-18 18:25:00')) AND toStartOfMinute(toDateTime('2026-02-19 18:25:00'))
  )
)
SELECT message AS dim, count() as cnt,
      countIf(level NOT IN ('ERROR', 'WARN', 'WARNING')) as cnt_ok,
      countIf(level IN ('WARN', 'WARNING')) as cnt_4xx,
      countIf(level = 'ERROR') as cnt_5xx
FROM helix_logs_production.lambda_logs
--INNER JOIN top_dims ON top_dims.dim = lambda_logs.message
WHERE toStartOfMinute(timestamp) BETWEEN toStartOfMinute(toDateTime('2026-02-18 18:25:00')) AND toStartOfMinute(toDateTime('2026-02-19 18:25:00'))
  AND message IN (SELECT dim FROM top_dims)
GROUP BY dim
ORDER BY cnt DESC
LIMIT 5
UNION ALL
SELECT '' AS dim,  count() as cnt,
      countIf(level NOT IN ('ERROR', 'WARN', 'WARNING')) as cnt_ok,
      countIf(level IN ('WARN', 'WARNING')) as cnt_4xx,
      countIf(level = 'ERROR') as cnt_5xx
FROM helix_logs_production.lambda_logs
WHERE toStartOfMinute(timestamp) BETWEEN toStartOfMinute(toDateTime('2026-02-18 18:25:00')) AND toStartOfMinute(toDateTime('2026-02-19 18:25:00'))

INNER JOIN 0.413s

WITH top_dims AS (
  SELECT tupleElement(pair, 1) AS dim
  FROM (
    SELECT arrayJoin(approx_top_count(5)(message)) AS pair
    FROM helix_logs_production.lambda_logs
    WHERE toStartOfMinute(timestamp) BETWEEN toStartOfMinute(toDateTime('2026-02-18 18:25:00')) AND toStartOfMinute(toDateTime('2026-02-19 18:25:00'))
  )
)
SELECT message AS dim, count() as cnt,
      countIf(level NOT IN ('ERROR', 'WARN', 'WARNING')) as cnt_ok,
      countIf(level IN ('WARN', 'WARNING')) as cnt_4xx,
      countIf(level = 'ERROR') as cnt_5xx
FROM helix_logs_production.lambda_logs
INNER JOIN top_dims ON top_dims.dim = lambda_logs.message
WHERE toStartOfMinute(timestamp) BETWEEN toStartOfMinute(toDateTime('2026-02-18 18:25:00')) AND toStartOfMinute(toDateTime('2026-02-19 18:25:00'))
--  AND message IN (SELECT dim FROM top_dims)
GROUP BY dim
ORDER BY cnt DESC
LIMIT 5
UNION ALL
SELECT '' AS dim,  count() as cnt,
      countIf(level NOT IN ('ERROR', 'WARN', 'WARNING')) as cnt_ok,
      countIf(level IN ('WARN', 'WARNING')) as cnt_4xx,
      countIf(level = 'ERROR') as cnt_5xx
FROM helix_logs_production.lambda_logs
WHERE toStartOfMinute(timestamp) BETWEEN toStartOfMinute(toDateTime('2026-02-18 18:25:00')) AND toStartOfMinute(toDateTime('2026-02-19 18:25:00'))

So I believe we could make this faster by changing https://github.com/adobe/klickhaus/blob/main/sql/queries/breakdown-approx-top.sql however in my investigation, despite the commentary, I did not see a single query in my browser using the syntax starting with WITH top_dims AS so I'm not certain the high cardinality logic is in place right now.