Document Open Access Logo

Algorithms for Optimizing Acyclic Queries

Authors Zheng Luo , Wim Van den Broeck , Guy Van den Broeck , Yisu Remy Wang

PDF

File

Thumbnail PDF
PDF
LIPIcs.ICDT.2026.17.pdf
  • Filesize: 1.41 MB
  • 18 pages

Document Identifiers

Related Versions

Subject Classification

ACM Subject Classification
  • Information systems → Query optimization
  • Theory of computation → Database theory
  • Mathematics of computing → Graph theory
Keywords
  • Query Optimization
  • Join Trees
  • Enumeration

Metrics

Abstract

Most research on query optimization has centered on binary join algorithms like hash join and sort-merge join. However, recent years have seen growing interest in theoretically optimal algorithms, notably Yannakakis' algorithm. These algorithms rely on join trees, which differ from the operator trees for binary joins and require new optimization techniques. We propose three approaches to constructing join trees for acyclic queries. First, we give an algorithm to enumerate all join trees of an α-acyclic query by edits in linear time with amortized constant delay, which forms the basis of a cost-based optimizer for acyclic joins. Second, we show the Maximum Cardinality Search algorithm by Tarjan and Yannakakis constructs the unique shallowest join tree for any Berge-acyclic query, thus enabling parallel execution of large join queries. Finally, we prove that a simple algorithm by Hu et al. converts any connected left-deep linear plan of a γ-acyclic query into a join tree, allowing reuse of optimizers developed for binary joins.

Cite As Get BibTex

Zheng Luo, Wim Van den Broeck, Guy Van den Broeck, and Yisu Remy Wang. Algorithms for Optimizing Acyclic Queries. In 29th International Conference on Database Theory (ICDT 2026). Leibniz International Proceedings in Informatics (LIPIcs), Volume 365, pp. 17:1-17:18, Schloss Dagstuhl – Leibniz-Zentrum für Informatik (2026) https://doi.org/10.4230/LIPIcs.ICDT.2026.17

Author Details

Zheng Luo
  • University of California, Los Angeles, CA, USA
Wim Van den Broeck
  • University of Bergen, Norway
Guy Van den Broeck
  • University of California, Los Angeles, CA, USA
Yisu Remy Wang
  • University of California, Los Angeles, CA, USA

Funding

  • Van den Broeck, Wim: Wim Van den Broeck acknowledges support from the L. Meltzers Høyskolefond and the Research Council of Norway, grant number 326537.

References

  1. Serge Abiteboul, Richard Hull, and Victor Vianu. Foundations of Databases. Addison-Wesley, 1995. URL: http://webdam.inria.fr/Alice/.
  2. Liese Bekkers, Frank Neven, Stijn Vansummeren, and Yisu Remy Wang. Instance-optimal acyclic join processing without regret: Engineering the yannakakis algorithm in column stores. Proc. VLDB Endow., 18(8):2413-2426, 2025. URL: https://www.vldb.org/pvldb/vol18/p2413-vansummeren.pdf.
  3. Michael A. Bender and Martin Farach-Colton. The lca problem revisited. In LATIN 2000, volume 1776 of Lecture Notes in Computer Science, pages 88-94. Springer, 2000. URL: https://doi.org/10.1007/10719839_9.
  4. Michael A. Bender and Martin Farach-Colton. The level ancestor problem simplified. Theoretical Computer Science, 321(1):5-12, 2004. URL: https://doi.org/10.1016/J.TCS.2003.05.002.
  5. Nicolas Bruno, César A. Galindo-Legaria, and Milind Joshi. Polynomial heuristics for query optimization. In Proceedings of ICDE 2010, pages 589-600. IEEE, 2010. URL: https://doi.org/10.1109/ICDE.2010.5447916.
  6. Nofar Carmeli, Batya Kenig, and Benny Kimelfeld. Efficiently enumerating minimal triangulations. In Proceedings of the 36th ACM Symposium on Principles of Database Systems (PODS 2017), pages 273-287. ACM, 2017. URL: https://doi.org/10.1145/3034786.3056109.
  7. Arthur Cayley. A theorem on trees. Quarterly Journal of Pure and Applied Mathematics, 23:376-378, 1889. Google Scholar
  8. Jeremy Chen, Yuqing Huang, Mushi Wang, Semih Salihoglu, and Ken Salem. Accurate summary-based cardinality estimation through the lens of cardinality estimation graphs. Proc. VLDB Endow., 15(8):1533-1545, 2022. URL: https://doi.org/10.14778/3529337.3529339.
  9. Alessandro D'atri and Marina Moscarini. On hypergraph acyclicity and graph chordality. Information Processing Letters, 29(5):271-274, 1988. URL: https://doi.org/10.1016/0020-0190(88)90121-4.
  10. Pablo De Caria and Marisa Gutierrez. On basic chordal graphs and some of its subclasses. Discrete Applied Mathematics, 210:261-276, 2016. URL: https://doi.org/10.1016/j.dam.2015.05.002.
  11. David DeHaan and Frank Wm. Tompa. Optimal top-down join enumeration. In Proceedings of SIGMOD 2007, pages 785-796, 2007. URL: https://doi.org/10.1145/1247480.1247567.
  12. David Eppstein. Representing all minimum spanning trees with applications to counting and generation. UC Irvine, 1995. Google Scholar
  13. Ronald Fagin. Degrees of acyclicity for hypergraphs and relational database schemes. J. ACM, 30(3):514-550, 1983. URL: https://doi.org/10.1145/2402.322390.
  14. Leonidas Fegaras. A new heuristic for optimizing large queries. In Proceedings of DEXA 1998, volume 1460 of Lecture Notes in Computer Science, pages 726-735. Springer, 1998. URL: https://doi.org/10.1007/BFB0054528.
  15. Pit Fender and Guido Moerkotte. Counter strike: Generic top-down join enumeration for hypergraphs. Proc. VLDB Endow., 6(14):1822-1833, 2013. URL: https://doi.org/10.14778/2556549.2556565.
  16. Georg Gottlob, Matthias Lanzinger, Cem Okulmus, and Reinhard Pichler. Fast parallel hypertree decompositions in logarithmic recursion depth. ACM Trans. Database Syst., 49(1):1:1-1:43, 2024. URL: https://doi.org/10.1145/3638758.
  17. Yuxing Han, Ziniu Wu, Peizhi Wu, Rong Zhu, Jingyi Yang, Liang Wei Tan, Kai Zeng, Gao Cong, Yanzhao Qin, Andreas Pfadler, Zhengping Qian, Jingren Zhou, Jiangneng Li, and Bin Cui. Cardinality estimation in dbms: A comprehensive benchmark evaluation. Proc. VLDB Endow., 15(4):752-765, 2021. URL: https://doi.org/10.14778/3503585.3503586.
  18. Frank Harary. A characterization of block-graphs. Canadian Mathematical Bulletin, 6(1):1-6, 1963. Google Scholar
  19. Zongyan He and Jeffrey Xu Yu. A branch-&-bound algorithm for fractional hypertree decomposition. Proc. VLDB Endow., 17(13):4655-4667, 2024. URL: https://www.vldb.org/pvldb/vol17/p4655-he.pdf.
  20. Zeyuan Hu, Yisu Remy Wang, and Daniel P. Miranker. Treetracker join: Simple, optimal, fast. ACM Trans. Database Syst., October 2025. Just Accepted. URL: https://doi.org/10.1145/3774325.
  21. Toshihide Ibaraki and Tiko Kameda. On the optimal nesting order for computing n-relational joins. ACM Trans. Database Syst., 9(3):482-502, 1984. URL: https://doi.org/10.1145/1270.1498.
  22. Sanjiv Kapoor and H. Ramesh. Algorithms for enumerating all spanning trees of undirected and weighted graphs. SIAM Journal on Computing, 24(2):247-265, 1995. URL: https://doi.org/10.1137/S009753979225030X.
  23. Ravi Krishnamurthy, Haran Boral, and Carlo Zaniolo. Optimization of nonrecursive queries. In Proceedings of the 12th International Conference on Very Large Data Bases (VLDB 1986), pages 128-137. Morgan Kaufmann, 1986. URL: http://www.vldb.org/conf/1986/P128.PDF.
  24. Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. How good are query optimizers, really? Proc. VLDB Endow., 9(3):204-215, 2015. URL: https://doi.org/10.14778/2850583.2850594.
  25. Arne Leitert. Computing the union join and subset graph of acyclic hypergraphs in subquadratic time. In WADS 2021, volume 12808 of Lecture Notes in Computer Science, pages 571-584. Springer, 2021. URL: https://doi.org/10.1007/978-3-030-83508-8_41.
  26. Feifei Li, Bin Wu, Ke Yi, and Zhuoyue Zhao. Wander join: Online aggregation via random walks. In Proceedings of the 2016 International Conference on Management of Data (SIGMOD 2016), pages 615-629. ACM, 2016. URL: https://doi.org/10.1145/2882903.2915235.
  27. Zheng Luo, Wim Van den Broeck, Guy Van den Broeck, and Yisu Remy Wang. Algorithms for optimizing acyclic queries, 2026. URL: https://doi.org/10.48550/arXiv.2509.14144.
  28. David Maier. The Theory of Relational Databases. Computer Science Press, 1983. Google Scholar
  29. Guido Moerkotte and Thomas Neumann. Analysis of two existing and one new dynamic programming algorithm for the generation of optimal bushy join trees without cross products. In Proceedings of VLDB 2006, pages 930-941, 2006. URL: http://dl.acm.org/citation.cfm?id=1164207.
  30. Guido Moerkotte and Thomas Neumann. Dynamic programming strikes back. In Proceedings of the ACM SIGMOD International Conference on Management of Data (SIGMOD 2008), pages 539-552. ACM, 2008. URL: https://doi.org/10.1145/1376616.1376672.
  31. Thomas Neumann. Query simplification: Graceful degradation for join-order optimization. In Proceedings of SIGMOD 2009, pages 403-414, 2009. URL: https://doi.org/10.1145/1559845.1559889.
  32. Øystein Ore. Theory of Graphs, volume 38 of American Mathematical Society Colloquium Publications. American Mathematical Society, 1962. URL: https://doi.org/10.1090/coll/038.
  33. Patricia G. Selinger, Morton M. Astrahan, Donald D. Chamberlin, Raymond A. Lorie, and Thomas G. Price. Access path selection in a relational database management system. In Proceedings of SIGMOD 1979, pages 23-34, 1979. URL: https://doi.org/10.1145/582095.582099.
  34. Akiyoshi Shioura, Akihisa Tamura, and Takeaki Uno. An optimal algorithm for scanning all spanning trees of undirected graphs. SIAM J. Comput., 26(3):678-692, 1997. URL: https://doi.org/10.1137/S0097539794270881.
  35. Michael Steinbrunn, Guido Moerkotte, and Alfons Kemper. Heuristic and randomized optimization for the join ordering problem. VLDB Journal, 6(3):191-208, 1997. URL: https://doi.org/10.1007/S007780050040.
  36. Vaishali Surianarayanan, Anikait Mundhra, Ajaykrishnan E. S, and Daniel Lokshtanov. Fast hypertree decompositions via linear programming: Fractional and generalized. Proc. ACM Manag. Data, 3(3):159:1-159:27, 2025. URL: https://doi.org/10.1145/3725296.
  37. Arun N. Swami. Optimization of large join queries: Combining heuristic and combinatorial techniques. In Proceedings of SIGMOD 1989, pages 367-376, 1989. URL: https://doi.org/10.1145/67544.66961.
  38. Robert E. Tarjan and Mihalis Yannakakis. Simple linear-time algorithms to test chordality of graphs, test acyclicity of hypergraphs, and selectively reduce acyclic hypergraphs. SIAM Journal on Computing, 13(3):566-579, 1984. URL: https://doi.org/10.1137/0213035.
  39. Transaction Processing Performance Council. TPC Benchmark H (Decision Support). https://tpc.org/tpc_documents_current_versions/pdf/tpc-h_v3.0.1.pdf, 2023. Standard Specification, Revision 3.0.1.
  40. Qichen Wang, Bingnan Chen, Binyang Dai, Ke Yi, Feifei Li, and Liang Lin. Yannakakis+: Practical acyclic query evaluation with theoretical guarantees. Proc. ACM Manag. Data, 3(3):235:1-235:28, 2025. URL: https://doi.org/10.1145/3725423.
  41. Yisu Remy Wang, Max Willsey, and Dan Suciu. Free join: Unifying worst-case optimal and traditional joins. Proc. ACM Manag. Data, 1(2), 2023. URL: https://doi.org/10.1145/3589295.
  42. Mihalis Yannakakis. Algorithms for acyclic database schemes. In Proceedings of VLDB 1981, pages 82-94, 1981. Google Scholar
  43. C. T. Yu and M. Z. Ozsoyoglu. An algorithm for tree-query membership of a distributed query. In COMPSAC 1979, pages 306-312. IEEE, 1979. URL: https://doi.org/10.1109/CMPSAC.1979.762509.
  44. Tao Yu, Rui Zhang, Kai Yang, Michihiro Yasunaga, Dongxu Wang, Zifan Li, James Ma, Irene Li, Qingning Yao, Shanelle Roman, Zilin Zhang, and Dragomir R. Radev. Spider: A large-scale human-labeled dataset for complex and cross-domain semantic parsing and text-to-sql task. In Proceedings of EMNLP 2018, pages 3911-3921. Association for Computational Linguistics, 2018. URL: https://doi.org/10.18653/v1/d18-1425.
  45. Junyi Zhao, Kai Su, Yifei Yang, Xiangyao Yu, Paraschos Koutris, and Huanchen Zhang. Debunking the myth of join ordering: Toward robust sql analytics. Proc. ACM Manag. Data, 3(3), 2025. URL: https://doi.org/10.1145/3725283.
Any Issues?
X

Feedback on the Current Page

CAPTCHA

Thanks for your feedback!

Feedback submitted to Dagstuhl Publishing

Could not send message

Please try again later or send an E-mail
© 2023-2025 Schloss Dagstuhl – LZI GmbH About DROPS Imprint Privacy Contact