_categorical_histogram runs a full value-counts over every distinct value to return 10 rows (~1GB transient per high-cardinality column)

[paddymul]

Summary

_categorical_histogram (xorq_stats_v2.py:245) computes

expr.group_by(col).aggregate(__count=...).order_by(desc('__count')).limit(10)

— a full value-counts over every distinct value of the column, hashed and sorted, to return 10 rows. There is no cardinality guard, and every non-numeric column (string, date, datetime) takes this branch, one query per column.

On a real 131,666,457-row table whose plate column has 6,182,193 distinct values (datafusion backend, fresh process, peak via ru_maxrss): the standalone top-10 query peaks at 841MB (0.82s). Through the pipeline (XorqDfStatsV2, histogram + its batch dependencies, that column only) the probe peaks at 4,035MB. Low-cardinality columns on the same table stay at ~270–290MB.

Within one /load_expr these per-column histogram queries stack on top of the batch aggregate (see #906) into a multi-GB transient: 5.2GB observed for the 4-column table above.

The cases split by cardinality:

• distinct_per ≈ 1.0 (ID-like columns): every count is 1; the top-10 is meaningless, yet it costs a full multi-million-group hash + sort.
• high-but-meaningful cardinality (the 6.2M-distinct plate column): the top-10 is informative, but exact counts over the full table are not needed for a display histogram.

Repro

python -c "
import numpy as np, pyarrow as pa, pyarrow.compute as pc, pyarrow.parquet as pq
rng = np.random.default_rng(0)
plates = pc.cast(pa.array(rng.integers(0, 6_000_000, size=30_000_000)), pa.string())
pq.write_table(pa.table({'plate': plates}), '/tmp/plates.parquet')
"
python -c "
import resource, time
import xorq.api as xo
expr = xo.deferred_read_parquet('/tmp/plates.parquet')
q = expr.group_by('plate').aggregate(c=lambda t: t.count()).order_by(xo.desc('c')).limit(10)
t0 = time.monotonic(); df = q.execute()
peak = resource.getrusage(resource.RUSAGE_SELF).ru_maxrss / 2**20  # bytes on macOS; use /2**10 on Linux
print(f'{time.monotonic()-t0:5.2f}s peak={peak:6.0f}MB rows={len(df)}')
"

Output (30M rows, ≤6M distinct)

 0.22s peak=   949MB rows=10

Suggested fixes

1. Cardinality guard: distinct_count is already computed in the batch phase, so when distinct_count/length is near 1.0, skip the query entirely and render the "all unique" treatment.
2. For the remaining high-cardinality columns, compute the top-10 from a sample (a 1–5% sample identifies heavy hitters fine for a display histogram) or an approximate top-k if the backend grows one.

Context

Found while replaying a tallyman catalog (131.7M-row parking-violations union) and attributing the buckaroo server's per-/load_expr memory spike stat by stat. Companion issue for the batch-aggregate side: #906. buckaroo 0.14.17, xorq 0.3.26 (xorq-datafusion 0.2.7), Python 3.13, macOS arm64.