Same query, different work: ClickHouse Cloud vs StarTree Cloud

Both systems can prune. The difference is how precisely they isolate the matching data, how that holds up as new query patterns appear, and what it costs to hold a latency SLO as traffic grows.

2.36M rows · 12 days CH: daily parts · ORDER BY (category, event_time) ST: daily segments · time routing + indexes granule = 8,192 rows dot = 512 matching rows

Query

Both systems reduce work early. ClickHouse's primary and many skip-index paths select candidate data at granule boundaries (default ~8,192 rows each), while projections can add alternate physical access paths. StarTree prunes segments at the Broker using time/partition metadata, then uses fine-grained indexes inside the routed segments to isolate matching rows. The difference is modest when a query aligns with the physical design, becomes more visible as new query patterns appear, and turns into hardware when residual work repeats at production QPS under a fixed SLO.

Illustrative model assumes the same modeled CPU cost per candidate row across both systems and both query shapes, so CPU work scales directly with candidate rows left after pruning. It isolates pruning precision, not operator or per-row execution efficiency. Capacity values are explanatory, not benchmark results.

About this model

Sample table. events(event_time, customer_id, category, amount), where customer_id is high-cardinality. ClickHouse: daily parts, ORDER BY (category, event_time). StarTree: daily segments with time routing and per-column indexes. 12 days × 24 granules a day × 8,192 rows = about 2.36M transactions (synthetic, the same every run). The query window is the last 7 days: 7 × 24 = 168 granules. For simplicity, the animation assumes the 7-day window is aligned to daily partition boundaries.
Dashboard rollup. On the ClickHouse side, rows are sorted by category, so each day’s electronics rows sit together. They fill 3 granules’ worth of rows but straddle 4 granules (half of one, two full, half of another). ClickHouse reads the 4: 7 × 4 × 8,192 = 229,376 rows. StarTree’s inverted index on category and range index on event_time return only the electronics rows, without relying on that sort order: 7 × 3 × 8,192 = 172,032 rows.
Customer lookup. The same customer-lookup query used in the paper, adapted to this smaller illustrative table. Customer 734521 has 5 transactions on 5 different days. Bloom filter (GRANULARITY 1 in this example): the 5 granules that contain a match + 4 assumed false positives (an illustrative ~2.5% false-positive outcome) = 9 × 8,192 = 73,728 rows. Projection by customer_id: one granule per matching day = 5 × 8,192 = 40,960 rows. StarTree: the 5 rows.
Scale. A small sample, so counts are smaller than the paper’s. The paper’s larger example narrows 4,800 candidate granules to 12, or roughly 98K rows of granularity, before resolving five matches.
Capacity. 90% lookups, 10% rollups. Average candidate rows per query: ClickHouse with a bloom filter 0.9 × 73,728 + 0.1 × 229,376 = 89,293; StarTree 0.9 × 5 + 0.1 × 172,032 = 17,208, about 5.2×. CPU demand scales with those rows, at the same modeled CPU cost per candidate row across both systems and both query shapes, intentionally isolating pruning precision rather than operator or per-row execution efficiency: StarTree’s mix is set to 7.2 CPU-ms per query (45% of one 16-core node at 1,000 QPS), so ClickHouse’s is about 37.4 CPU-ms. For illustration, the capacity model uses a 70% operating limit to preserve headroom under a fixed p99 SLO, keeping 30% for bursts and background work, and sizes nodes to that limit. It does not predict latency; the right limit is workload-specific.
Not charged. Index-analysis and index build/maintenance costs are excluded for both systems; the model isolates the candidate-row work after pruning. Both systems pay to build and maintain the additional access structures shown here.

What the animation shows