Optimization of Analytic Window Functions

Summary: Optimizes analytic window function evaluation beyond the standard naive sorting, introducing several efficient techniques to optimize a workload consisting of multiple window functions. Integrated into PostgreSQL; large-scale experiments on TPC-DS and synthetic data demonstrate substantial speedups over existing approaches. (summarized by gpt-5-nano on Feb 09 2026)

Paper ID: h2d7f051389cd1e3f
Venue: VLDB
Year: 2012
Pagerank: 6.968919e-05
Overall Rank: 3,842 | 74.25%
DOI: 10.14778/2350229.2350252

Incoming Non-self Citations Over Time

Authors

1. Yu Cao (EMC Labs; National University of Singapore)
2. Chee-Yong Chan (National University of Singapore)
3. Jie Li (Duke University)
4. Kian-Lee Tan (National University of Singapore)

BibTeX Citation

@article{cao_vldb12,
        title = {{Optimization of Analytic Window Functions}},
        author = {Cao, Yu and Chan, Chee-Yong and Li, Jie and Tan, Kian-Lee},
        journal = {PVLDB},
        series = {{VLDB} '12},
        volume = {5},
        number = {11},
        pages = {1244--1255},
        doi = {10.14778/2350229.2350252},
        url = {https://doi.org/10.14778/2350229.2350252},
        year = {2012}
}

Incoming Citations (Sorted by Pagerank)

Showing 7 of 7 citing papers.

Rank	Citing Paper	Year	Venue	Pagerank
2,527	Adaptive and Big Data Scale Parallel Execution in Oracle	2013	VLDB	8.3243069e-05
3,438	Efficient Processing of Window Functions in Analytical SQL Queries	2015	VLDB	7.2807703e-05
6,049	Incremental Computation of Common Windowed Holistic Aggregates	2016	VLDB	5.8866676e-05
7,887	Building Advanced SQL Analytics From Low-Level Plan Operators	2021	SIGMOD	5.4162508e-05
8,848	Efficient Evaluation of Arbitrarily-Framed Holistic SQL Aggregates and Window Functions	2022	SIGMOD	5.2492386e-05
9,687	Window Function Expression: Let the Self-join Enter	2024	VLDB	5.1258854e-05
10,947	Window Function Optimization: Co-Evaluation and Other Techniques	2026	VLDB	4.9605343e-05

Outgoing Citations (Sorted by Pagerank)

Showing 9 of 9 cited papers.

Citations counted here include only citations to other VLDB/SIGMOD/CIDR/PODS papers in this database.

Rank	Cited Paper	Year	Venue	Pagerank
1	Access Path Selection in a Relational Database Management System	1979	SIGMOD	0.0023948467
214	On the Computation of Multidimensional Aggregates	1996	VLDB	0.00024576033
726	Fundamental Techniques for Order Optimization	1996	SIGMOD	0.0001443284
1,909	Querying Multiple Features of Groups in Relational Databases	1996	VLDB	9.3758651e-05
2,054	WinMagic : Subquery Elimination Using Window Aggregation	2003	SIGMOD	9.1030561e-05
2,122	Enhanced Subquery Optimizations in Oracle	2009	VLDB	8.9938885e-05
3,605	A Combined Framework for Grouping and Order Optimization	2004	VLDB	7.1534371e-05
4,423	Avoiding Sorting and Grouping In Processing Queries	2003	VLDB	6.5900826e-05
5,598	Efficient Computation of Multiple Group By Queries	2005	SIGMOD	6.053914e-05

Semantically Similar Papers

#	Overall Rank	Paper	Year	Venue
1	8,188	Efficient Query Re-optimization with Judicious Subquery Selections	2023	SIGMOD
2	4,607	Window-Aware Load Shedding for Aggregation Queries over Data Streams	2006	VLDB
3	1,680	Semantics and Evaluation Techniques for Window Aggregates in Data Streams	2005	SIGMOD
4	1,447	Continuous Monitoring of Top-k Queries over Sliding Windows	2006	SIGMOD
5	9,687	Window Function Expression: Let the Self-join Enter	2024	VLDB
6	4,708	Static Optimization of Conjunctive Queries with Sliding Windows Over Infinite Streams	2004	SIGMOD
7	9,409	Efficient Approximation of Certain and Possible Answers for Ranking and Window Queries over Uncertain Data	2023	VLDB
8	8,848	Efficient Evaluation of Arbitrarily-Framed Holistic SQL Aggregates and Window Functions	2022	SIGMOD
9	10,947	Window Function Optimization: Co-Evaluation and Other Techniques	2026	VLDB
10	3,438	Efficient Processing of Window Functions in Analytical SQL Queries	2015	VLDB