Write a SQL Optimizer using Egg (2023)

The SQL optimizer is a crucial component of relational database systems, designed to enhance query efficiency by optimizing SQL statements. To simplify the understanding of its fundamental principles, a compact SQL optimizer has been developed using the Egg framework in Rust. Despite its size, it incorporates essential optimization techniques such as expression simplification, constant folding, predicate pushdown, column pruning, join reordering, and cost estimation.

The optimizer handles both rule-based optimization (RBO) and cost-based optimization (CBO) and is functional with realistic TPC-H queries.

In this article, the implementation process of this project will be briefly overviewed. For detailed code, refer to the SQL Optimizer Labs or learn about its application in the RisingLight project. Egg, a program optimizer framework written in Rust, employs a technique known as Equality Saturation. This method gradually rewrites expressions to uncover all equivalent forms and subsequently identifies the optimal solution among them.

This process benefits from the e-graph data structure, which efficiently queries and maintains equivalence classes at runtime, minimizing computational costs.

To illustrate the optimization process, consider simplifying the expression a * 2 / 2 to a. The Egg framework organizes this optimization into three layers: e-graph, e-class, and e-node. Each node in the diagram represents a variable, a constant, or an operation. Multiple e-nodes can form an e-class, representing a group of equivalent nodes with identical semantics that can be substituted for one another.

The child nodes of each e-node are e-classes, which collectively form an e-graph with a compact representation of numerous possible combinations.

The e-graph also facilitates dynamic insertion of e-nodes and merging of e-classes, enabling expression rewriting. The following diagram demonstrates the insertion of a new expression a 1 into the graph and its subsequent merging with a * 2. Egg supports user-defined rules using S-expressions, for example, (* ?a 2) = (?a 1). For more complex rules, Rust can be used. This flexibility allows developers to quickly create an optimizer for their specific language using Egg.

The article also highlights the application of Egg in SQL, emphasizing its academic backing. The research behind Egg was awarded the Distinguished Paper Award at POPL 2021. For further exploration, resources are available on the Egg website. The process begins by defining the language, SQL statements and their expressions, using Egg's algebraic data types (ADT) in Rust.

An enum is created using the define_language macro to describe a node, which can be a value, list, operator, or plan node. The query plan is then represented in Rust, facilitating optimization. Subsequent sections will delve into optimization rules using Egg's provided API, demonstrating the optimizer's versatility and extensibility.