Estimating join selectivities using bandwidth-optimized kernel density models

Abstract

Accurately predicting the cardinality of intermediate plan operations is an essential part of any modern relational query optimizer. The accuracy of said estimates has a strong and direct impact on the quality of the generated plans, and incorrect estimates can have a negative impact on query performance. One of the biggest challenges in this field is to predict the result size of join operations. Kernel Density Estimation (KDE) is a statistical method to estimate multivariate probability distributions from a data sample. Previously, we introduced a modern, self-tuning selectivity estimator for range scans based on KDE that outperforms state-of-the-art multidimensional histograms and is efficient to evaluate on graphics cards. In this paper, we extend these bandwidth-optimized KDE models to estimate the result size of single and multiple joins. In particular, we propose two approaches: (1) Building a KDE model from a sample drawn from the join result. (2) Efficiently combining the information from base table KDE models. We evaluated our KDE-based join estimators on a variety of synthetic and real-world datasets, demonstrating that they are superior to state-of-the art join estimators based on sketching or sampling.

A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration

“…To support the shifts in workload and dataset, they update the bandwidth after each incoming query and design the new sample maintenance method for insert-only workload and updates/deletions workload. Furthermore, in Kiefer et al [48] extend the method into estimating the selectivity of join. They design two different models: single model over the join samples and the models over the base tables, which does not need the join operation and estimates the selectivity of join with the independent assumption.…”

A Unified Deep Model of Learning from both Data and Queries for Cardinality Estimation

“…Therefore, it would be a natural idea of combining datadriven and query-driven models. As discussed before, the existing proposals leveraging both data and query workload [19,30,37,39] are insufficient towards this direction. An idea to overcome the problem of data-driven methods suffering the tail of the distribution due to their averaging optimization target would be using ensemble methods with each component targeting a different part of the distribution.…”

“…In fact, a few proposals (e.g., DeepDB) consider the combination as an interesting avenue for future work. Moreover, towards this direction several solutions [19,30,37,39] have been proposed to utilize both data and workload.…”

Selectivity Estimation for Relation-Tree Joins

“…The problem of join selectivity estimation has been extensively studied in the relational database [1,7,15,16,18,33,34]. Particularly, these studies can be divided into three classes.…”