Extensible/rule based query rewrite optimization in Starburst

Pirahesh, Hamid; Hellerstein, Joseph M.; Hasan, Waqar

doi:10.1145/130283.130294

Cited by 159 publications

(42 citation statements)

References 15 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…The running time of UDP on all these rules are within 15 seconds. Many such rewrites involve queries that are beyond UCQ, i.e., they are not part of the decidable fragment of SQL, such as the 3 rewrite rules from Starburst [44] (we described one of them in Sec. 5.4).…”

Section: Characterizing Resultsmentioning

confidence: 99%

See 1 more Smart Citation

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

et al. 2018

View full text Add to dashboard Cite

Deciding the equivalence of SQL queries is a fundamental problem in data management. As prior work has mainly focused on studying the theoretical limitations of the problem, very few implementations for checking such equivalences exist. In this paper, we present a new formalism and implementation for reasoning about the equivalences of SQL queries. Our formalism, Usemiring, extends SQL's semiring semantics with unbounded summation and duplicate elimination. U-semiring is defined using only very few axioms and can thus be easily implemented using proof assistants such as Coq for automated query reasoning. Yet, they are sufficient enough to enable us reason about sophisticated SQL queries that are evaluated over bags and sets, along with various integrity constraints. To evaluate the effectiveness of U-semiring, we have used it to formally verify 68 equivalent queries and rewrite rules from both classical data management research papers and real-world SQL engines, where many of them have never been proven correct before.

show abstract

Section: Characterizing Resultsmentioning

confidence: 99%

“…We prove here a consequence of the key constraint, which we use in Sec. 5.4 to prove some non-trivial SQL identities described by [44]: THEOREM 4.3. If R.k satisfies the key constraint (Def.…”

Section: Axiomatic Interpretation Of Constraintsmentioning

confidence: 99%

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

et al. 2018

View full text Add to dashboard Cite

show abstract

“…A SQL query maps one or more input relations to a (nameless) output relation. For example, if a relation with schema R(a, b) has instance {(1, 40), (2,40), (2,50)} then the SQL query Q1:SELECT a FROM R returns the bag {1, 2, 2}.…”

Section: Overviewmentioning

confidence: 99%

HoTTSQL: proving query rewrites with univalent SQL semantics

Chu

Weitz

Cheung

et al. 2017

Proceedings of the 38th ACM SIGPLAN Conference on Programming Language Design and Implementation

View full text Add to dashboard Cite

Every database system contains a query optimizer that performs query rewrites. Unfortunately, developing query optimizers remains a highly challenging task. Part of the challenges comes from the intricacies and rich features of query languages, which makes reasoning about rewrite rules difficult. In this paper, we propose a machine-checkable denotational semantics for SQL, the de facto language for relational database, for rigorously validating rewrite rules. Unlike previously proposed semantics that are either non-mechanized or only cover a small amount of SQL language features, our semantics covers all major features of SQL, including bags, correlated subqueries, aggregation, and indexes. Our mechanized semantics, called HoTT SQL, is based on K-Relations and homotopy type theory, where we denote relations as mathematical functions from tuples to univalent types. We have implemented HoTT SQL in Coq, which takes only fewer than 300 lines of code and have proved a wide range of SQL rewrite rules, including those from database research literature (e.g., magic set rewrites) and real-world query optimizers (e.g., subquery elimination). Several of these rewrite rules have never been previously proven correct. In addition, while query equivalence is generally undecidable, we have implemented an automated decision procedure using HoTT SQL for conjunctive queries: a well-studied decidable fragment of SQL that encompasses many real-world queries.

show abstract

“…We exemplarily follow the plan with the mrg operator. The operator is added to the plan and subsequently, the set of inputN odes is divided into required and optional nodes (Lines [19][20][21][22][23][24]. Required nodes are those nodes that have the currently added node as its direct predecessor 1 enumAlternatives(Graph precedGraph, Graph plan, 2 Graph partialPlan) { 3 4 if (isEmpty(precedGraph)) { 5 addPlanToResultSet(partialPlan); 6 return; 7 } 8 candidateNodes = getNodesWithOutDegreeZero(precedGraph); 9 10 foreach(Node n in candidateNodes) { 11 inputNodes = getNodesWithOpenInputs(partialPlan); 12 13 addNodeToPartialPlan(n); 14 removeNodeAndIncidentEdgesFromPrecedenceGraph(n); 15 16 if (isEmpty(inputNodes)) 17 enumAlternatives(precedGraph, plan, partialPlan); 18 19 foreach(Node m in inputNodes){ in the original dataflow, optional successors are all other operators contained in inputN odes.…”

Section: Plan Enumerationmentioning

confidence: 99%

SOFA: An extensible logical optimizer for UDF-heavy data flows

Rheinländer

Heise

Hueske

et al. 2015

Information Systems

View full text Add to dashboard Cite

a b s t r a c tRecent years have seen an increased interest in large-scale analytical data flows on nonrelational data. These data flows are compiled into execution graphs scheduled on large compute clusters. In many novel application areas the predominant building blocks of such data flows are user-defined predicates or functions (UDFs). However, the heavy use of UDFs is not well taken into account for data flow optimization in current systems.SOFA is a novel and extensible optimizer for UDF-heavy data flows. It builds on a concise set of properties for describing the semantics of Map/Reduce-style UDFs and a small set of rewrite rules, which use these properties to find a much larger number of semantically equivalent plan rewrites than possible with traditional techniques. A salient feature of our approach is extensibility: we arrange user-defined operators and their properties into a subsumption hierarchy, which considerably eases integration and optimization of new operators. We evaluate SOFA on a selection of UDF-heavy data flows from different domains and compare its performance to three other algorithms for data flow optimization. Our experiments reveal that SOFA finds efficient plans, outperforming the best plans found by its competitors by a factor of up to six.

show abstract

Extensible/rule based query rewrite optimization in Starburst

Cited by 159 publications

References 15 publications

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

Axiomatic foundations and algorithms for deciding semantic equivalences of SQL queries

HoTTSQL: proving query rewrites with univalent SQL semantics

SOFA: An extensible logical optimizer for UDF-heavy data flows

Contact Info

Product

Resources

About