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.