How SLS Achieves 8× Faster High‑Cardinality GroupBy Queries

01 What Is High‑Cardinality GroupBy

High‑cardinality GroupBy refers to grouping and aggregating data that contains an extremely large number of distinct values, such as ItemId, RequestId, or TraceId, often reaching tens or hundreds of millions.

These scenarios are common in operational analysis, e‑commerce sales distribution, game player behavior tracking, and product performance metrics.

02 GroupBy Implementation Principles

GroupBy is a fundamental OLAP aggregation capability. Distributed engines hash‑partition data across nodes, perform partial aggregation (PartialAgg) on each node, then merge results in a final aggregation (FinalAgg) phase, often using a priority queue for sorting and limiting.

Pre‑aggregation reduces network traffic by aggregating locally before sending data to the final nodes.

The overall process follows four steps: DataSource → PartialAgg → FinalAgg → Output.

03 Experience with SLS High‑Cardinality GroupBy Acceleration

Test data simulated Nginx access logs stored in a Logstore with 5000 CU. Schema:

{
  RequestId: varchar, /* each request ID is globally unique */
  ClientIP: varchar,
  Method: varchar,
  Latency: int,
  Status: int,
  ...
}

Three test cases were designed:

High‑cardinality single‑column aggregation: 2.8 billion rows, GroupBy on RequestId (cardinality 2.8 billion).

High‑cardinality multi‑column aggregation: 4.5 billion rows, GroupBy on ClientIP, Status, Latency (combined cardinality 1.5 billion).

Low‑cardinality value aggregation: 1.5 trillion rows, Top‑100 frequencies on Latency (cardinality 7.35 million).

To avoid cache effects, each query added a non‑existent keyword filter.

Case 1: Single‑Column High‑Cardinality

Baseline (plain SQL) took ~17 s. Enabling Enhanced SQL with set session hash_partition_count=40 reduced time to 10 s. Increasing the partition count to 64, 128, and 200 further lowered latency to 7 s, 4.5 s, and 3.7 s respectively.

Activating set session high_cardinality_agg=true brought the query down to 2.1 s, achieving an 8× speedup.

Case 2: Multi‑Column High‑Cardinality

Baseline took ~24 s. Enhanced SQL with hash_partition_count=40 cut it to 11 s. Raising the partition count to 64/128/200 reduced latency to 7.3 s, 5.9 s, and 5.8 s. Enabling high_cardinality_agg further reduced it to 2.9 s, again an 8× improvement.

Case 3: Low‑Cardinality Value Aggregation

Baseline plain SQL returned in 4.3 s but was truncated. Enhanced SQL produced a precise result in 23.4 s, showing its strength on massive datasets.

Increasing hash_partition_count had little effect because the bottleneck lay in PartialAgg, not FinalAgg. Enabling high_cardinality_agg caused a timeout, confirming that this optimization is unsuitable for low‑cardinality scenarios.

04 Conclusions and Recommendations

SLS’s high‑cardinality GroupBy optimizations can deliver up to 8× faster queries and enable sub‑20‑second responses on trillion‑row datasets.

Recommendations:

Use default mode for modest data volumes.

Enable Enhanced SQL for large datasets with few shards to increase parallelism.

For high‑cardinality workloads, adjust hash_partition_count (recommended 20‑64) and consider high_cardinality_agg based on data distinctness.

For low‑cardinality aggregations, stick with Enhanced SQL without the high‑cardinality flags.

These practices help SLS users achieve optimal performance across diverse analytical scenarios.