Selectivity Estimation of Inequality Joins in Databases

Abstract

Selectivity estimation refers to the ability of the SQL query optimizer to estimate the size of the results of a predicate in the query. It is the main calculation based on which the optimizer can select the least expensive plan to execute. While the problem has been known since the mid-1970s, we were surprised that there are no solutions in the literature for the selectivity estimation of inequality joins. By testing four common database systems: Oracle, SQL-Server, PostgreSQL, and MySQL, we found that the open-source systems PostgreSQL and MySQL lack this estimation. Oracle and SQL-Server make fairly accurate estimations, yet their algorithms are secret. This paper, thus, proposes an algorithm for inequality join selectivity estimation. The proposed algorithm was implemented in PostgreSQL and sent as a patch to be included in the next releases. We compared this implementation with the above DBMS for three different data distributions (uniform, normal, and Zipfian) and showed that our algorithm provides extremely accurate estimations (below 0.1% average error), outperforming the other systems by an order of magnitude.

1. Introduction

Query optimization is the overall process of generating the most-efficient query plan given a SQL statement. The query optimizer, responsible for this process, applies equivalence rules to reorganize and merge the operations in the query to find the fastest execution plan and feeds it to the executor. It examines multiple access methods, such as sequential table scans or index scans, different join methods, such as nested loops and hash joins, different join orders, sub-query normalization, materialized views, and other possible transformations. Almost all existing systems adopt a cost-based optimization approach, with roots back to the architecture of System R [1] and Volcano/Cascades [2,3].

In cost-based query optimization, the optimizer estimates the cost of the alternative query plans and chooses the plan with the minimum cost. The cost is estimated in terms of the CPU and I/O resources that the query plan will use. A central component in cost estimation is the Selectivity Estimation (SE). SE collects statistics for all attributes in a relation, such as data distribution histograms, the most-common values, the null percentage, etc. These statistics are then used during planning time to estimate the number of tuples generated by a predicate in the query. A smaller selectivity value means a smaller size for intermediate results, which is favorable for a more efficient execution. The cost-based optimizer, thus, reorders the selection and join predicates to quickly reduce the sizes of intermediate results.

Since, in general, the cost of each operator depends on the size of its input relations, it is important to provide good estimations of their selectivity, that is of their result size, to the query optimizer [4,5]. Inaccurate selectivity estimations can lead to inefficient query plans being chosen, sometimes leading to orders of magnitude longer execution times [6].

There is a trade-off between the size of the stored statistics and the complexity of the estimation algorithm, on the one hand, and the estimation accuracy, on the other. Recent research thus focuses on using machine learning methods to capture the data distribution into compact models. While there are good results in this research direction [7], common relational database systems continue to use traditional statistics structures, mostly based on histograms. A histogram can be used as a discrete approximation of the probability density function of an attribute.

Despite the popularity of histograms, there is a lack of a theory of how to use them in estimating inequality join selectivity. In addition, we found that common database systems lack accurate implementations for such an estimator. This paper thus aimed to fill this gap and makes the following main contributions:

• A formal model for describing a selectivity estimation problem using probability theory;
• A novel algorithm for join selectivity estimation of inequality operators using histogram statistics;
• An extension of the algorithm that also takes advantage of additional statistics (e.g., MCV), when available.
• An implementation of the proposed algorithm in PostgreSQL. This implementation has been submitted as a patch for inclusion in a future release https://github.com/DRepas/postgres/tree/rangejoinsel (accessed on 7 March 2023).

Section 2 starts by describing the research problem, together with a running example, which will be used throughout the paper. In the same section, we also introduce some definitions, notations, and terminology. Section 3 then reviews the existing related work in selectivity estimation. The algorithm presented in this paper consists of mapping the problem of selectivity estimation to a probability theory problem, as described in Section 4. The algorithm of join selectivity estimation is developed in Section 5. This section also develops a way to incorporate the null values and Most-Common Values (MCVs) statistics in the estimation model. A mapping of this model for several range operators is also presented. Finally, an implementation in PostgreSQL and the experimental evaluation are provided in Section 6.

2. Preliminaries

This paper presents a formal model to compute two different selectivity estimation types:

• Restriction selectivity estimation: when one of the sides of the operator is an attribute of a relation and the other is a constant value:

  –

  Example: SELECT * FROM R1 WHERE R1.X < 100.

• Join selectivity estimation: when both sides of the operator are attributes of different relations:

  –

  Example: SELECT * FROM R1, R2 WHERE R1.X < R2.Y.

The selectivity estimation of operations where both sides of the operator are attributes of the same relation, with no join or Cartesian product involved (example: SELECT * FROM R1 WHERE R1.x < R1.y), is not addressed by this paper.

The selectivity of an operator is the fraction of values kept in the result after selection. In the case of restriction selectivity, the denominator is the input relation’s size. In the case of join selectivity, the denominator is the input relations’ Cartesian product size (their sizes multiplied). This fraction can be interpreted as the probability that a given randomly selected tuple from the input relation, or from the Cartesian product of input relations in the case of joins, is selected by the operator being considered.

2.1. Problem Definition

In this paper, we developed an algorithm to perform the join selectivity estimation of inequality joins described above. The restriction selectivity estimation is already implemented by all common database systems, thus not a novel contribution of this work. We, however, formulated it as a probability problem and developed the join selectivity estimation on top of it, to maximize the code reuse in these systems.

The focus of the next sections will be on the restriction selectivity estimation of the less than (<) operator. The restriction selectivity estimation of all scalar inequality operators was derived from this initial estimation. We also built/generalized it to develop the join selectivity estimation.

The attributes being restricted or joined were treated as random variables that follow a distribution modeled by a Probability Density Function (PDF) and/or a Cumulative Distribution Function (CDF).

2.2. Running Example

For demonstration purposes, relations R1 and R2 are used throughout this paper. For each relation, 12 integers were manually selected to cover as many corner cases as possible when using equi-depth histograms (introduced in Section 2.3), such as skew and common bin boundaries.

$R1.X=\{10,11,12,20,21,22,24,25,30,35,38,45\}$

$R2.Y=\{15,16,17,20,30,35,38,39,40,42,45,50\}$

2.3. Histogram Statistics

Histograms are commonly used to approximate the PDF of an attribute by grouping the values in uniform bins. Each bin is an interval of the form: $B_j=]his{t}_j,his{t}_{j+1}]$, where $his{t}_i$ are values from the domain of the attribute. It is important to note that the side on which the interval is open or closed has nothing to do with the purposes of this paper, as all estimations correspond to integration over a continuous domain, where singular points do not affect the final result. By defining a bin this way (using intervals), this paper restricted itself to domains where total order exists. This excludes categorical data, for which the idea of an equi-depth histogram does not apply.

Let $H_j\left(X\right)=P(X\in B_j)$ be the fraction of values of the attribute X that is represented by the histogram bin $B_j$, i.e., the height/depth of the histogram bin:

• Singleton histograms are such that each bin refers to the frequency of a single element, typically used to collect most-common values statistics (introduced in Section 5.3).
• Equi-width histograms are such that each bin has the same width. That is, $his{t}_{j+1}-his{t}_j$ is constant.
• Equi-depth histograms are such that each bin has the same depth (height), but a varying width. That is, $\forall j,P(X\in B_j)=H_j\left(X\right)=\frac{1}{n}$, where n is the total number of bins.

For selectivity estimation, equi-depth histograms are favored because the resolution of the histogram adapts to skewed value distributions. Typically, the histogram is smaller than the original data. Thus, it cannot represent the true distribution entirely, and some assumptions are induced, e.g., uniformity on a single attribute, with the independence assumption among different attributes. The use of equi-depth histograms is also motivated by their prevalence in current RDBMS. These are usually constructed through the use of random sampling of the original relations.

For demonstration purposes, Figure 1 shows a graphical representation of equi-depth histograms for the attributes $R1.X$ and $R2.Y$ of the running example. For both histograms, there are three bins, meaning that the fraction accounted for by each bin is $\frac{1}{3}$. For attribute $R1.X$, $his{t}_X=[10,20,25,45]$, which means that ${B_X}_0=[10,20]$, ${B_X}_1=]20,25]$, and ${B_X}_2=]25,45]$. For attribute y, $his{t}_Y=[15,20,38,50]$, which means that ${B_Y}_0=[15,20]$, ${B_Y}_1=]20,39]$, and ${B_Y}_2=]39,50]$.

Using histograms as a statistical representation of attributes involves the following implicit assumptions:

• The data are distributed uniformly inside each bin;
• The histograms are complete (they account for all the data points), that is:

  –

  $his{t}_0=min\left(X\right)$;

  –

  $his{t}_n=max\left(X\right)$.

In practice, these two assumptions do not strictly hold. The data are usually not uniformly distributed inside each bin. The more bins used in the histograms, the smaller the error introduced by this assumption is. Database systems, e.g., PostgreSQL, typically create the histogram using a random sample of the attribute values, especially when the number of tuples is too large. The assumption of the completeness of the histogram might be broken in the presence of sampling. When the sample is representative of the underlying data, the estimation is still fairly accurate.

Given the equi-depth histogram of an attribute X, with n bins, one can derive its approximate PDF and CDF as shown next. Let $f_X\left(c\right)$ and $F_X\left(c\right)$ denote the PDF and the CDF of X, respectively, at a given value c, then

$$f_X\left(c\right)=\left\{\begin{array}{cc}0\hfill & c<his{t}_0\hfill \\ \frac{1}{n}\frac{1}{(his{t}_{j+1}-his{t}_j)}\hfill & c\in B_j,0\le j<n\hfill \\ 0\hfill & c\ge his{t}_n\hfill \end{array}\right.$$

(1)

$$F_X\left(c\right)=\left\{\begin{array}{cc}0\hfill & c<his{t}_0\hfill \\ \frac{1}{n}(j+\frac{c-his{t}_j}{his{t}_{j+1}-his{t}_j})\hfill & c\in B_j,0\le j<n\hfill \\ 1\hfill & c\ge his{t}_n\hfill \end{array}\right.$$

(2)

When $c\in B_j,0\le j<n$, Formula (1) is derived from the definition of the equi-depth histogram, where each bin represents $H_j\left(X\right)=\frac{1}{n}$ of the data, spread over a width of $his{t}_{j+1}-his{t}_j$.

Formula (2) is derived from the following:

$$\begin{array}{cc}\hfill F_X\left(c\right)& ={\int }_{-\infty }^c{f}_X\left(x\right)dx\hfill \\ \hfill & ={\int }_{-\infty }^{his{t}_0}{f}_X\left(x\right)dx+\sum _{i=0}^{j-1}{\int }_{his{t}_i}^{his{t}_{i+1}}{f}_X\left(x\right)dx+{\int }_{his{t}_j}^c{f}_X\left(x\right)dx\hfill \end{array}$$

where

$$\begin{array}{cc}\hfill {\int }_{-\infty }^{his{t}_0}{f}_X\left(x\right)dx& =0\hfill \\ \hfill \sum _{i=0}^{j-1}{\int }_{his{t}_i}^{his{t}_{i+1}}{f}_X\left(x\right)dx& =\sum _{i=0}^{j-1}{H}_j\left(X\right)=\sum _{i=0}^{j-1}\frac{1}{n}=\frac{j}{n}\hfill \\ \hfill {\int }_{his{t}_j}^c{f}_X\left(x\right)dx& =H_j\left(X\right)\frac{c-his{t}_j}{his{t}_{j+1}-his{t}_j}dx=\frac{1}{n}\frac{c-his{t}_j}{his{t}_{j+1}-his{t}_j}\hfill \end{array}$$

This last formula performs linear interpolation within the bin where c is contained, thus assuming a uniform distribution of values within the bin. The assumption that the histogram is complete is reflected in substituting the infinite bounds by $his{t}_0,his{t}_n$. In the running example, the PDF and CDF of $R1.X$ can be derived from the formulas presented in this section as follows:

$$f_X\left(c\right)=\left\{\begin{array}{cc}0\hfill & c<10\hfill \\ \frac{1}{30}\hfill & 10\le c<20\hfill \\ \frac{1}{15}\hfill & 20\le c<25\hfill \\ \frac{1}{60}\hfill & 25\le c<45\hfill \\ 0\hfill & 45\le c\hfill \end{array}\right.$$

$$F_X\left(c\right)=\left\{\begin{array}{cc}0\hfill & c<10\hfill \\ \frac{1}{30}c-\frac{1}{3}\hfill & 10\le c<20\hfill \\ \frac{1}{15}c-1\hfill & 20\le c<25\hfill \\ \frac{1}{60}c+\frac{1}{4}\hfill & 25\le c<45\hfill \\ 1\hfill & 45\le c\hfill \end{array}\right.$$

3. Review of Related Work

A survey on DBMS query optimizer was proposed in 2021 [6], which categorizes cardinality estimation methods into synopsis-based methods, sampling-based methods, and learning-based methods. Many learning-based methods [8,9,10,11,12] have been proposed in recent years and show better accuracy than traditional methods. However, there are still many missing parts to be solved to put them into real systems, such as the cost of model training and updating and the black-box property of learning algorithms [7]. Sampling-based methods estimate selectivity by executing a (sub)query on samples collected from tables, whose accuracy depends on the degree to which the samples fit the original data distribution [6]. These methods, however, suffer from a high cost of storage and retrieval time, especially when the tables are very large. Another limitation of sampling-based methods is that they currently only support equality join selectivity estimation [6].

A third class of methods collects data summaries in the form statistics, to then estimate the size of the query results [13]. Histograms, as a form of synopsis-based methods, have been extensively studied [6,8] and are widely adopted in common database systems [14] for the purpose of selectivity estimation, including MySQL, PostgreSQL, Oracle, and SQL-Server [15,16,17,18]. Histograms are commonly used in databases for other tasks, including, for instance, data security and watermarking [19,20].

MySQL uses two histogram types for selectivity estimation [15]. One is the singleton histogram, which stores the distinct values and their cumulative frequency. Another is the equi-depth histogram, called equi-height in the MySQL documentation. This histogram stores the lower and upper bounds, the cumulative frequency, and the number of distinct values for each bucket. However, the usage of histograms is limited to restriction selectivity estimation [15], i.e., the selection operator. For join selectivity estimation, MySQL naively returns a constant: 0.1 for equality joins and 0.3333 for inequality joins [21]. Listing 1 displays an excerpt of the MySQL source code in which these constants are defined [22]:

Listing 1. 

MySQL Defaul Selectivity Values.

• /// Filtering effect for equalities: col1 = col2
• #define COND_FILTER_EQUALITY 0.1f
• /// Filtering effect for inequalities: col1 > col2
• #define COND_FILTER_INEQUALITY 0.3333f

PostgreSQL also uses the histogram as the optimizer statistics [16]. By analyzing its manual [23], as well as its source code, it uses equi-depth histograms. In contrast to MySQL, the number of distinct values in each bucket is not stored. For this reason, PostgreSQL does not use these histogram statistics in estimating equi-join selectivity. It rather uses a singleton histogram of the Most-Common Values (MCVs) [23]. As for inequality join selectivity estimation (<, ≤, >, ≥), a default constant value of $0.3333$ is returned [24].

Listing 2 displays an excerpt of the PostgreSQL source code in which these constants are defined [25]:

Listing 2. 

PostgreSQL Defaul Selectivity Values.

• /∗ default selectivity estimate for equalities such as "A = b"∗/
• #define DEFAULT_EQ_SEL 0.005
• /∗ default selectivity estimate for inequalities such as "A < b"∗/
• #define DEFAULT_INEQ_SEL 0.3333333333333333

Oracle Database uses three types of histograms to capture the data distribution of a relation’s attribute [17]: singleton histograms (referred to as frequency histograms and top frequency histograms in the official documentation), equi-depth histograms (referred to as height-balanced in the official documentation), and hybrid histograms (a combination of equi-depth and frequency histograms). The type of histogram is determined based on specific criteria to fit different situations. The official documentation [17] also states some factors behind their selectivity estimation algorithms, such as endpoint numbers (the unique identifier of a bucket, e.g., the cumulative frequency of buckets in frequency and hybrid histograms) and values (the highest value in a bucket), and whether column values are popular (an endpoint value that appears multiple times in a histogram) or non-popular (every column value that is not popular). However, the details of these estimation algorithms are not published. Few online articles, in the form of hacker blogs, made experimental analyses to guess how selectivity estimation works in Oracle Database, but did not yield a clear algorithm [26,27].

SQL-Server is another popular closed-source DBMS. Due to its proprietary nature, the implementation details are scarce. According to the official documentation [18], a proprietary kind of histogram with a density vector associated is built in three steps for each attribute. The official documentation [28,29] describes four core assumptions for the selectivity estimation: independence when no correlation information is available, uniformity in histogram bins, inclusion when filtering a column with a constant, and containment when joining distinct values from two histograms [30]. Although the white paper [29] is a publication from SQL-Server that deals with the problem of selectivity estimation, it does not explain the algorithm used for join selectivity. Similar to Oracle, the implemented algorithm is a secret.

To identify if any informed selectivity estimation is taking place when performing inequality joins in SQL-Server and Oracle, we performed the following experiment. Two different attributes, T1 and T2, with 1000 and 200 rows, respectively, were randomly generated by sampling the range [0, 100] uniformly. They were then joined using the < (less than) operator. Both databases made a quite accurate selectivity estimation of this inequality join. Oracle Database had an estimation error of 3%, and SQL-Server had a smaller error of just 0.29%. As such, we know that both systems implement good estimation algorithms.

In conclusion, although learning-based methods have become a popular research direction for selectivity estimation in recent years, histograms are still the most-commonly used statistics in existing DBMS for this purpose. The recurring types of used histogram statistics are equi-depth histograms approximating the distribution of values and singleton histograms of most-common values. As our investigation indicates, MySQL and PostgreSQL do not have algorithms implemented for join selectivity estimation, and they use predefined constants. On the other hand, popular commercial DBMS (SQL-Server and Oracle) have implemented some algorithms based on the histograms, but we could not find any source describing them. This paper addresses this gap by proposing such an algorithm.

4. A Formal Model for Selectivity Estimation

We first start by formalizing the problem of restriction selectivity estimation for the less than (<) operator. Suppose that the goal is to estimate the selectivity of the following operation (expressed in SQL):

• SELECT ∗
• FROM R1
• WHERE R1.X < c

where c is a constant. Treating the attribute $R1.X$ as a random variable X, estimating the selectivity of the above operation is equivalent to finding $P(X<c)$. Given the PDF or the CDF of X, $f_X$, or $F_X$, respectively, the selectivity of the operation above can be formalized as

$$P(X<c)={\int }_{-\infty }^c{f}_X\left(x\right)dx=F_X\left(c\right)$$

(3)

Suppose now that the goal is to estimate the join selectivity for the less than (<) operator. That is, we want to estimate the selectivity of the following operation (expressed in SQL):

• SELECT ∗
• FROM R1, R2
• WHERE R1.X < R2.Y

Treating the attributes $R1.X$ and $R2.Y$ as random variables X and Y, respectively, estimating the selectivity of the above operation can be formulated as finding $P(X<Y)$.

Consider the joint distribution of X and Y, $P(X,Y)$. The probability that a sample (a, b) taken at random from the Cartesian product of the values in $R1.X$ and $R2.Y$ can be defined as follows:

$$\forall a\in R1.X,b\in R2.Y,P(X=a,Y=b)=P(X=a)\times P(Y=b)$$

or, equivalently,

$$P(X,Y)=P\left(X\right)P\left(Y\right)$$

which is the definition of independent random variables. Note that, when a Cartesian product is involved, either explicitly as in the SQL statement above or implicitly through a join clause, the two variables are independent.

Given a joint PDF of X and Y, $f_{X,Y}$. With X and Y being independent, it is known that $f_{X,Y}(x,y)=f_X\left(x\right)f_Y\left(y\right)$, with $f_X$ and $f_Y$ the PDFs of X and Y, respectively. Considering $F_X$ to be the CDF of X, the selectivity of the less than (<) operator can be formalized as follows:

$$\begin{array}{cc}\hfill P(X<Y)& ={\int }_{-\infty }^{+\infty }{\int }_{-\infty }^y{f}_{X,Y}(x,y)dxdy\hfill \\ \hfill & ={\int }_{-\infty }^{+\infty }{\int }_{-\infty }^y{f}_X\left(x\right)f_Y\left(y\right)dxdy\hfill \\ \hfill & ={\int }_{-\infty }^{+\infty }\left({\int }_{-\infty }^y{f}_X\left(x\right)dx\right)f_Y\left(y\right)dy\hfill \\ \hfill & ={\int }_{-\infty }^{+\infty }{F}_X\left(y\right)f_Y\left(y\right)dy\hfill \end{array}$$

(4)

This formula, thus, presents a solution for estimating the join selectivity estimation. Next, we discuss how to translate it into an algorithm.

5. Implementation in a Database System

In RDBMS implementations, histograms are used as a discrete approximation of the PDF and CDF of attributes. This section maps the theory above into an implementable solution in databases using equi-depth histograms.

5.1. Selectivity Estimation

5.1.1. Restriction Selectivity Estimation

Recall that restriction selectivity estimation is about estimating the selectivity of a predicate in the following form:

• SELECT ∗
• FROM R1
• WHERE R1.X < c

As described in Section 4, restriction selectivity estimation can be calculated using the CDF of X (Equation (3)). Deriving the CDF from an equi-depth histograms is presented in Equation (2). To find the bin, $B_j$, where c is contained, one can perform a binary search over the histogram boundaries.

Algorithm 1 illustrates the estimation of restriction selectivity. The $hist$ array represents the equi-depth histogram, stored as an ordered array of bin boundaries. The function binary_search returns the greatest index in this array that is less than or equal to a given constant, effectively finding the bin where such a constant falls. The rest of the algorithm computes the CDF at c using Equation (2).

Algorithm 1: Restriction selectivity estimation for the expression R1.X < c.

Using the running example and the following query:

• SELECT ∗
• FROM R1
• WHERE R1.X < 30

This query yields eight rows, which corresponds to a selectivity of $\frac{2}{3}$. Using Algorithm 1 with the histograms presented in the running example, the number 30 will be found in ${B_X}_2$, meaning that the estimated selectivity will be $\frac{2}{3}+\frac{1}{3}\frac{30-25}{20}=\frac{9}{12}$. After multiplying by the attribute’s cardinality (12), we obtain the estimated row count of nine, which is a close estimate to the actual result size.

Figure 2 shows a graphical depiction of the PDF of the $R1.X$, which is directly obtainable from its equi-depth histogram. The integral in Equation (2), as well as the estimation calculated above using Algorithm 1 correspond to the highlighted area in the figure.

5.1.2. Join Selectivity Estimation

Join selectivity estimation is mapped into a double integral involving the two PDFs. Equation (4) illustrates that join selectivity can be estimated by using the CDF of X and the PDF of Y. These can be calculated using Equations (1) and (2).

The CDF of X is linear piecewise, each piece being defined in a bin of X’s histogram. The PDF of Y is a step function, i.e., constant piecewise, where each piece is defined in a bin of Y’s histogram. This leads to the conclusion that their product, which is needed in Equation (4), is a linear piecewise function, with every piece being defined in an intersection of X and Y’s bins (see Figure 3 for a graphical depiction of this using the running example introduced in Section 2.2). By merging the bounds of the two histograms of X and Y in a single sorted array, $sync$, the intersections of X and Y’s bins will be of the form $[syn{c}_j,syn{c}_{j+1}[$.

From Equation (1), we know that $f_Y$ is 0 for all values until the first value of $his{t}_Y$, and it is 0 after the last value of $his{t}_Y$. From Equation (2), we know that $F_X$ is 0 for all values until the first value of $his{t}_X$, and it is 1 after the last value of $his{t}_X$.

Analyzing the product $F_X·f_Y$ piecewise:

$$(F_X·f_Y)\left(c\right)=\left\{\begin{array}{cc}0\hfill & c\le max({his{t}_X}_0,{his{t}_Y}_0)\hfill \\ F_X\left(c\right)·f_Y\left(c\right)\hfill & max({his{t}_X}_0,{his{t}_Y}_0)<c\hfill \\ \hfill & <min({his{t}_X}_{n_X-1},{his{t}_Y}_{n_Y-1})\hfill \\ f_Y\left(c\right)\hfill & {his{t}_X}_{n_X-1}\le c<{his{t}_Y}_{n_Y-1}\hfill \\ 0\hfill & c\ge max({his{t}_X}_{n_X-1},{his{t}_Y}_{n_Y-1})\hfill \end{array}\right.$$

(5)

Since the product of $F_X$ and $f_Y$ is linear in each interval of the form $[syn{c}_j,syn{c}_{j+1}[$, Equation (4) can be discretized as follows:

$$\begin{array}{cc}\hfill P(X<Y)& ={\int }_{-\infty }^{+\infty }(F_X·f_Y)\left(y\right)dy\hfill \\ \hfill & =\sum _{k=0}^{n_X+n_Y-1}{\int }_{syn{c}_k}^{syn{c}_{k+1}}(F_X·f_Y)\left(y\right)dy\hfill \\ \hfill & =\sum _{k=0}^{n_X+n_Y-1}\frac{(F_X·f_Y)\left(syn{c}_k\right)+(F_X·f_Y)\left(syn{c}_{k+1}\right)}{2}(syn{c}_{k+1}-syn{c}_k)\hfill \end{array}$$

(6)

To maximize code re-use, it is possible to reorganize this equation into a sum of the CDFs of both X and Y, i.e., so that we can reuse Algorithm 1. The following is derived directly from Equation (3):

$$\begin{array}{cc}\hfill f_Y\left(syn{c}_k\right)(syn{c}_{k+1}-syn{c}_k)& =f_Y\left(syn{c}_{k+1}\right)(syn{c}_{k+1}-syn{c}_k)\hfill \\ \hfill & ={\int }_{syn{c}_k}^{syn{c}_{k+1}}{f}_Y\left(y\right)dy\hfill \\ \hfill & =F_Y\left(syn{c}_{k+1}\right)-F_Y\left(syn{c}_k\right)\hfill \end{array}$$

(7)

and the first two steps rely on the fact that $f_Y$ is constant in the interval $[syn{c}_k,syn{c}_{k+1}[$.

Equation (6) can now be re-written using only CDFs of X and Y as follows:

$$\begin{array}{cc}\hfill P(X<Y)& =\sum _{k=0}^{n_X+n_Y-1}\frac{(F_X·f_Y)\left(syn{c}_k\right)+(F_X·f_Y)\left(syn{c}_{k+1}\right)}{2}(syn{c}_{k+1}-syn{c}_k)\hfill \\ \hfill & =\frac{1}{2}\sum _{k=0}^{n_X+n_Y-1}(F_X\left(syn{c}_k\right)+F_X\left(syn{c}_{k+1}\right))(F_Y\left(syn{c}_{k+1}\right)-F_Y\left(syn{c}_k\right))\hfill \end{array}$$

(8)

Algorithm 2 illustrates the estimation of the join selectivity of the less than (<) operator using this equation. It, thus, has the advantage of re-using Algorithm 1 to compute $F_X$ and $F_Y$. The creation of the $sync$ array in Algorithm 2 comes at the expense of the time and space of O($n_X+n_Y$), for duplicating and merging the two sorted histograms. To optimize this, the two histograms can be scanned in parallel, without the need to materialize the sync array. This also allows for further optimization. The algorithm only needs to iterate over the overlapping region of both histograms. All the partial sums before that will be zero, as can be verified in Figure 4. After the overlapping region, the remaining partial sums are equal to what is left of the histogram of Y, $1-{cu{r}_F}_Y$, because all remaining values of Y will be greater than the maximum value in X. We adopted these optimizations in our implementation, yet we omitted them here for the clarity of presentation.

Algorithm 2: Join selectivity estimation algorithm for the less than (<) operator re-using Algorithm 1 as $F_X$ and $F_Y$.

The goal of Algorithm 2 is to calculate the area under the curve of the product $F_X·f_Y$, represented in Figure 4. Taking the three-bin histograms calculated in Section 2.3 from the running example attributes $R1.X$ and $R2.Y$, a materialized $sync$ array would have the values [10, 15, 20, 25, 39, 45, 50] after merging, sorting, and removing duplicates. These correspond to the boundaries of the pieces in which the product $F_X·f_Y$ is linear. Stepping through Algorithm 2 with k from 1 to 5, corresponding to the six pieces represented by the sync array, we arrive at the following sum:

$$\begin{array}{cc}\hfill P(R1.X<R2.Y)=& \frac{1}{2}\left((0+\frac{1}{6})\times (0-0)+(\frac{1}{6}+\frac{1}{3})\times (\frac{1}{3}-0)\right.\hfill \\ \hfill & +(\frac{1}{3}+\frac{2}{3})\times (\frac{8}{19}-\frac{1}{3})+(\frac{2}{3}+\frac{9}{10})\times (\frac{2}{3}-\frac{8}{19})\hfill \\ \hfill & \left.+(\frac{9}{10}+1)\times (\frac{28}{33}-\frac{2}{3})+(1+1)\times (1-\frac{28}{33})\right)\hfill \\ \hfill =& \frac{24221}{37620}\approx 0.643833\hfill \end{array}$$

The correct result will have 95 rows, which corresponds to a selectivity of $\frac{95}{144}\approx 0.659722$. After multiplying by the cardinality of the Cartesian product of both attributes (144) and rounding the result to the nearest integer, we obtain the estimated row count of $92.712\approx 93$, which is close to the correct result size.

Figure 4 shows a graphical depiction of the product of the CDF of $R1.X$ and the PDF of $R1.Y$, which is directly obtainable from their equi-depth histograms. The integral in Equation (6), as well as the estimation calculated above using Algorithm 2 correspond to the area under the curve in the figure.

Figure 4 illustrates the multiplication of the CDF($R1.X$) and the PDF($R2.Y$). The integral in Equation (4), as well as its estimation in Algorithm 4 correspond to calculating the area under the curve in the figure.

Note that the code re-use in Algorithm 2 has a small performance impact. This algorithm has a time complexity of O($(n_X+n_Y)log(n_X+n_Y)$), since it performs a binary search (twice) for each element of each histogram. This binary search is not necessary since the two histograms are being scanned sequentially and the current indices are known at each iteration. One way to avoid this overhead would be to optionally specify j as an input parameter of Algorithm 1, thus reducing the time complexity to O($n_X+n_Y$).

5.2. Extending to All Scalar Inequality Operators

Given the restriction and join selectivity estimators for the less than inequality, all scalar inequality operators can be implemented by noting the following equivalences:

Restriction selectivity:

• $P\left(X>=c\right)=1-P(X<c)$
• $P(X>c)=1-P(X<c)-P(X=c)$
• $P\left(X<=c\right)=P(X<c)+P(X=c)$

Join selectivity:

• $P\left(X>=Y\right)=1-P(X<Y)$
• $P(X>Y)=P(Y<X)$
• $P\left(X<=Y\right)=P(X<Y)+P(X=Y)$

Estimators for equality selections and joins are already implemented by almost all common systems. In case they are missing, one could assume P(X = c) and P(X = Y) to be zero, thus leading to under-/over-estimation of the selectivity.

5.3. Making Use of Other Statistics

Typically, RDBMS will collect statistics about nulls, in the form of a fraction of null values, and Most-Common Values (MCVs), in the form of a singleton histogram. Histograms will, thus, be constructed for the remaining part of the data. When the histogram statistics only refer to a fraction of the data, the methods described up to this point only provide an estimation for this fraction. The final estimation must, thus, take nulls and MCV into account.

As a general way to integrate such other statistics in the estimation besides the histograms, we note the following: given a non-overlapping partitioning of the data, if each partition j corresponds to a fraction $p_j$ of the original data and selectivity within that partition is $s_j$, the final selectivity can be calculated by the inner product $p·s$.

Since a value is either null, a most-common value, or accounted for by the histogram, the overall restriction selectivity can be calculated by the following formula:

$$p·s=p_{null}{s}_{null}+p_{mcv}{s}_{mcv}+p_{hist}{s}_{hist}$$

(9)

5.3.1. Null Values

All inequality operators are strict; this means that the selectivity of null values is 0. For this reason, the first term in Equation (9) is also 0.

5.3.2. Most-Common Values

MCV statistics maintain pairs of values and their frequencies in the table. They are maintained for the top k frequent values, where k is a statistics collection parameter. Since MCVs represent the data in their original form, it is possible to accurately compute the selectivity for these values.

To estimate the restriction selectivity of an operator using the most-common values, Algorithm 3 can be used. This algorithm computes the selectivity of a Boolean operator on a list of most-common values by adding the frequencies of the most-common values satisfying this Boolean condition.

Algorithm 3: mcv_selectivity(values, fractions, n, op), estimates the restriction selectivity, using only the MCV statistics, for a given Boolean operator op.

For join selectivity estimation, since there is the need to combine statistics from null values, MCVs, and equi-depth histograms for both X and Y, there are nine cases that need consideration, depending on the combination of the values of X and Y, as shown in Figure 5.

For strict operators, such as inequalities, only Cases 5, 6, 8, and 9 need to be calculated. This is because nulls result in empty joins. Case 9 was already handled in Algorithm 2. For Case 5, we iterate over the values in the MCV of Y. For each value, we multiply the fraction represented by that value in Y times the MCV restriction selectivity of the operator in question with the current value of the MCV of Y as the constant. This process is described in Algorithm 4.

Algorithm 4: Join selectivity estimation algorithm for any binary Boolean operator re-using Algorithm 3 as mcv_selectivity(values, fractions, n, op).

    For Cases 6 and 8, Algorithm 5 is used, by swapping the arguments. For each common value in the statistics of X, multiply its fraction by the histogram restriction selectivity of Y using the current value of X as the constant.

Algorithm 5: Join selectivity estimation algorithm for the less than (<) operator re-using Algorithm 1 as $F_Y$.

    In terms of Algorithms 4 and 5, the selectivity of the less than (<) operator involving histograms and most-common values can be estimated by the following formula:

$$\begin{array}{cc}\hfill hist\_mcv\_selectivity=& p_{his{t}_X}{p}_{his{t}_Y}{s}_{his{t}_X\times his{t}_Y}\hfill \\ \hfill +& p_{his{t}_X}{p}_{mc{v}_Y}{s}_{his{t}_X\times mc{v}_Y}\hfill \\ \hfill +& p_{mc{v}_X}{p}_{his{t}_Y}{s}_{mc{v}_X\times his{t}_Y}\hfill \\ \hfill +& p_{mc{v}_X}{p}_{mc{v}_Y}{s}_{mc{v}_X\times mc{v}_Y}\hfill \end{array}$$

(10)

The final selectivity taking null values into account can be estimated as follows:

$$selectivity=(1-p_{null})\times hist\_mcv\_selectivity$$

(11)

5.4. Implementation for Ranges and Multiple Ranges

The algorithms described above are for scalar types. An advanced type, which is implemented by many database systems, is the range type. A range type is a tuple (left, right, lc, rc), where left <= right are two values of a domain with a total order. lc and rc specify whether, respectively, the left and right bounds are included in the range. The range type can be parameterized by the type of its bounds, e.g., range (float), range (timestamp), etc. In this section, we describe how the selectivity estimation in previous sections can be applied to the range type and the respective operators.

PostgreSQL, as an example of a DBMS that has range types, collects the statistics for range attributes in the form of two equi-depth histograms: one for the lower bounds of the ranges and one for the upper bounds. In the following, let $X,Y$ be attributes of the same range type. Furthermore, let $X.lower$ be the variable that represents all the lower bounds of X, $X.upper$ be the variable that represents all the upper bounds of X, and similarly for Y. Then, it is possible to estimate the selectivity of the different range operators as follows:

• $P(X<<Y)=P(X.upper<Y.lower)$, where $<<$, reads strictly left of, yields true when X ends before Y starts;
• $P(X>>Y)=P(X.lower>Y.upper)$, where $>>$, reads strictly right of, yields true when X starts after Y ends;
• $P(X$ &$<Y)=P(X.upper<Y.upper)$, where &<, reads X does not extend to the right of Y, yields true when X ends before the end of Y;
• $P(X$ &$>Y)=P(X.lower<Y.lower)$, where &>, reads X does not extend to the left of Y, yields true when X starts before Y starts;
• $P(X\&\&Y)=1-P(X<<Y)-P(X>>Y)$, where $\&\&$ indicates the overlapping between X and Y;
• And so on.

It is, however, not possible to accurately estimate the join selectivity of the operators that express total or partial containment. This is mainly because the lower and upper histograms assume independence between the range bounds. For containment operators, we need to relate the two bounds, which explicitly breaks this assumption.

Another consideration in estimating the selectivity of range operators is the fraction of empty ranges since these are not accounted for by the histograms. Depending on the operator, empty ranges are either always included or always excluded when compared to non-empty ranges and, similarly, when compared to other empty ranges.

6. Experiments

This section evaluates the proposed algorithm in two ways. Firstly, we evaluated the join selectivity estimation accuracy of the algorithm and its relation to the size of the histogram statistics, i.e., the number of bins. In a second step, the accuracy of the proposed solution was compared to the existing solutions in Oracle, SQL-Server, MySQL, and PostgreSQL. The algorithm was implemented in PostgreSQL 15, including support for range operators as described in Section 5.4. The code has been submitted as a patch, and it is currently under review for inclusion in the next release of PostgreSQL. The patch is also included as an artifact with this paper.

The experiments described in this section were run using our implementation in PostgreSQL 15-develop on a Debian virtual machine with 32 GB of disk and 8 GB of RAM. For the first set of experiments, we created two relations, R1 and R2, with range attributes R1.X and R2.Y and cardinalities of 20,390 and 20,060 rows, respectively. Note that, for range types, we used the same algorithm as for scalar types, so the results of this experiment hold for both.

The range values in the two relations were generated to cover a mixture of short, medium, and long ranges. We also included corner cases such as ranges with infinite bounds, empty ranges, and null values in the two relations. The following query was executed varying the number of histogram bins:

• SELECT ∗
• FROM R1, R2
• WHERE x << y

Note that << denotes the “strictly left of” operator, which returns true if and only if the upper bound of x is less than the lower bound of y. PostgreSQL collects bounds’ histogram statistics for range attributes. To estimate the selectivity of the query above, the histograms of the upper bound of X and lower bound of Y were used as the input for Algorithm 2.

The optimizer statistics collector of PostgreSQL has a parameter called statistics target, which controls the number of bins in the equi-depth histograms. The default value is 100, and it can be increased up to 10,000. The experiments described in this section were run by incrementing the statistics target by steps of 100 starting with the default value till the maximum value.

In the experiment, we observed two quantities: (1) the planning time, which is the time taken by the query optimizer to enumerate the alternative query plans and estimate their costs, and (2) the cost estimation error defined as the absolute difference between the estimated and the actual number of rows returned by the query, divided by the cardinality of the Cartesian product of the two relations, which is 409,013,259.

Figure 6 shows the change in planning time (in milliseconds) as the statistics target increases. Apart from two outliers, the planning time showed approximately linear behavior, indicating that the binary search does not have a significant impact on it in the allowed range of statistics targets. Recall that the analytical complexity is O($(n_X+n_Y)log(n_X+n_Y)$).

Figure 7 shows the estimation error (in a logarithmic scale) against the number of histogram bins, i.e., by varying the statistics target. As expected, using more bins lead to a lower estimation error. Even with a small histogram of 100 histogram bins, the estimation error was insignificant, amounting to about 1.112%. This error dropped rapidly to less than 0.002% at 900 bins, which corresponds to less than 5% of each relation size. The error stayed consistently around this value for the histograms bigger than 900 bins.

Figure 8 plots the selectivity estimation error against the planning time in milliseconds. The significance of this figure is to illustrate the relation of the expenditure in terms of planning time versus the gain in terms of reduced error. This figure shows that, for the relations used, the planning time did not need to exceed 2 milliseconds to obtain extremely accurate estimations.

Next, we compared our algorithm with the existing solutions used in Oracle, SQL-Server, MySQL, and PostgreSQL. For this experiment, we generated three relations, U, N, and Z, with 1000 rows each. Relation U contains 10 columns (U.A to U.J) with uniformly sampled data from a range starting at $0,100,200,300,400,500,600,700,800,900$, respectively, with each range being 1000 units wide. Relations N and Z each contain 10 column (N.A to N.J, and Z.A to Z.J) with data sampled from a normal distribution with a mean of 1000 and a standard deviation of 300 and a Zipfian distribution with coefficient $s=1.3$, respectively. The following three queries were then run for each pair of columns:

• Q1: SELECT ∗ FROM U AS U1, U AS U2 WHERE U1.A < U2.column
• Q2: SELECT ∗ FROM U, N WHERE U.column < N.column
• Q3: SELECT ∗ FROM U, Z WHERE U.column < Z.column

Figure 9 shows the average estimation error for each query, as returned by the tested DBMS. Our solution is denoted as PostgreSQL Patched. Note that we used a logarithmic scale for the y-axis; otherwise, our solution would not be visible on the graph. As we can see, the results for MySQL and the existing PostgreSQL solution were equally bad. This was expected, as both system returned a constant selectivity of $1/3$ for all queries. Oracle and SQL-Server returned better estimates for Query 1 (below 0.5% error), but struggled with the normal and Zipfian distribution. Our estimator consistently computed fairly accurate estimates for all data distributions, with average estimation errors of 0.02%, 0.05%, and 0.04% for Q1, Q2, and Q3 respectively.

As explained in the Introduction, providing accurate selectivity estimations is crucial for the database optimizer to choose the best execution plan for a specific query. To illustrate the impact of choosing a good or a bad plan, the following simple query will be used:

• SELECT ∗ FROM U, N, Z WHERE U.A < N.A AND U.A < Z.A

Since this query combines two inequality joins, it is beneficial to first compute the join with the lowest selectivity. In this case, this corresponds to the join between U and Z. However, the execution plan returned by the current PostgreSQL optimizer computes the join between U and N first. This is because the returned selectivity estimates are not accurate enough for the optimizer to apply the reordering. In our current implementation, however, the optimizer receives accurate selectivity estimates and is able to reorder the joins. This reordering decreases the query duration from 89 s to 48 s, which is a 1.8× increase in speed. This shows how important accurate selectivity estimations can be when combining multiple joins in the same query.

7. Conclusions and Possible Future Work

This paper proposed an algorithm for estimating the selectivity of inequality join predicates. It is a fundamental problem in databases that has not been solved before to our knowledge. This work covered this theoretical gap and further implemented the solution in the open-source database PostgreSQL.

We estimated the inequality join selectivity of two relations using the joint probability density function of the common join attribute. In database systems, the integral is discretized using equi-depth histogram synopses for the join attributes. We further structured the join selectivity calculations to reuse the selectivity estimation when one of the arguments is a constant. Since the latter is already implemented in common database systems, the estimator of join selectivity builds on existing implementations in a modular fashion. The experimental results validated the high accuracy of estimation, while the query planning time remained in the acceptable range of a few milliseconds.

This paper generalized the selectivity estimation algorithm to support scalar and 1D range types. As an extension, it would be interesting to support multi-dimensional ranges. This is of particular interest to spatial (2D, 3D) and spatiotemporal (3D, 4D) databases.