Skip to main content

Optimizing iceberg queries with complex joins

Publication ,  Conference
Walenz, B; Roy, S; Yang, J
Published in: Proceedings of the ACM SIGMOD International Conference on Management of Data
May 9, 2017

Iceberg queries, commonly used for decision support, find groups whose aggregate values are above or below a threshold. In practice, iceberg queries are often posed over complex joins that are expensive to evaluate. This paper proposes a framework for combining a number of techniques-a-priori, memoization, and pruning- to optimize iceberg queries with complex joins. A-priori pushes partial GROUP BY and HAVING condition before a join to reduce its input size. Memoization caches and reuses join computation results. Pruning uses cached results to infer that certain tuples cannot contribute to the final query result, and short-circuits join computation. We formally derive conditions for correctly applying these techniques. Our practical rewrite algorithm produces highly efficient SQL that can exploit combinations of optimization opportunities in ways previously not possible. We evaluate our PostgreSQL-based implementation experimentally and show that it outperforms both baseline PostgreSQL and a commercial database system.

Duke Scholars

Published In

Proceedings of the ACM SIGMOD International Conference on Management of Data

DOI

ISSN

0730-8078

ISBN

9781450341974

Publication Date

May 9, 2017

Volume

Part F127746

Start / End Page

1243 / 1244
 

Citation

APA
Chicago
ICMJE
MLA
NLM
Walenz, B., Roy, S., & Yang, J. (2017). Optimizing iceberg queries with complex joins. In Proceedings of the ACM SIGMOD International Conference on Management of Data (Vol. Part F127746, pp. 1243–1244). https://doi.org/10.1145/3035918.3064053
Walenz, B., S. Roy, and J. Yang. “Optimizing iceberg queries with complex joins.” In Proceedings of the ACM SIGMOD International Conference on Management of Data, Part F127746:1243–44, 2017. https://doi.org/10.1145/3035918.3064053.
Walenz B, Roy S, Yang J. Optimizing iceberg queries with complex joins. In: Proceedings of the ACM SIGMOD International Conference on Management of Data. 2017. p. 1243–4.
Walenz, B., et al. “Optimizing iceberg queries with complex joins.” Proceedings of the ACM SIGMOD International Conference on Management of Data, vol. Part F127746, 2017, pp. 1243–44. Scopus, doi:10.1145/3035918.3064053.
Walenz B, Roy S, Yang J. Optimizing iceberg queries with complex joins. Proceedings of the ACM SIGMOD International Conference on Management of Data. 2017. p. 1243–1244.

Published In

Proceedings of the ACM SIGMOD International Conference on Management of Data

DOI

ISSN

0730-8078

ISBN

9781450341974

Publication Date

May 9, 2017

Volume

Part F127746

Start / End Page

1243 / 1244