Extensive Large-Scale Study of Error in Samping-Based Distinct Value Estimators for Databases

The problem of distinct value estimation has many applications. Being a critical component of query optimizers in databases, it also has high commercial impact. Many distinct value estimators have been proposed, using various statistical approaches. However, characterizing the errors incurred by these estimators is an open problem: existing analytical approaches are not powerful enough, and extensive empirical studies at large scale do not exist. We conduct an extensive large-scale empirical study of 11 distinct value estimators from four different approaches to the problem over families of Zipfian distributions whose parameters model real-world applications. Our study is the first that scales to the size of a billion-rows that today’s large commercial databases have to operate in. This allows us to characterize the error that is encountered in real-world applications of distinct value estimation. By mining the generated data, we show that estimator error depends on a key latent parameter — the average uniform class size — that has not been studied previously. This parameter also allows us to unearth error patterns that were previously unknown. Importantly, ours is the first approach that provides a framework for ∗This is the full-length version of a shorter published paper, and includes supplementary material for the published paper. Please cite as “Vinay Deolalikar and Hernan Laffitte: Extensive LargeScale Study of Error in Samping-Based Distinct Value Estimators for Databases, IEEE Big Data Conference, Washington DC, December 2016.” †contact author, [email protected], work done at HP Labs, Palo Alto. ‡[email protected]