Optimizing Window Aggregate Functions in Relational Database Systems

Abstract

The window function has become an important OLAP extension of SQL since SQL:2003, and is supported by major commercial RDBMSs (e.g. Oracle, DB2, SQL Server, Teradata and Pivotal Greenplum) and by emerging Big Data platforms (e.g. Google Tenzing, Apache Hive, Pivotal HAWQ and Cloudera Impala). Window functions are designed for advanced data analytics use cases, bringing significant functional and performance enhancements to OLAP and decision support applications. However, we identify that existing window function evaluation approaches are still with significant room for improvement. In this paper, we revisit the conventional two-phase evaluation framework for window functions in relational databases, and propose several novel optimization techniques which aim to minimize the redundant data accesses and computations during the function calls invoked over window frames. We have integrated the proposed techniques into PostgreSQL, and compared them with both PostgreSQL’s and SQL Server’s native window function implementation over the TPC benchmark. Our comprehensive experimental studies demonstrate significant speedup over existing approaches.

A  Analysis of TF

Lemma 1

In ROWS model, the size of two adjacent frames in a partition differs at most one.

Proof: Let $f_{i}$ and $f_{i+1}$ ($0<i<N$) be two adjacent frames in a partition. The size difference can be expressed as $sd_i=|(f_{i+1}.t-f_{i}.t) - (f_{i+1}.h-f_{i}.h)|$. Since frame head and frame tail moves at most one step ahead at one time, $0 \leqslant f_{i+1}.h-f_{i}.h \leqslant 1$ and $0 \leqslant f_{i+1}.t-f_{i}.t \leqslant 1$, then $0 \leqslant sd_i \leqslant 1$, Lemma 1 is proved.

• $G_{j}$: The j’th group in a partition.

• $k_{j}$: The number of frames which use the same TF in the group $G_{j}$.

The frames are divided into groups and frames in each group share the same TTV value, which is calculated when calculating the TV of the first frame in the group. Since two partitions share nothing, their cost could be analysed separately.

For purpose of simplicity to build the cost model, we set the whole table as a partition. Then the partition size is N. We first assume each frame has the same frame size F and frame head changes for each frame. Group $G_{j}$ contains the $k_{j}+1$ frames, $f_{i}$, $f_{i+1}$, $f_{i+2}$, ... , $f_{i+k_{j}}$. Let o denotes the overlap of the group of frames from $f_{i}$ to $f_{i+k_{j}}$ ($k_{j}>0$), then $o=f_{i}\cap f_{i+1}\cap ... \cap f_{i+k_{j}}$, it’s not hard to get that o starts from $f_{i+k_{j}}.h$ and ends at $f_{i}.t$. And apparently $f_{i}.t$ is bigger than $f_{i+k_{j}}.h$, because it is utterly useless if the overlap is empty. So $s = f_{i+k_{j}}.h$ and it will not be changed until it’s out of the range of current frame. Besides, e is initialized to $f_{i_{j}}.t$ and assigned to the tail position of current frame in the following calculation. So in the end, e will be equal to $f_{i+k_{j}}.t$.

We can get that, the start position s of TF is associated with $k_{j}$. So, if we want to know s, we must confirm $k_{j}$ firstly. Next, we will give the analysis of $k_{j}$.

For a group $G_{j}$ which contains $k_{j}$+1 frames, the calculation cost consists of two components, one is related to the calculation and updating of TTV, the other one is related to the calculation of each transition value (TV). Since the TF is finally identical with the last frame, the cost on TTV is $F\times T_{CPU}$. For the first frame, all rows need to be aggregated, for other frames, approximatively only rows before the overlap (the number of the rows for $f_{i+1}$ is k-1, the number of the rows for $f_{i+2}$ is k-2, ... , the number of the rows for $f_{i+k}$ is 0. So, the sum is k(k-1)/2) and the new entering row (For every frame, there is just one row entering the frame) needs to be aggregated. So the calculation cost related to TV is $(F+k\times (k-1)/2+k)\times T_{CPU}$. For each group $G_{j}$, the calculation cost of TF-based scheme is:

$$\begin{aligned} C_{cal}=(2F+k_{j}\times \frac{k_{j}+1}{2}) \times T_{CPU} \end{aligned}$$

(3)

So, for the partition, the total calculation cost is:

$$\begin{aligned} C_{cal}=\sum _{j=1}^{t}[(2F+k_{j}\times \frac{k_{j}+1}{2}) \times T_{CPU}] \end{aligned}$$

(4)

Since the number of frames in the partition is N, we get this:

$$\begin{aligned} \sum _{j=1}^{t}(k_{j}+1) = N \end{aligned}$$

(5)

By the Formulas 4 and 5, we get this:

$$\begin{aligned} f=\frac{1}{T_{CPU}} \times C_{cal}=2Ft+\frac{1}{2} \times \sum _{j=1}^{t}({k_{j}}^{2}+k_{j}) \end{aligned}$$

(6)

$$\begin{aligned} \varphi = t +\sum _{j=1}^{t}k_{j} - N = 0 \end{aligned}$$

(7)

f takes the minimum value when $C_{cal}$ takes the minimum value. In order to minimize the $C_{cal}$. We use the $Lagrange \ Multiplier$ with Formulas 6 and 7 ($\lambda$ is the new variable):

$$\begin{aligned} L(k_{1},k_{2},...,k_{t}) = f + \lambda \varphi \end{aligned}$$

(8)

As well as:

$$\begin{aligned} L(k_{1},k_{2},...,k_{t}) = 2Ft+\frac{1}{2} \times \sum _{j=1}^{t}({k_{j}}^{2}+k_{j}) + \lambda (t +\sum _{j=1}^{t}k_{j} - N) \end{aligned}$$

(9)

Then we get the simultaneous formulae with a step partial derivative of Formula 9 by $k_{1}, k_{2}, ..., k_{t}$ and Formula 7:

$$\begin{aligned} {\left\{ \begin{array}{ll} k_{1}+\frac{1}{2}+\lambda =0\\ k_{2}+\frac{1}{2}+\lambda =0\\ k_{3}+\frac{1}{2}+\lambda =0\\ .\\ .\\ k_{t}+\frac{1}{2}+\lambda =0\\ t +\sum _{j=1}^{t}k_{j} - N = 0 \end{array}\right. } \end{aligned}$$

(10)

By the Formula 10, we get this:

$$\begin{aligned} k_{1} = k_{2} = ... = k_{t} = k = \frac{N}{t} - 1 \end{aligned}$$

(11)

We can get that, if we give the partition and it is divided into t groups ($N, F, T_{CPU}$ and t have been given), $k_{1}, k_{2}, ... , k_{t}$ is all the same when the cost $C_{cal}$ is taking the minimum. Then we take the Formula 11 into Formula 6 and get this:

$$\begin{aligned} f = 2Ft + \frac{{N}^{2}}{2t} - \frac{N}{2} \end{aligned}$$

(12)

By the Formula 12, we can get that the cost($C_{cal}$) is associated with the number of groups in a partition. So, if we want to get a minimum cost, we should set a appropriate value of t. We get the first-order derivative of the Formula 12:

$$\begin{aligned} {f}^{'} = 2F - \frac{{N}^{2}}{2} \times {t}^{-2} \end{aligned}$$

(13)

Let ${f}^{'}=0$, we get that $t=\frac{N}{2\sqrt{F}}$. Then we take $t=\frac{N}{2\sqrt{F}}$ into Formula 11 and get that $k=2\sqrt{F}-1$.

So, if we have been given a partition and it’s size is P, we set the number of groups in the partition as $\frac{P}{2\sqrt{F}}$. That means that each group contains $2\sqrt{F}$ frames. For each group, the start position of TF is ${f}_{i+\lfloor 2\sqrt{F}\rfloor -1}.h$, $s={f}_{i}.h+\lfloor 2\sqrt{F}\rfloor -1$.

In the experiments, we tried different frame sizes. From the experiment results, we can see that the TF algorithm have a good performance.