QuickSel: Quick Selectivity Learning with Mixture Models

TL;DR

QuickSel introduces a fast, model-based approach for query selectivity estimation that continuously learns from queries, significantly outperforming traditional histogram methods in speed and accuracy.

Contribution

It proposes a novel mixture model framework for selectivity learning that is faster and more accurate than existing histogram-based methods.

Findings

QuickSel refines its model in under 2 milliseconds for 300 queries.

It is 34.0x-179.4x faster than state-of-the-art query-driven histograms.

QuickSel achieves 26.8%-91.8% higher accuracy given the same space budget.

Abstract

Estimating the selectivity of a query is a key step in almost any cost-based query optimizer. Most of today's databases rely on histograms or samples that are periodically refreshed by re-scanning the data as the underlying data changes. Since frequent scans are costly, these statistics are often stale and lead to poor selectivity estimates. As an alternative to scans, query-driven histograms have been proposed, which refine the histograms based on the actual selectivities of the observed queries. Unfortunately, these approaches are either too costly to use in practice---i.e., require an exponential number of buckets---or quickly lose their advantage as they observe more queries. In this paper, we propose a selectivity learning framework, called QuickSel, which falls into the query-driven paradigm but does not use histograms. Instead, it builds an internal model of the underlying data, which can be refined significantly faster (e.g., only 1.9 milliseconds for 300 queries). This fast refinement allows QuickSel to continuously learn from each query and yield increasingly more accurate selectivity estimates over time. Unlike query-driven histograms, QuickSel relies on a mixture model and a new optimization algorithm for training its model. Our extensive experiments on two real-world datasets confirm that, given the same target accuracy, QuickSel is 34.0x-179.4x faster than state-of-the-art query-driven histograms, including ISOMER and STHoles. Further, given the same space budget, QuickSel is 26.8%-91.8% more accurate than periodically-updated histograms and samples, respectively.

Tables

Table 1. Table 1 . The differences between query-driven histograms  (Srivastava et al . , 2006 ; Markl et al . , 2005 ; Kaushik and Suciu, 2009 ; Ré and Suciu, 2012 ; Markl et al . , 2007 ) and our method (QuickSel)

	Query-driven Histograms	QuickSel (ours)	Our Contribution
Model	histograms (non-overlapping buckets)	mixture models (overlapping subpopulations)	Employs a new expressive model $\to$ no exponential growth of complexity
Training	maximum entropy solved by iterative scaling	min difference from a uniform distribution solved analytically	A new optimization objective and its reduction to quadratic programming (solved analytically) $\to$ fast training and model refinements

Table 2. Table 2 . Notations.

Symbol	Meaning
$T$	a table (or a relation)
$C_i$	$i$-th column (or an attribute) of $T$; $i=1,\dots ,d$
$\left|T\right|$	the number of tuples in $T$
$[l_i,u_i]$	the range of the values in $C_i$
$x$	a tuple of $T$
$B_0$	the domain of $x$; $[l_1,u_1]\times \cdots \times [l_d,u_d]$
$P_i$	$i$-th predicate
$B_i$	hyperrectangle range for the $i$-th predicate
$\left|B_i\right|$	the size (of the area) of $B_i$
$x\in B_i$	$x$ belongs to $B_i$; thus, satisfies $P_i$
$I(\cdot )$	indicator function that returns 1 if its argument is true and 0 otherwise
$s_i$	the selectivity of $P_i$ for $T$
$(P_i,s_i)$	$i$-th observed query
$n$	the total number of observed queries
$f\left(x\right)$	probability density function of tuple $x$ (of $T$)

Table 3. Table 3 . Comparison of selectivity estimation methods

Approach	Model	Method	Key Contributions
Based on Database Scans (Scan-based Selectivity Estimation)	Histograms	Multi-dim Hist (Lynch, 1988; Deshpande et al., 2001; Ilyas et al., 2004)	Introduces multidimensional histograms
		Muralikrishna (Muralikrishna and DeWitt, 1988)	Introduces equidepth histograms
		Van Gelder (Van Gelder, 1993)	Estimates Join selectivity with histograms for important domains
		GOH (Jagadish et al., 2001)	Optimizes single-attribute histograms for joint distribution
		Thaper (Thaper et al., 2002)	Builds histograms over streaming data
		To (To et al., 2013)	Builds histograms with entropy as a metric
	Sampling	Lipton (Lipton et al., 1990)	Introduces adaptive sampling for high accuracy
		Haas (Haas et al., 1994)	Uses sampling for join selectivity estimation
		Riondato (Riondato et al., 2011)	Guarantees accuracy relying on the VC-dimension of queries
	ML	KDE (Gunopulos et al., 2005, 2000; Heimel et al., 2015)	Applies kernel density estimation to selectivity estimation
		PGM (Getoor et al., 2001; Tzoumas et al., 2013; Spiegel and Polyzotis, 2006)	Uses probabilistic graphical models for selectivity estimation
		Neural Net (Liu et al., 2015; Kipf et al., 2018)	Trains a neural network for selectivity estimation
Based on Observed Queries (Query-driven Selectivity Estimation)	Error-feedback Histograms (fast but less accurate)	ST-histogram (Aboulnaga and Chaudhuri, 1999)	Refines the bucket frequencies based on the errors
		LEO (Stillger et al., 2001)	Identifies incorrect statistics using observed queries
		STHoles (Bruno et al., 2001)	Proposes a new buckets split mechanism; adopted by ISOMER
		SASH (Lim et al., 2003)	Proposes a junction tree model for finding the best set of histograms
		QueryModel (Anagnostopoulos and Triantafillou, 2015)	Avoids modeling the data distribution by using queries directly
	Max-Entropy Histograms (accurate but slow)	ISOMER (Srivastava et al., 2006; Markl et al., 2005, 2007)	Finds a maximum entropy distribution consistent with observed queries
		Kaushik et al. (Kaushik and Suciu, 2009)	Extends ISOMER for distinct values
		Ré et al. (Ré and Suciu, 2012, 2010)	Seeks the max entropy distribution based on possible worlds
	Mixture Model (fast & accurate)	QuickSel (Ours)	Employs a mixture model for selectivity estimation; develops an efficient training algorithm for the new model

Equations

$$f(x)=\sum _{z=1}^{m}h(z)g_z(x)$$

$${\int }_{x\in B_i}f(x)dx=s_i$$

$$f(x)=\sum _{z=1}^{m}h(z)g_z(x)=\sum _{z=1}^m{w}_z{g}_z(x)$$

$$\int g_{z1}(x)g_{z2}(x)dx$$

$${\int }_{B_i}$$

$$=\sum _{z=1}^m{w}_z\int \frac{1}{|G_z|}I(x\in G_z\cap B_i)dx=\sum _{z=1}^m{w}_z\frac{|G_z\cap B_i|}{|G_z|}$$

$$\text{MSE}(f(x),g_0(x))=\int {\left(f(x)-g_0(x)\right)}^{2}dx$$

$$\underset{\mathbf{w}}{\mathrm{argmin}}$$

$${\int }_{B_i}f(x)dx=s_i\text{ for }i=1,\dots ,n$$

$$f(x)\ge 0$$

$${\int }_{x\in G_1\cap G_2}{\left(\frac{w_1+w_2}{|G_1\cap G_2|}-g_0(x)\right)}^{2}dx$$

$$+$$

$$\underset{\mathbf{w}}{\mathrm{argmin}}$$

$$A\mathbf{w}=\mathbf{s},\mathbf{w}\succcurlyeq 0$$

$$(Q{)}_{ij}$$

$$\int (f(x){)}^{2}dx$$

$$=\int \sum _{i=1}^m\sum _{j=1}^m\frac{w_i{w}_j}{|G_i||G_j|}I(x\in G_i)I(x\in G_j)dx$$

$$\sum _{i=1}^m\sum _{j=1}^m\frac{w_i{w}_j}{|G_i||G_j|}|G_i\cap G_j|$$

$$={\left[\begin{array}{c}{w}_1\\ w_2\\ ⋮\\ w_m\end{array}\right]}^{\top }\left[\begin{array}{ccc}\frac{|G_1\cap G_1|}{|G_1||G_1|}& \cdots & \frac{|G_1\cap G_m|}{|G_1||G_m|}\\ ⋮& & ⋮\\ \frac{|G_m\cap G_1|}{|G_m||G_1|}& \cdots & \frac{|G_m\cap G_m|}{|G_m||G_m|}\end{array}\right]\left[\begin{array}{c}{w}_1\\ w_2\\ ⋮\\ w_m\end{array}\right]$$

$$={\mathbf{w}}^{\top }Q\mathbf{w}$$

$${\int }_{B_i}f(x)dx={\int }_{B_i}\sum _{j=1}^m\frac{w_j}{|G_j|}I(x\in G_j)dx$$

$$=\sum _{j=1}^m\frac{w_j}{|G_j|}{\int }_{B_i}I(x\in G_j)dx=\sum _{j=1}^m\frac{w_j}{|G_j|}|B_i\cap G_j|$$

$$=\left[\begin{array}{ccc}\frac{|B_i\cap G_1|}{|G_1|}& \cdots & \frac{|B_i\cap G_m|}{|G_m|}\end{array}\right]\left[\begin{array}{c}{w}_1\\ ⋮\\ w_m\end{array}\right]$$

$$\left[\begin{array}{ccc}\frac{|B_1\cap G_1|}{|G_1|}& \cdots & \frac{|B_1\cap G_m|}{|G_m|}\\ ⋮& \ddots & ⋮\\ \frac{|B_n\cap G_1|}{|G_1|}& \cdots & \frac{|B_n\cap G_m|}{|G_m|}\end{array}\right]\left[\begin{array}{c}{w}_1\\ ⋮\\ w_m\end{array}\right]=\left[\begin{array}{c}{s}_1\\ ⋮\\ s_m\end{array}\right]$$

$$\Rightarrow A\mathbf{w}=\mathbf{s}$$

$$\underset{\mathbf{w}}{\mathrm{argmin}}\ell (\mathbf{w})={\mathbf{w}}^{\top }Q\mathbf{w}+\lambda \Vert A\mathbf{w}-\mathbf{s}{\Vert }^2$$

$$\frac{\partial \ell ({\mathbf{w}}^{\ast })}{\partial \mathbf{w}}=2Q{\mathbf{w}}^{\ast }+2\lambda A^{\top }(A{\mathbf{w}}^{\ast }-\mathbf{s})=0$$

$$\displaystyle\Rightarrow\bm{w}^{*}=\left(Q+\lambda\,A^{\top}A\right)^{\scalebox{0.75}[1.0]{$-$}1}\,\lambda\,A\,\bm{s}$$

$$\text{RMS error}={\left(\frac{1}{t}\sum _{i=1}^t(\text{true\_sel}-\text{est\_sel}{)}^2\right)}^{1/2}$$

$$\mathrm{argmin}\int f(x)\log (f(x))dx$$

$$=\mathrm{argmin}\int (f(x){)}^{2}dx$$

$$f(x)=\frac{w_j}{|G_j|}\text{for }{G}_j\text{ such that }x\in G_j$$

$$s_i=\sum _{j=1}^m\frac{|B_i\cap G_j|}{|G_j|}\mathbf{w}=\mathbf{s}$$

$$\underset{\mathbf{w}}{\mathrm{argmin}}$$

$$A\mathbf{w}=\mathbf{s}$$

$$(A{)}_{i,j}=\frac{|B_i\cap G_j|}{|G_j|}.$$

Full text

In this paper, we propose a selectivity learning framework, called QuickSel, which falls into the query-driven paradigm but does not use histograms. Instead, it builds an internal model of the underlying data, which can be refined significantly faster (e.g., only 1.9 milliseconds for 300 queries). This fast refinement allows QuickSel to continuously learn from each query and yield increasingly more accurate selectivity estimates over time. Unlike query-driven histograms, QuickSel relies on a mixture model and a new optimization algorithm for training its model. Our extensive experiments on two real-world datasets confirm that, given the same target accuracy, QuickSel is 34.0$\times$–179.4$\times$ faster than state-of-the-art query-driven histograms, including ISOMER and STHoles. Further, given the same space budget, QuickSel is 26.8%–91.8% more accurate than periodically-updated histograms and samples, respectively.

††copyright: acmlicensed††doi: 10.1145/3318464.3389727††isbn: 978-1-4503-6735-6/20/06††journalyear: 2020††booktitle: Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data (SIGMOD’20), June 14–19, 2020, Portland, OR, USA††conference: Proceedings of the 2020 ACM SIGMOD International Conference on Management of Data; June 14–19, 2020; Portland, OR, USA††article: 4††price: 15.00

1. Introduction

Estimating the selectivity of a query—the fraction of input tuples that satisfy the query’s predicate—is a fundamental component in cost-based query optimization, including both traditional RDBMSs (Proteau, [n. d.]; pos, [n. d.]; ibm, [n. d.]; sql, [n. d.]b; mar, [n. d.]) and modern SQL-on-Hadoop engines (Rodríguez, [n. d.]; Hu et al., 2018). The estimated selectivities allow the query optimizer to choose the cheapest access path or query plan (Selinger et al., 1979; Kester et al., 2017).

Today’s databases typically rely on histograms (pos, [n. d.]; ibm, [n. d.]; sql, [n. d.]b) or samples (Proteau, [n. d.]) for their selectivity estimation. These structures need to be populated in advance by performing costly table scans. However, as the underlying data changes, they quickly become stale and highly inaccurate. This is why they need to be updated periodically, creating additional costly operations in the database engine (e.g., ANALYZE table).111Some database systems (sql, [n. d.]b) automatically update their statistics when the number of modified tuples exceeds a threshold.

To address the shortcoming of scan-based approaches, numerous proposals for query-driven histograms have been introduced, which continuously correct and refine the histograms based on the actual selectivities observed after running each query (Srivastava et al., 2006; Bruno et al., 2001; Markl et al., 2005; Kaushik and Suciu, 2009; Aboulnaga and Chaudhuri, 1999; Lim et al., 2003; Ré and Suciu, 2012; Stillger et al., 2001; Agrawal et al., 2006). There are two approaches to query-driven histograms. The first approach (Bruno et al., 2001; Aboulnaga and Chaudhuri, 1999; Lim et al., 2003; Agrawal et al., 2006), which we call error-feedback histograms, recursively splits existing buckets (both boundaries and frequencies) for every distinct query observed, such that their error is minimized for the latest query. Since the error-feedback histograms do not minimize the (average) error across multiple queries, their estimates tend to be much less accurate.

To achieve a higher accuracy, the second approach is to compute the bucket frequencies based on the maximum entropy principle (Srivastava et al., 2006; Markl et al., 2005; Ré and Suciu, 2012; Kaushik and Suciu, 2009). However, this approach (which is also the state-of-the-art) requires solving an optimization problem, which quickly becomes prohibitive as the number of observed queries (and hence, number of buckets) grows. Unfortunately, one cannot simply prune the buckets in this approach, as it will break the underlying assumptions of their optimization algorithm (called iterative scaling, see Section 2.3 for details). Therefore, they prune the observed queries instead in order to keep the optimization overhead feasible in practice. However, this also means discarding data that could be used for learning a more accurate distribution.

Our Goal  We aim to develop a new framework for selectivity estimation that can quickly refine its model after observing each query, thereby producing increasingly more accurate estimates over time. We call this new framework selectivity learning. We particularly focus on designing a low-overhead method that can scale to a large number of observed queries without requiring an exponential number of buckets.

Our Model  To overcome the limitations of query-driven histograms, we use a mixture model (Bishop, 2006) to capture the unknown distribution of the data. A mixture model is a probabilistic model to approximate an arbitrary probability density function (pdf), say $f(x)$, using a combination of simpler pdfs:

[TABLE]

where $g_{z}(x)$ is the $z$-th simpler pdf and $h(z)$ is its corresponding weight. The subset of the data that follows each of the simpler pdfs is called a subpopulation. Since the subpopulations are allowed to overlap with one another, a mixture model is strictly more expressive than histograms. In fact, it is shown that mixture models can achieve a higher accuracy than histograms (Chen, [n. d.]), which is confirmed by our empirical study (Section 5.5). To the best of our knowledge, we are the first to propose a mixture model for selectivity estimation.222Our mixture model also differs from kernel density estimation (Heimel et al., 2015; Gunopulos et al., 2005; Blohsfeld et al., 1999), which scans the actual data, rather than analyzing observed queries.

Challenges  Using a mixture model for selectivity learning requires finding optimal parameter values for $h(z)$ and $g_{z}(x)$; however, this optimization (a.k.a. training) is challenging for two reasons.

First, the training process aims to find parameters that maximize the model quality, defined as $\int Q(f(x))\,dx$ for some metric of quality $Q$ (e.g., entropy). However, computing this integral is non-trivial for a mixture model since its subpopulations may overlap in arbitrary ways. That is, the combinations of $m$ subpopulations can create $2^{m}$ distinct ranges, each with a potentially different value of $f(x)$. As a result, naïvely computing the quality of a mixture model quickly becomes intractable as the number of observed queries grows.

Second, the outer optimization algorithms are often iterative (e.g., iterative scaling, gradient descent), which means they have to repeatedly evaluate the model quality as they search for optimal parameter values. Thus, even when the model quality can be evaluated relatively efficiently, the overall training/optimization process can be quite costly.

Our Approach  First, to ensure the efficiency of the model quality evaluation, we propose a new optimization objective. Specifically, we find the parameter values that minimize the $L2$ distance (or equivalently, mean squared error) between the mixture model and a uniform distribution, rather than maximizing the entropy of the model (as pursued by previous work (Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Ré and Suciu, 2012; Markl et al., 2007)). As described above, directly computing the quality of a mixture model involves costly integrations over $2^{m}$ distinct ranges. However, when minimizing the $L2$ distance, the $2^{m}$ integrals can be reduced to only $m^{2}$ multiplications, hence greatly reducing the complexity of the model quality evaluation. Although minimizing the $L2$ distance is much more efficient than maximizing the entropy, these two objectives are closely related (see our report (Authors, 2020) for a discussion).

In addition, we adopt a non-conventional variant of mixture models, called a uniform mixture model. While uniform mixture models have been previously explored in limited settings (with only a few subpopulations) (Craigmile and Tirrerington, 1997; Gupta and Miyawaki, 1978), we find that they are quite appropriate in our context as they allow for efficient computations of the $L2$ distance. That is, with this choice, we can evaluate the quality of a model by only using min, max, and multiplication operations (Section 3.2). Finally, our optimization can be expressed as a standard quadratic program, which still requires an iterative procedure.

Therefore, to avoid the costly iterative optimization, we also devise an analytic solution that can be computed more efficiently. Specifically, in addition to the standard reduction (i.e., moving some of the original constraints to the objective clause as penalty terms), we completely relax the positivity constraints for $f(x)$, exploiting the fact that they will be naturally satisfied in the process of approximating the true distribution of the data. With these modifications, we can solve for the solution analytically by setting the gradient of the objective function to zero. This simple transformation speeds up the training by 1.5$\times$–17.2$\times$. In addition, since our analytic solution requires a constant number of operations, the training time is also consistent across different datasets and workloads.

Using these ideas, we have developed a first prototype of our selectivity learning proposal, called QuickSel, which allows for extremely fast model refinements. As summarized in Table 1, QuickSel differs from—and considerably improves upon—query-driven histograms (Aboulnaga and Chaudhuri, 1999; Lim et al., 2003; Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Ré and Suciu, 2012) in terms of both modeling and training (see Section 7 for a detailed comparison).

Contributions  We make the following contributions:

1. (1)

We propose the first mixture model-based approach to selectivity estimation (Section 3). 2. (2)

For training the mixture model, we design a constrained optimization problem (Section 4.1). 3. (3)

We show that the proposed optimization problem can be reduced (from an exponentially complex one) to a quadratic program and present further optimization strategies for solving it (Section 4.2). 4. (4)

We conduct extensive experiments on two real-world datasets to compare QuickSel’s performance and state-of-the-art selectivity estimation techniques (Section 5).

2. Preliminaries

In this section, we first define relevant notations in Section 2.1 and then formally define the problem of query-driven selectivity estimation in Section 2.2. Next, in Section 2.3, we discuss the drawbacks of previous approaches.

2.1. Notations

Table 2 summarizes the notations used throughout this paper.

Set Notations  $T$ is a relation that consists of $d$ real-valued columns $C_{1},\ldots,C_{d}$.333Handling integer and categorical columns is discussed in Section 2.2. The range of values in $C_{i}$ is $[l_{i},u_{i}]$ and the cardinality (i.e., row count) of $T$ is $N$=$|T|$. The tuples in $T$ are denoted by $x_{1},\ldots,x_{N}$, where each $x_{i}$ is a size-$d$ vector that belongs to $B_{0}=[l_{1},u_{1}]\times\cdots\times[l_{d},u_{d}]$. Geometrically, $B_{0}$ is the area bounded by a hyperrectangle whose bottom-left corner is $(l_{1},\ldots,l_{d})$ and top-right corner is $(u_{1},\ldots,u_{d})$. The size of $B_{0}$ can thus be computed as $|B_{0}|$=$(u_{1}-l_{1})\times\cdots\times(u_{d}-l_{d})$.

Predicates  We use $P_{i}$ to denote the (selection) predicate of the $i$-th query on $T$. In this paper, a predicate is a conjunction444See Section 2.2 for a discussion of disjunctions and negations. of one or more constraints. Each constraint is a range constraint, which can be one-sided (e.g., $3\leq C1$) or two-sided (e.g., $-3\leq C1\leq 10$). This range can be extended to also handle equality constraints on categorical data (see Section 2.2). Each predicate $P_{i}$ is represented by a hyperrectangle $B_{i}$. For example, a constraint “$1\leq C1\leq 3$ AND $2\leq C2$” is represented by a hyperrectangle $(1,3)\times(2,u2)$, where $u2$ is the upper-bound of $C2$. We use $P_{o}$ to denote an empty predicate, i.e., one that selects all tuples.

Selectivity  The selectivity $s_{i}$ of $P_{i}$ is defined as the fraction of the rows of $T$ that satisfy the predicate. That is, $s_{i}=(1/N)\sum_{k=1}^{N}I(x_{k}\in B_{i})$, where $I(\cdot)$ is the indicator function. A pair $(P_{i},s_{i})$ is referred to as an observed query.555This pair is also referred to as an assertion by prior work (Ré and Suciu, 2010). Without loss of generality, we assume that $n$ queries have been observed for $T$ and seek to estimate $s_{n+1}$. Finally, we use $f(x)$ to denote the joint probability density function of tuple $x$ (that has generated tuples of $T$).

2.2. Problem Statement

Next, we formally state the problem:

Problem 1 (Query-driven Selectivity Estimation)

Consider a set of $n$ observed queries ${(P_{1},s_{1}),\allowbreak\ldots,\allowbreak(P_{n},s_{n})}$ for $T$. By definition, we have the following for each $i=1,\ldots,n$:

[TABLE]

Then, our goal is to build a model of $f(x)$ that can estimate the selectivity $s_{n+1}$ of a new predicate $P_{n+1}$.

Initially, before any query is observed, we can conceptually consider a default query $(P_{0},1)$, where all tuples are selected and hence, the selectivity is 1 (i.e., no predicates).

Discrete and Categorical Values  Problem 1 can be extended to support discrete attributes (e.g., integers, characters, categorical values) and equality constraints on them, as follows. Without loss of generality, suppose that $C_{i}$ contains the integers in $\{1,2,\ldots,b_{i}\}$. To apply the solution to Problem 1, it suffices to (conceptually) treat these integers as real values in $[1,b_{i}+1]$ and then convert the original constraints on the integer values into range constraints, as follows. A constraint of the form “$C_{i}=k$” will be converted to a range constraint of the form $k\leq C_{i}<k+1$. Mathematically, this is equivalent to replacing a probability mass function with a probability density function defined using dirac delta functions.666A dirac delta function $\delta(x)$ outputs $\infty$ if $x=0$ and outputs 0 otherwise while satisfying $\int\delta(x)\,dx=1$. Then, the summation of the original probability mass function can be converted to the integration of the probability density function. String data types (e.g., char, varchar) and their equality constraints can be similarly supported, by conceptually mapping each string into an integer (preserving their order) and applying the conversion described above for the integer data type.

Supported Queries  Similar to prior work (Bruno et al., 2001; Aboulnaga and Chaudhuri, 1999; Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Ré and Suciu, 2012; Markl et al., 2007; Lim et al., 2003), we support selectivity estimation for predicates with conjunctions, negations, and disjunctions of range and equality constraints on numeric and categorical columns. We currently do not support wildcard constraints (e.g., LIKE ’word’), EXISTS constraints, or ANY constraints. In practice, often a fixed selectivity is used for unsupported predicates, e.g., 3.125% in Oracle (Proteau, [n. d.]).

To simplify our presentation, we focus on conjunctive predicates. However, negations and disjunctions can also be easily supported. This is because our algorithm only requires the ability to compute the intersection size of pairs of predicates $P_{i}$ and $P_{j}$, which can be done by converting $P_{i}\land P_{j}$ into a disjunctive normal form and then using the inclusion-exclusion principle to compute its size.

As in the previous work, we focus our presentation on predicates on a single relation. However, any selectivity estimation technique for a single relation can be applied to estimating selectivity of a join query whenever the predicates on the individual relations are independent of the join conditions (Hu et al., 2018; pos, [n. d.]; Selinger et al., 1979; Swami and Schiefer, 1994).

2.3. Why not Query-driven Histograms

In this section, we briefly describe how query-driven histograms work (Srivastava et al., 2006; Bruno et al., 2001; Markl et al., 2005; Kaushik and Suciu, 2009; Aboulnaga and Chaudhuri, 1999; Lim et al., 2003; Ré and Suciu, 2012), and then discuss their limitations, which motivate our work.

How Query-driven Histograms Work  To approximate $f(x)$ (defined in Problem 1), query-driven histograms adjust their bucket boundaries and bucket frequencies according to the queries they observe. Specifically, they first determine bucket boundaries (bucket creation step), and then compute their frequencies (training step), as described next.

1. (1)

Bucket Creation: Query-driven histograms determine their bucket boundaries based on the given predicate’s ranges (Aboulnaga and Chaudhuri, 1999; Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Ré and Suciu, 2012). If the range of a later predicate overlaps with that of an earlier predicate, they split the bucket(s) created for the earlier one into two or more buckets in order to ensure that the buckets do not overlap with one another. Figure 1 shows an example of this bucket splitting operation. 2. (2)

Training: After creating the buckets, query-driven histograms assign frequencies to those buckets. Early work (Aboulnaga and Chaudhuri, 1999; Lim et al., 2003) determines bucket frequencies in the process of bucket creations. That is, when a bucket is split into two or more, the frequency of the original bucket is also split (or adjusted), such that it minimizes the estimation error for the latest observed query.

However, since this process does not minimize the (average) error across multiple queries, their estimates are much less accurate. More recent work (Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Ré and Suciu, 2012) has addressed this limitation by explicitly solving an optimization problem based on the maximum entropy principle. That is, they search for bucket frequencies that maximize the entropy of the distribution while remaining consistent with the actual selectivities observed.

Although using the maximum entropy principle will lead to highly accurate estimates, it still suffers from two key limitations.

Limitation 1: Exponential Number of Buckets  Since existing buckets may split into multiple ones for each new observed query, the number of buckets can potentially grow exponentially as the number of observed queries grows. For example, in our experiment in Section 5.5, the number of buckets was 22,370 for 100 observed queries, and 318,936 for 300 observed queries. Unfortunately, the number of buckets directly affects the training time. Specifically, using iterative scaling—the optimization algorithm used by all previous work (Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Markl et al., 2007; Ré and Suciu, 2012)— the cost of each iteration grows linearly with the number of variables (i.e., the number of buckets). This means that the cost of each iteration can grow exponentially with the number of observed queries.

As stated in Section 1, we address this problem by employing a mixture model, which can express a probability distribution more effectively than query-driven histograms. Specifically, our empirical study in Section 5.5 shows that—using the same number of parameters—a mixture model achieves considerably more accurate estimates than histograms.

Limitation 2: Non-trivial Bucket Merge/Pruning  Gi-ven that query-driven histograms (Srivastava et al., 2006; Markl et al., 2005) quickly become infeasible due to their large number of buckets, one might consider merging or pruning the buckets in an effort to reduce their training times. However, merging or pruning the histogram buckets violates the assumption used by their optimization algorithms, i.e., iterative scaling. Specifically, iterative scaling relies on the fact that a bucket is either completely included in a query’s predicate range or completely outside of it.777For example, this property is required for the transition from Equation (6) to Equation (7) in (Markl et al., 2005). That is, no partial overlap is allowed. This property must hold for each of the $n$ predicates. However, merging some of the buckets will inevitably cause partial overlaps (between predicate and histogram buckets). For interested readers, we have included a more detailed explanation of why iterative scaling requires this assumption in  Appendix A.

3. QuickSel: Model

This section presents how QuickSel models the population distribution and estimates the selectivity of a new query. QuickSel’s model relies on a probabilistic model called a mixture model. In Section 3.1, we describe the mixture model employed by QuickSel. Section 3.2 describes how to estimate the selectivity of a query using the mixture model. Section 3.3 describes the details of QuickSel’s mixture model construction.

3.1. Uniform Mixture Model

A mixture model is a probabilistic model that expresses a (complex) probability density function (of the population) as a combination of (simpler) probability density functions (of subpopulations). The population distribution is the one that generates the tuple $x$ of $T$. The subpopulations are internally managed by QuickSel to best approximate $f(x)$.

Uniform Mixture Model  QuickSel uses a type of mixture model, called the uniform mixture model. The uniform mixture model represents a population distribution $f(x)$ as a weighted summation of multiple uniform distributions, $g_{z}(x)$ for $z=1,\ldots,m$. Specifically,

[TABLE]

where $h(z)$ is a categorical distribution that determines the weight of the $z$-th subpopulation, and $g_{z}(x)$ is the probability density function (which is a uniform distribution) for the $z$-th subpopulation. The support of $h(z)$ is the integers ranging from 1 to $m$; $h(z)=w_{z}$. The support for $g_{z}(x)$ is represented by a hyperrectangle $G_{z}$. Since $g_{z}(x)$ is a uniform distribution, $g_{z}(x)=1/|G_{z}|$ if $x\in G_{z}$ and 0 otherwise. The locations of $G_{z}$ and the values of $w_{z}$ are determined in the training stage (Section 4). In the remainder of this section (Section 3), we assume that $G_{z}$ and $w_{z}$ are given.

Benefit of Uniform Mixture Model  The uniform mixture model was studied early in the statistics community (Craigmile and Tirrerington, 1997; Gupta and Miyawaki, 1978); however, recently, a more complex model called the Gaussian mixture model has received more attention (Bishop, 2006; Yang et al., 2015; Ragothaman et al., 2016).888There are other variants of mixture models (Moser et al., 2015; Asparouhov and Muthén, 2016). The Gaussian mixture model uses a Gaussian distribution for each subpopulation; the smoothness of its probability density function (thus, differentiable) makes the model more appealing when gradients need to be computed. Nevertheless, we intentionally use the uniform mixture model for QuickSel due to its computational benefit in the training process, as we describe below.

As will be presented in Section 4.2, QuickSel’s training involves the computations of the intersection size between two subpopulations, for which the essential operation is evaluating the following integral:

[TABLE]

Evaluating the above expression for multivariate Gaussian distributions, e.g., g_{z1}(x)=\exp\left(\scalebox{0.75}[1.0]{-}x^{\top}\Sigma^{\scalebox{0.75}[1.0]{-}1}x\right)/\sqrt{(2\pi)^{d}\,|\Sigma|}, requires numerical approximations (Genz, 1992; Joe, 1995), which are either slow or inaccurate. In contrast, the intersection size between two hyperrectangles can be exactly computed by simple min, max, and multiplication operations.

3.2. Selectivity Estimation with UMM

For the uniform mixture model, computing the selectivity of a predicate $P_{i}$ is straightforward:

[TABLE]

Recall that both $G_{z}$ and $B_{i}$ are represented by hyperrectangles. Thus, their intersection is also a hyperrectangle, and computing its size is straightforward.

3.3. Subpopulations from Observed Queries

We describe QuickSel’s approach to determining the boundaries of $G_{z}$ for $z=1,\ldots,m$. Note that determining $G_{z}$ is orthogonal to the model training process, which we describe in Section 4; thus, even if one devises an alternative approach to creating $G_{z}$, our fast training method is still applicable.

QuickSel creates $m$ hyperrectangular ranges999The number $m$ of subpopulations is set to $\min(4\cdot n,4,000)$, by default. (for the supports of its subpopulations) in a way that satisfies the following simple criterion: if more predicates involve a point $x$, use a larger number of subpopulations for $x$. Unlike query-driven histograms, QuickSel can easily pursue this goal by exploiting the property of a mixture model: the supports of subpopulations may overlap with one another.

In short, QuickSel generates multiple points (using predicates) that represent the query workloads and create hyperrectangles that can sufficiently cover those points. Specifically, we propose two approaches for this: a sampling-based one and a clustering-based one. The sampling-based approach is faster; the clustering-based approach is more accurate. Each of these is described in more detail below.

Sampling-based  This approach performs the following operations for creating $G_{z}$ for $z=1,\ldots,m$.

1. (1)

Within each predicate range, generate multiple random points $r$. Generating a large number of random points increases the consistency; however, QuickSel limits the number to 10 since having more than 10 points did not improve accuracy in our preliminary study. 2. (2)

Use simple random sampling to reduce the number of points to $m$, which serves as the centers of $G_{z}$ for $z=1,\ldots,m$. 3. (3)

The length of the $i$-th dimension of $G_{z}$ is set to twice the average of the distances (in the same $i$-th dimension) to the 10 nearest-neighbor centers.

Figure 2 illustrates how the subpopulations are created using both (1) highly-overlapping query workloads and (2) scattered query workloads. In both cases, QuickSel generates random points to represent the distribution of query workloads, which is then used to create $G_{z}$ ($z=1,\ldots,m$), i.e., the supports of subpopulations. This sampling-based approach is faster, but it does not ensure the coverage of all random points $r$. In contrast, the following clustering-based approach ensures that.

Clustering-based  The second approach relies on a clustering algorithm for generating hyperrectangles:

1. (1)

Do the same as the sampling-based approach. 2. (2)

Cluster $r$ into $m$ groups. (We used K-means++.) 3. (3)

For each of $m$ groups, we create the smallest hyperrectangle $G_{z}$ that covers all the points belonging to the group.

Note that since each $r$ belongs to a cluster and we have created a hyperrectangle that fully covers each cluster, the union of the hyperrectangles covers all $r$. Our experiments primarily use the sampling-based approach due to its efficiency, but we also compare them empirically in Section 5.7.

The following section describes how to assign the weights (i.e., $h(z)=w_{z}$) of these subpopulations.

4. QuickSel: Model Training

This section describes how to compute the weights $w_{z}$ of QuickSel’s subpopulations. For training its model, QuickSel finds the model that maximizes uniformity while being consistent with the observed queries. In Section 4.1, we formulate an optimization problem based on this criteria. Next, Section 4.2 presents how to solve the problem efficiently.

4.1. Training as Optimization

This section formulates an optimization problem for QuickSel’s training. Let $g_{0}(x)$ be the uniform distribution with support $B_{0}$; that is, $g_{0}(x)=1/|B_{0}|$ if $x\in B_{0}$ and 0 otherwise. QuickSel aims to find the model $f(x)$, such that the difference between $f(x)$ and $g_{0}(x)$ is minimized while being consistent with the observed queries.

There are many metrics that can measure the distance between two probability density functions $f(x)$ and $g_{0}(x)$, such as the earth mover’s distance (Rubner et al., 2000), Kullback-Leibler divergence (Kullback and Leibler, 1951), the mean squared error (MSE), the Hellinger distance, and more. Among them, QuickSel uses MSE (which is equivalent to $L2$ distance between two distributions) since it enables the reduction of our originally formulated optimization problem (presented shortly; Problem 2) to a quadratic programming problem, which can be solved efficiently by many off-the-shelf optimization libraries (Andersen et al., 2013; apa, [n. d.]; jop, [n. d.]; mat, [n. d.]). Also, minimizing MSE between $f(x)$ and $g_{0}(x)$ is closely related to maximizing the entropy of $f(x)$ (Srivastava et al., 2006; Markl et al., 2005; Kaushik and Suciu, 2009; Ré and Suciu, 2012). See Section 6 for the explanation of this relationship.

MSE between $f(x)$ and $g_{0}(x)$ is defined as follows:

[TABLE]

Recall that the support for $g_{0}(x)$ is $B_{0}$. Thus, QuickSel obtains the optimal weights by solving the following problem.

Problem 2 (QuickSel’s Training)

QuickSel obtains the optimal parameter $\bm{w}$ for its model by solving:

[TABLE]

Here, (5) ensures $f(x)$ is a proper probability density function.

4.2. Efficient Optimization

We first describe the challenges in solving Problem 2. Then, we describe how to overcome the challenges.

Challenge  Solving Problem 2 in a naïve way is computationally intractable. For example, consider a mixture model consisting of (only) two subpopulations represented by $G_{1}$ and $G_{2}$, respectively. Then, $\int_{x\in B_{0}}(f(x)-g_{0}(x))^{2}\,dx$ is:

[TABLE]

Observe that with this approach, we need four separate integrations only for two subpopulations. In general, the number of integrations is $O(2^{m})$, which is $O(2^{n})$. Thus, this direct approach is computationally intractable.

Conversion One: Quadratic Programming  Problem 2 can be solved efficiently by exploiting the property of the distance metric of our choice (i.e., MSE) and the fact that we use uniform distributions for subpopulations (i.e., UMM). The following theorem presents the efficient approach.

Theorem 1

The optimization problem in Problem 2 can be solved by the following quadratic optimization:

[TABLE]

where

[TABLE]

The bendy inequality sign ($\succcurlyeq$) means that every element of the vector on the left-hand side is equal to or larger than the corresponding element of the vector on the right-hand side.

Proof.

This theorem can be shown by substituting the definition of QuickSel’s model (Equation 2) into the probability density function $f(x)$ in Equation 3. Note that minimizing $(f(x)-g_{0}(x))^{2}$ is equivalent to minimizing $f(x)\,(f(x)-2\,g_{0}(x))$, which is also equivalent to minimizing $(f(x))^{2}$ since $g_{0}(x)$ is constant over $B_{0}$ and $\int f(x)\,dx=1$.

The integration of $(f(x))^{2}$ over $B_{0}$ can be converted to a matrix multiplication, as shown below:

[TABLE]

which can be simplified to

[TABLE]

Second, we express the equality constraints in an alternative form. Note that the left-hand side of each equality constraint, i.e., $\int_{B_{i}}f(x)\,dx$, can be expressed as:

[TABLE]

Then, the equality constraints, i.e., $\int_{B_{i}}f(x)\,dx=s_{i}$ for $i=1,\ldots,n$, can be expressed as follows:

[TABLE]

Finally, $\bm{w}^{\top}\bm{1}=1$ if and only if $\int f(x)=1$, and $\bm{w}\succcurlyeq\bm{0}$ for arbitrary $G_{z}$ if and only if $\int f(x)\geq 0$. ∎

The implication of the above theorem is significant: we could reduce the problem of $O(2^{n})$ complexity to the problem of only $O(n^{2})$ complexity.

Conversion Two: Moving Constraints  The quadratic programming problem in Theorem 1 can be solved efficiently by most off-the-shelf optimization libraries; however, we can solve the problem even faster by converting the problem to an alternative form. We first present the alternative problem, then discuss it.

Problem 3 (QuickSel’s QP)

QuickSel solves this problem alternative to the quadratic programming problem in Theorem 1:

[TABLE]

where $\lambda$ is a large real value (QuickSel uses $\lambda=10^{6}$).

In formulating Problem 3, two types of conversions are performed: (1) the consistency with the observed queries (i.e., $A\bm{w}=\bm{s}$) is moved into the optimization objective as a penalty term, and (2) the positivity of $f(x)$ is not explicitly specified (by $\bm{w}\succcurlyeq\bm{0}$). These two types of conversions have little impact on the solution for two reasons. First, to guarantee the consistency, a large penalty (i.e., $\lambda=10^{6}$) is used. Second, the mixture model $f(x)$ is bound to approximate the true distribution, which is always non-negative. We empirically examine the advantage of solving Problem 3 (instead of solving the problem in Theorem 1 directly) in Section 5.7.

The solution $\bm{w}^{*}$ to Problem 3 can be obtained in a straightforward way by setting its gradients of the objective (with respect to $\bm{w}$) equal to $\bm{0}$:

[TABLE]

Observe that $\bm{w}^{*}$ is expressed in a closed form; thus, we can obtain $\bm{w}^{*}$ analytically instead of using iterative procedures typically required for general quadratic programming.

5. Experiment

In this section, we empirically study QuickSel.

In summary, our results show the following:

1. (1)

End-to-end comparison against other query-driven methods: QuickSel was significantly faster (34.0$\times$–179.4$\times$) for the same accuracy—and produced much more accurate estimates (26.8%–91.8% lower error) for the same time limit—than previous query-driven methods. (Section 5.2) 2. (2)

Comparison against periodic database scans: For the same storage size, QuickSel’s selectivity estimates were 77.7% and 91.3% more accurate than scan-based histograms and sampling, respectively. (Section 5.3) 3. (3)

Impact on PostgreSQL performance: Using QuickSel for PostgreSQL makes the system 2.25$\times$ faster (median) than the default. (Section 5.4) 4. (4)

Effectiveness of QuickSel’s mixture model: QuickSel’s model produced considerably more accurate estimates than histograms given the same number of parameters. (Section 5.5) 5. (5)

Robustness to workload shifts: QuickSel’s accuracy quickly recovers after sudden workload shifts. (Section 5.6) 6. (6)

Optimization efficiency: QuickSel’s optimization method (Problem 3) was 1.5$\times$–17.2$\times$ faster than solving the standard quadratic programming. (Section 5.7)

5.1. Experimental Setup

Methods  Our experiments compare QuickSel to six other selectivity estimation methods.

Query-driven Methods:

1. (1)

STHoles (Bruno et al., 2001): This method creates histogram buckets by partitioning existing buckets (as in Figure 1). The frequency of an existing bucket is distributed uniformly among the newly created buckets. 2. (2)

ISOMER (Srivastava et al., 2006): This method applies STHoles for histogram bucket creations, but it computes the optimal frequencies of the buckets by finding the maximum entropy distribution. Among existing query-driven methods, ISOMER produced the highest accuracy in our experiments. 3. (3)

ISOMER+QP: This method combines ISOMER’s approach for creating histogram buckets and QuickSel’s quadratic programming (Problem 3) for computing the optimal bucket frequencies. 4. (4)

QueryModel (Anagnostopoulos and Triantafillou, 2015): This method computes the selectivity estimate by a weighted average of the selectivities of observed queries. The weights are determined based on the similarity of the new query and each of the queries observed in the past.

Scan-based Methods:

1. (5)

AutoHist: This method creates an equiwidth multidimensional histogram by scanning the data. It also updates its histogram whenever more than 20% of the data changes (this is the default setting with SQL Server’s AUTO_UPDATE_ STATISTICS option (sql, [n. d.]a)). 2. (6)

AutoSample: This method relies on a uniform random sample of data to estimate selectivities. Similar to AutoHist, AutoSample updates its sample whenever more than 10% of the data changes.

We have implemented all methods in Java.

Datasets and Query Sets  We use two real datasets and one synthetic dataset in our experiments, as follows:

1. (1)

DMV: This dataset contains the vehicle registration records of New York State (State of New York, 2018). It contains 11,944,194 rows. Here, the queries ask for the number of valid registrations for vehicles produced within a certain date range. Answering these queries involves predicates on three attributes: model_year, registration_date, and expiration_date. 2. (2)

Instacart: This dataset contains the sales records of an online grocery store (Stanley, 2017). We use their orders table, which contains 3.4 million sales records. Here, the queries ask for the reorder frequency for orders made during different hours of the day. Answering these queries involves predicates on two attributes: order_hour_of_day and days_since_prior. (In Section 5.3, we use more attributes (up to ten).) 3. (3)

Gaussian: We also generated a synthetic dataset using a bivariate dimensional normal distribution. We varied this dataset to study our method under workload shifts, different degrees of correlation between the attributes, and more. Here, the queries count the number of points that lie within a randomly generated rectangle.

For each dataset, we measured the estimation quality using 100 test queries not used for training. The ranges for selection predicates (in queries) were generated randomly within a feasible region; the ranges of different queries may or may not overlap.

Environment  All our experiments were performed on m5.4xlarge EC2 instances, with 16-core Intel Xeon 2.5GHz and 64 GB of memory running Ubuntu 16.04.

Metrics  We use the root mean square (RMS) error:

[TABLE]

where $t$ is the number of test queries. We report the RMS errors in percentage (by treating both true_sel and est_sel as percentages).

When reporting training time, we include the time required for refining a model using an additional observed query, which itself includes the time to store the query and run the necessary optimization routines.

5.2. Selectivity Estimation Quality

In this section, we compare the end-to-end selectivity estimation quality of QuickSel versus query-driven histograms.

Specifically, we gradually increased the number of observed queries provided to each method from 10 to 1,000. For each number of observed queries, we measured the estimation error and training time of each method using 100 test queries.

These results are reported in Figure 3. Given the same number of observed queries, QuickSel’s training was significantly faster (Figures 3(a) and 3(d)), while still achieving comparable estimation errors (Figures 3(b) and 3(e)). We also studied the relationship between errors and training times in Figures 3(c) and 3(f), confirming QuickSel’s superior efficiency (STHoles, ISOMER+QP, and QueryModel are omitted in these figures due to their poor performance). In summary, QuickSel was able to quickly learn from a large number of observed queries (i.e., shorter training time) and produce highly accurate models.

5.3. Comparison to Scan-based Methods

We also compared QuickSel to two automatically-updating scan-based methods, AutoHist and AutoSample, which incorporate SQL Server’s automatic updating rule into equiwidth multidimensional histograms and samples, respectively. Since both methods incur an up-front cost for obtaining their statistics, they should produce relatively more accurate estimates initially (before seeing new queries). In contrast, the accuracy of QuickSel’s estimates should quickly improve as new queries are observed.

To verify this empirically, we first generated a Gaussian dataset (1 million tuples) with correlation 0. We then inserted 200K new tuples generated from a distribution with a different correlation after processing 200 queries, and repeated this process. In other words, after processing the first 100 queries, we inserted new data with correlation 0.1; after processing the next 100 queries, we inserted new data with correlation 0.2; and continued this process until a total of 1000 queries were processed. We performed this process for each method under comparison. QuickSel adjusted its model each time after observing 100 queries. AutoHist and AutoSample updated their statistics after each batch of data insertion. QuickSel and AutoHist both used 100 parameters (# of subpopulations for the mixture model and # of buckets for histograms); AutoSample used a sample of 100 tuples.

Figure 4(a) shows the error of each method. As expected, AutoHist produced more accurate estimates initially. However, as more queries were processed, the error of QuickSel drastically decreased. In contrast, the errors of AutoSample and AutoHist did not improve with more queries, as they only depend on the frequency at which a new scan (or sampling) is performed. After processing only 100 queries (i.e., initial update), QuickSel produced more accurate estimates than both AutoHist and AutoSample. On average (including the first 100 queries), QuickSel was 71.4% and 89.8% more accurate than AutoHist and AutoSample, respectively. This is consistent with the previously reported observations that query-driven methods yield better accuracy than scan-based ones (Bruno et al., 2001). (The reason why query-driven proposals have not been widely adopted to date is due to their prohibitive cost; see Section 7.2).

In addition, Figure 4(b) compares the update times of the three methods. By avoiding scans, QuickSel’s query-driven updates were 525$\times$ and 243$\times$ faster than AutoHist and AutoSample, respectively.

Finally, we studied how the performance of those methods changed as we increased the data dimension (i.e., the number of attributes appearing in selection predicates). First, using the Instacart dataset, we designed each query to target a random subset of dimensions (N/2) as increasing the dimension N from 2 to 10. In all test cases (Figure 4(c) left), QuickSel ’s accuracy was consistent, showing its ability to scale to high-dimensional data. Also in this experiment, QuickSel performed significantly better than, or comparably to, histograms and sampling. We could also obtain a similar result using the Gaussian dataset (Figure 4(c) right). This consistent performance across different data dimensions is primarily due to how QuickSel is designed; that is, its estimation only depends on how much queries overlap with one another.

5.4. Impact on Query Performance

This section examines QuickSel’s impact on query performance. That is, we test if QuickSel’s more accurate selectivity estimates can lead to improved query performance for actual database systems (i.e., shorter latency).

To measure the actual query latencies, we used PostgreSQL ver. 10 with a third-party extension, called pg_hint_plan (pgh, [n. d.]). Using this extension, we enforced our own estimates (for PostgreSQL’s query optimization) in place of the default ones. We compared PostgreSQL Default (i.e., no hint) and QuickSel—to measure the latencies of the following join query in processing the Instacart dataset:

⬇

select count(*)

from S inner join T on S.tid = T.tid

   inner join U on T.uid = U.uid

where (range_filter_on_T)

and (range_filter_on_S);

where the joins keys for the tables S, T, and U were in the PK-FK relationship, as described by the schema (of Instacart).

Figure 5 shows the speedups QuickSel could achieve in comparison to PostgreSQL Default. Note that QuickSel does not improve any underlying I/O or computation speed; its speedups are purely from helping PostgreSQL’s query optimizer choose a more optimal plan based on improved selectivity estimates. Even so, QuickSel could bring 2.25$\times$ median speedup, with 3.47$\times$ max speedup. In the worst case, PostgreSQL with QuickSel was almost identical to PostgreSQL Default (i.e., 0.98$\times$ speedup).

5.5. QuickSel’s Model Effectiveness

In this section, we compare the effectiveness of QuickSel’s model to that of models used in the previous work. Specifically, the effectiveness is assessed by (1) how the model size—its number of parameters—grows as the number of observed queries grows, and (2) how quickly its error decreases as its number of parameters grows.

Figure 6(a) reports the relationship between the number of observed queries and the number of model parameters. As discussed in Section 2.3, the number of buckets (hence, parameters) of ISOMER increased quickly as the number of observed queries grew. STHoles was able to keep the number of its parameters small due to its bucket merging technique; however, this had a negative impact on its accuracy. Here, QuickSel used the least number of model parameters. For instance, when 100 queries were observed for DMV, QuickSel had 10$\times$ fewer parameters than STHoles and 56$\times$ fewer parameters than ISOMER.

We also studied the relationship between the number of model parameters and the error. The lower the error (for the same number of model parameters), the more effective the model. Figure 6(b) shows the result. Given the same number of model parameters, QuickSel produced significantly more accurate estimates. Equivalently, QuickSel produced the same quality estimates with much fewer model parameters.

5.6. Robustness to Workload Shifts

In this section, we test QuickSel’s performance under significant workload shifts. That is, after observing a certain number of queries (i.e., 100 queries) around a certain region of data, the query workload suddenly jumps to a novel region. This pattern repeats several times.

Figure 7 shows the result. Here, we could observe the following pattern. QuickSel’s error increased significantly right after each jump (i.e., at query sequence #100 and at #200), producing 1.5$\times$-3.6$\times$ higher RMS errors compared to histograms. However, QuickSel’s error dropped quickly, achieving 12$\times$-378$\times$ lower RMS errors than histograms. This was possible due to QuickSel’s faster adaptation.

5.7. QuickSel Internal Mechanisms

In this section, we empirically study (1) the effect of QuickSel’s optimization (presented in Section 4.2), and (2) two alternative mechanisms for generating subpopulations (presented in Section 3.3).

Optimization Efficiency  To study QuickSel’s optimization efficiency, we compared two approaches for solving the quadratic problem defined in Theorem 1: solving the original QP without any modifications versus solving our modified version (Problem 3). We used the cvxopt library for the former and used jblas (a linear algebra library) for the latter. Both libraries use multiple cores for parallel processing.

Figure 8 shows the time taken by each optimization approach. The second approach (Problem 3) was increasingly more efficient as the number of observed queries grew. For example, it was 8.36$\times$ faster when the number of observed queries reached 1,000. This is thanks to the modified problem having an analytical solution, while the original problem required an iterative gradient descent solution.

Subpopulation Generation  We empirically studied the two subpopulation generation approaches (i.e., the sampling-based approach and the clustering-based approach, Section 3.3) in terms of their scalability to high-dimensional data. Specifically, we compared their estimation accuracies and computational overhead using the Gaussian dataset (with its dimension set to 2–10).

Figure 9 reports the results. As shown in Figure 9(a), the clustering-based approach obtained impressive accuracy in comparison to the sampling-based one. However, as shown in Figure 9(b), the clustering-based approach produced higher overhead (i.e., longer training times), which is an example of the natural tradeoff between cost and accuracy.

6. Connection: MSE and Entropy

The max-entropy query-driven histograms optimize their parameters (i.e., bucket frequencies) by searching for the parameter values that maximize the entropy of the distribution $f(x)$. We show that this approach is approximately equivalent to QuickSel’s optimization objective, i.e., minimizing the mean squared error (MSE) of $f(x)$ from a uniform distribution. The entropy of the probability density function is defined as \scalebox{0.75}[1.0]{-}\int f(x)\,\log(f(x))\,dx. Thus, maximizing the entropy is equivalent to minimizing $\int f(x)\,\log(f(x))\,dx$, which is related to minimizing MSE as follows:

[TABLE]

since $\int f(x)\,dx=1$ by definition. We used the first-order Taylor expansion to approximate $\log(x)$ with $x-1$. Note that, when the constraint $\int f(x)\,dx=1$ is considered, $f(x)=1/|R_{0}|$ is the common solution to both the entropy maximization and minimizing MSE.

7. Related Work

There is extensive work on selectivity estimation due to its importance for query optimization. In this section, we review both scan-based (Section 7.1) and query-driven methods (Section 7.2). QuickSel belongs to the latter category. We have summarized the related work in Table 3.

7.1. Database Scan-based Estimation

As explained in Section 1, we use the term scan-based methods to refer to techniques that directly inspect the data (or part of it) for collecting their statistics. These approaches differ from query-based methods which rely only on the actual selectivities of the observed queries.

Scan-based Histograms  These approaches approximate the joint distribution by periodically scanning the data. There has been much work on how to efficiently express the joint distribution of multidimensional data (Cormode et al., 2011; Lynch, 1988; Muralikrishna and DeWitt, 1988; Van Gelder, 1993; Jagadish et al., 2001; Deshpande et al., 2001; Thaper et al., 2002; Ilyas et al., 2004; To et al., 2013; Lipton et al., 1990; Haas et al., 1994; Riondato et al., 2011; Gunopulos et al., 2005, 2000; Heimel et al., 2015; Jestes et al., 2011; Lam and Salem, 2005; Moerkotte et al., 2014; Guha et al., 2002; Istvan et al., 2014; He et al., 2005; Karras and Mamoulis, 2008; Chen and Ozsu, 2004). There is also some work on histograms for special types of data, such as XML (Wang et al., 2006; Bhowmick et al., 2007; Aboulnaga et al., 2001; Wu et al., 2003), spatial data (Zhang and Lin, 2004; Tao et al., 2003; Mamoulis and Papadias, 2001; Wang et al., 2014; Sun et al., 2006; Lin et al., 2003; Zhang and Lin, 2002; Koudas et al., 2000; Jagadish et al., 1998; Koloniari et al., 2005; Korn et al., 1999; Tang and Li, 2014; Neumann and Michel, 2008), graph (Feng et al., 2005), string (Mazeika et al., 2007; Jagadish et al., 1999a, 2000; Jagadish et al., 1999b); or for privacy (Hay et al., 2010; Kuo et al., 2018; Li et al., 2010).

Sampling  Sampling-based methods rely on a sample of data for estimating its joint distribution (Lipton et al., 1990; Haas et al., 1994; Riondato et al., 2011). However, drawing a new random sample requires a table-scan or random retrieval of tuples, both of which are costly operations and hence, are only performed periodically.

Machine Learning Models 

KDE is a technique that translates randomly sampled data points into a distribution (Silverman, 2018). In the context of selectivity estimation, KDE has been used as an alternative to histograms (Gunopulos et al., 2005, 2000; Heimel et al., 2015). The similarity between KDE and mixture models (which we employ for QuickSel) is that they both express a probability density function as a summation of some basis functions. However, KDE and MM (mixture models) are fundamentally different. KDE relies on independent and identically distributed samples, and hence lends itself to scan-based selectivity estimation. In contrast, MM does not require any sampling and can thus be used in query-driven selectivity estimation (where sampling is not practical). Similarly, probabilistic graphical models (Getoor et al., 2001; Tzoumas et al., 2013; Spiegel and Polyzotis, 2006), neural networks (Liu et al., 2015; Kipf et al., 2018), and tree-based ensembles (Dutt et al., 2019) have been used for selectivity estimation. Unlike histograms, these approaches can capture column correlations more succinctly. However, applicability of these models for query-driven selectivity estimation has not been explored and remains unclear.

More recently, sketching (Cai et al., 2019) and probe executions (Trummer, 2019) have been proposed, which differ from ours in that they build their models directly using the data (not query results). Similar to histograms, using the data requires either periodic updates or higher query processing overhead. QuickSel avoids both of these shortcomings with its query-driven MM.

7.2. Query-driven Estimation

Query-driven techniques create their histogram buckets adaptively according to the queries they observe in the workload. These can be further categorized into two techniques based on how they compute their bucket frequencies: error-feedback histograms and max-entropy histograms.

Error-feedback Histograms  Error-feedback histograms (Aboulnaga and Chaudhuri, 1999; Bruno et al., 2001; Lim et al., 2003; Anagnostopoulos and Triantafillou, 2015; Khachatryan et al., 2015, 2012) adjust bucket frequencies in consideration of the errors made by old bucket frequencies. They differ in how they create histogram buckets according to the observed queries. For example, STHoles (Bruno et al., 2001) splits existing buckets with the predicate range of the new query. SASH (Lim et al., 2003) uses a space-efficient multidimensional histogram, called MHIST (Deshpande et al., 2001), but determines its bucket frequencies with an error-feedback mechanism. QueryModel (Anagnostopoulos and Triantafillou, 2015) treats the observed queries themselves as conceptual histogram buckets and determines the distances among those buckets based on the similarities among the queries’ predicates.

Max-Entropy Histograms  Max-entropy histograms (Srivastava et al., 2006; Markl et al., 2005, 2007; Kaushik and Suciu, 2009; Ré and Suciu, 2012) find a maximum entropy distribution consistent with the observed queries. Unfortunately, these methods generally suffer from the exponential growth in their number of buckets as the number of observed queries grows (as discussed in Section 2). QuickSel avoids this problem by relying on mixture models.

Fitting Parametric Functions  Adaptive selectivity estimation (Chen and Roussopoulos, 1994) fits a parametric function (e.g., linear, polynomial) to the observed queries. This approach is more applicable when we know the data distribution a priori, which is not assumed by QuickSel.

Self-tuning Databases  Query-driven histograms have also been studied in the context of self-tuning databases (Marcus et al., 2019; Kraska et al., 2019; Ma et al., 2018). IBM’s LEO (Stillger et al., 2001) corrects errors in any stage of query execution based on the observed queries. Microsoft’s AutoAdmin (Agrawal et al., 2006; Chaudhuri and Narasayya, 2007) focuses on automatic physical design, self-tuning histograms, and monitoring infrastructure. Part of this effort is ST-histogram (Aboulnaga and Chaudhuri, 1999) and STHoles (Bruno et al., 2001) (see Table 3). DBL (Park et al., 2017) and IDEA (Galakatos et al., 2017) exploit the answers to past queries for more accurate approximate query processing. QueryBot 5000 (Ma et al., 2018) forecasts the future queries, whereas OtterTune (Van Aken et al., 2017) and index (Kraska et al., 2018) use machine learning for automatic physical design and building secondary indices, respectively.

8. Conclusion and Future Work

The prohibitive cost of query-driven selectivity estimation techniques has greatly limited their adoption by DBMS vendors, which for the most part still rely on scan-based histograms and samples that are periodically updated and are otherwise stale. In this paper, we proposed a new framework, called selectivity learning or QuickSel, which learns from every query to continuously refine its internal model of the underlying data, and therefore produce increasingly more accurate selectivity estimates over time. QuickSel differs from previous query-driven selectivity estimation techniques by (i) not using histograms and (ii) enabling extremely fast refinements using its mixture model. We formally showed that the training cost of our mixture model can be reduced from exponential to only quadratic complexity (Theorem 1).

Supporting Complex Joins  When modeling the selectivity of join queries, even state-of-the-art modes  (Kipf et al., 2018; Dutt et al., 2019; Yang et al., 2019) take a relatively simple approach: conceptually prejoining corresponding tables, and constructing a joint probability distribution over each join pattern. We plan to similarly extend our current formulation of QuickSel to model the selectivity of general joins.

9. Acknowledgement

This material is based upon work supported by the National Science Foundation under Grant No. 1629397 and the Michigan Institute for Data Science (MIDAS) PODS. Any opinions, findings, and conclusions or recommendations expressed in this material are those of the author(s) and do not necessarily reflect the views of the National Science Foundation.

Appendix A Analysis of Iterative Scaling

The previous work uses an algorithm called iterative scaling to optimize the bucket frequencies. In this section, we analyze why using the approach is non-trivial when some buckets are merged or pruned. Specifically, we show that the approach becomes non-trivial if a histogram bucket may partially overlap with a predicate range, rather than being completely within or outside of the predicate range.

Selectivity Estimation with Histograms  First, we describe how histograms can be used for selectivity estimation. This description is needed to present iterative scaling itself. Let $G_{z}$ for $z=1,\ldots,m$ denote the boundary of $z$-th bucket. $w_{z}$ is the frequency of the $z$-th bucket. Then, the histogram approximates the distribution of data as follows:

[TABLE]

For a query’s predicate $P_{i}$, its selectivity can be computed as follows:

[TABLE]

One can observe that the above expression is akin to selectivity estimation formula with a mixture model (Section 3.2), which is natural since mixture models can be regarded as a generalization of histograms.

Optimization with Maximum Entropy Principle  To optimize the bucket frequencies, the previous work employs the maximum entropy principle. When the maximum entropy principle is used, one can optimize the bucket frequencies by solving the following problem.

Problem 4 (Training with Max Entropy Principle)

[TABLE]

where $A$ is a $n$-by-$m$ matrix; its $(i,j)$-th entry is defined as

[TABLE]

$\bm{s}$* is a size-$n$ column vector; its $i$-th entry is the observed selectivity of the $i$-th query.*

If a histogram bucket is completely included within a predicate range, $A_{i,j}$ takes 1; if a histogram bucket is completely outside a predicate range, $A_{i,j}$ takes 0. If a histogram bucket partially overlaps with a predicate range, $A_{i,j}$ takes a value between 0 and 1.

For histograms, the integral in the above problem can be directly simplified as follows:

[TABLE]

Iterative Scaling  Iterative Scaling solves the above problem by updating model parameters in a sequential order; that is, it updates $w_{1}$ as using fixed values for other parameters, it updates $w_{2}$ as using fixed values for other parameters, and so on. This iteration (i.e., updating all $w_{1}$ through $w_{m}$) continues until those parameter values converge. In this process, the important part is the formula for the updates.

To derive this update rule, the previous work uses the Lagrangian method, as follows. In the following derivation, we suppose a slightly more general setting; that is, we allow possible partial overlaps. We first define $L$:

[TABLE]

where $\bm{\lambda}$ is a size-$m$ column vector containing $m$ Lagrangian multipliers, i.e., $\bm{\lambda}=(\lambda_{1},\ldots,\lambda_{m})^{\top}$.

At optimal solutions, the derivative of $L$ with respect to $\bm{w}$ and $\lambda$ must be zero. Let the column vectors of $A$ be denoted by $a_{1},\ldots,a_{m}$; that is, $A=[a_{1},\ldots,a_{m}]$. Then,

[TABLE]

Let $z_{i}=\exp(\lambda_{i})$. Then,

[TABLE]

From the constraint that $\sum_{j=1}^{m}A_{i,j}w_{j}=s_{i}$ for $i=1,\ldots,n$,

[TABLE]

Let’s assume that $A_{i,j}$ always takes either 0 or 1 (the condition used in the previous work); then, the above expression can be simplified to produce an analytic update rule. Let $C_{i}$ be an index set such that $C_{i}=\{k\mid A_{i,k}=1,k=1,\ldots,m\}$. Also, let $D_{j\textbackslash i}=\{k\mid A_{k,j}=1,k=1,\ldots,n,k\neq i\}$. Then,

[TABLE]

Using the above equation, the previous work continues to update $z_{i}$ for $i=1,\ldots,n$ until convergence. Once those values are obtained, the bucket frequencies can be obtained by Equation 6.

However, without the assumption that $A_{i,j}$ always takes either 0 or 1, obtaining the update equation (Equation 8) from Equation 7 is non-trivial.