From Single to Multiple Attributes: Experimental Insights on Sampling-Based Distinct Combination Estimation in GROUP-BY Queries
2026-07-01 • Databases
Databases
AI summaryⓘ
The authors studied how to estimate the number of unique groups when running database queries that group data by multiple attributes, which is a hard problem. They point out that existing methods mostly focus on simpler queries or grouping by just one attribute, and it's unclear how well current approaches work for more complex cases. To investigate, they created a tool to generate different types of group-by queries and tested various methods on real datasets and benchmarks. Their results reveal common mistakes in estimations and how these errors affect query planning in PostgreSQL. They conclude with advice for designing better estimators in the future.
cardinality estimationGROUP-BY queriesmulti-attribute estimationdistinct value estimationsampling-based methodslearned modelsquery optimizationTPC-H benchmarkPostgreSQLquery planning
Authors
Yujie Zhang, Xiaochun Yang, Bin Wang, Yuan Sui
Abstract
Estimating the number of distinct combinations in multi-attribute GROUP-BY queries remains a significant yet underexplored challenge. Current cardinality estimation techniques primarily focus on SPJ queries (i.e., selections, projections, and joins) and neglect GROUP-BY operations; meanwhile, distinct value estimation research has mainly targeted the single-attribute setting. Although sampling-based methods, including recent approaches with learned models, can theoretically support multi-attribute estimation, their practical effectiveness remains unclear. A comprehensive empirical evaluation is thus lacking to address whether joint distribution information from samples alone is sufficient for accurate multi-attribute estimation, whether existing methods fully exploit single-attribute information and can be further optimized, and whether filtered GROUP-BY queries can be accurately estimated. To this end, we propose a specialized workload generator for multi-attribute GROUP-BY queries and generate both filtered and non-filtered queries over four real-world datasets. By evaluating existing methods across synthetic workloads and the multi-table TPC-H benchmark, we analyze the sources of GROUP-BY cardinality estimation errors and their impact on PostgreSQL's plan selection, offering key recommendations for future estimator design.