From Single to Multiple Attributes: Experimental Insights on Sampling-Based Distinct Combination Estimation in GROUP-BY Queries

2026-07-01

AI Summary

Existing cardinality estimation methods primarily target SPJ queries and struggle to accurately estimate the number of distinct value combinations in multi-attribute GROUP-BY queries, particularly under filtering conditions. This work presents the first systematic evaluation of various sampling-based approaches for this task. By constructing a dedicated workload generator, we produce both filtered and unfiltered queries on four real-world datasets and the TPC-H benchmark, enabling a thorough analysis of how these methods model joint attribute distributions, leverage single-attribute statistics, and influence PostgreSQL query execution plans. Our study uncovers that the primary source of estimation error lies in the neglect of multi-attribute joint distributions and provides empirical insights and concrete recommendations to guide the design of future high-accuracy cardinality estimators for GROUP-BY queries.

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.

Problem

• multi-attribute GROUP-BY
• distinct combination estimation
• cardinality estimation
• sampling-based methods
• query optimization

Innovation

• multi-attribute GROUP-BY
• distinct combination estimation
• sampling-based cardinality estimation
• workload generator
• query optimization