Optimizing join enumeration in transformation-based query optimizers

Shanbhag, Anil; Sudarshan, S.

doi:10.14778/2732977.2732997

Cited by 8 publications

(5 citation statements)

References 14 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…It is also known as the AND-OR-DAG in [12] where it is used for detecting and unifying common subexpressions for multi-query optimization. It is also used for generating the space of cross-product free join trees in [15]. The LQDAG data structure comprises two distinct node types: equivalence nodes and operation nodes.…”

Section: Preliminary Backgroundmentioning

confidence: 99%

A Fast Plan Enumerator for Recursive Queries

Fejza,

Genevès,

Layaïda

2024

2024 IEEE 40th International Conference on Data Engineering (ICDE)

View full text Add to dashboard Cite

Plan enumeration is one of the most crucial components in relational query optimization. We demonstrate RLQDAG, a system implementation of a top-down plan enumerator for the purpose of transforming sets of recursive relational terms efficiently. We describe a complete system of query optimization with parsers and compilers adapted for recursive queries over knowledge and property graphs. We focus on the enumeration component of this sytem, the RLQDAG, and especially on its efficiency in generating plans out of reach of other approaches. We show graphical representations of explored plan spaces for queries on real datasets. We demonstrate the plan enumerator and its benefits in finding more efficient query plans.

show abstract

Section: Preliminary Backgroundmentioning

confidence: 99%

A Fast Plan Enumerator for Recursive Queries

Fejza,

Genevès,

Layaïda

2024

2024 IEEE 40th International Conference on Data Engineering (ICDE)

View full text Add to dashboard Cite

show abstract

“…In this paper, we do not consider cross products which is a well accepted thumb rule in query optimization [9,28,30]. Furthermore, [9,30] suggest to avoid cartesian joins unless one has a reason such as joining small relations, or in case of division operator.…”

Section: Dependencies Amongmentioning

confidence: 99%

Efficient Massively Parallel Join Optimization for Large Queries

Mancini¹,

Karthik²,

Chandra³

et al. 2022

Preprint

View full text Add to dashboard Cite

Modern data analytical workloads often need to run queries over a large number of tables. An optimal query plan for such queries is crucial for being able to run these queries within acceptable time bounds. However, with queries involving many tables, finding the optimal join order becomes a bottleneck in query optimization. Due to the exponential nature of join order optimization, optimizers resort to heuristic solutions after a threshold number of tables. Our objective is two fold: (a) reduce the optimization time for generating optimal plans; and (b) improve the quality of the heuristic solution.In this paper, we propose a new massively parallel algorithm, MPDP, that can efficiently prune the large search space (via a novel plan enumeration technique) while leveraging the massive parallelism offered by modern hardware (Eg: GPUs). When evaluated on real-world benchmark queries with PostgreSQL, MPDP is at least an order of magnitude faster compared to state-of-the-art techniques for large analytical queries. As a result, we are able to increase the heuristic-fall-back limit from 12 relations to 25 relations with same time budget in PostgreSQL. Also, in order to handle queries with even larger number of tables, we augment MPDP to a well known heuristic, IDP 2 (iterative DP version 2) and a novel heuristic UnionDP. By systematically exploring a much larger search space, these heuristics provides query plans that are up to 7 times cheaper as compared to the state-of-the-art techniques while being faster to compute.

show abstract

“…Furthermore, Fender and Moerkotte [18,19] present a general framework to handle non-inner joins and a more generalized graph, hyper graph for top-down join enumeration. RS-Graph, a new join transformation rule based on top-down join enumeration, is presented in [80] to efficiently generate the space of cross-product free join trees.…”

Section: Top-down Strategiesmentioning

confidence: 99%

A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration

2021

View full text Add to dashboard Cite

Query optimizer is at the heart of the database systems. Cost-based optimizer studied in this paper is adopted in almost all current database systems. A cost-based optimizer introduces a plan enumeration algorithm to find a (sub)plan, and then uses a cost model to obtain the cost of that plan, and selects the plan with the lowest cost. In the cost model, cardinality, the number of tuples through an operator, plays a crucial role. Due to the inaccuracy in cardinality estimation, errors in cost model, and the huge plan space, the optimizer cannot find the optimal execution plan for a complex query in a reasonable time. In this paper, we first deeply study the causes behind the limitations above. Next, we review the techniques used to improve the quality of the three key components in the cost-based optimizer, cardinality estimation, cost model, and plan enumeration. We also provide our insights on the future directions for each of the above aspects.

show abstract

Optimizing join enumeration in transformation-based query optimizers

Cited by 8 publications

References 14 publications

A Fast Plan Enumerator for Recursive Queries

A Fast Plan Enumerator for Recursive Queries

Efficient Massively Parallel Join Optimization for Large Queries

A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration

Contact Info

Product

Resources

About