Optimization of Analytic Window Functions

Analytic functions represent the state-of-the-art way of perform-ing complex data analysis within a single SQL statement. In par-ticular, an important class of analytic functions that has been fre-quently used in commercial systems to support OLAP and decision support applications is the class of window functions. A window function returns for each input tuple a value derived from applying a function over a window of neighboring tuples. However, exist-ing window function evaluation approaches are based on a naive sorting scheme. In this paper, we study the problem of optimiz-ing the evaluation of window functions. We propose several effi-cient techniques, and identify optimization opportunities that allow us to optimize the evaluation of a set of window functions. We have integrated our scheme into PostgreSQL. Our comprehensive experimental study on the TPC-DS datasets as well as synthetic datasets and queries demonstrate significant speedup over existing approaches. 1