QueryBrew: System-Agnostic SQL-to-SQL Query Optimization [pdf]

QueryBrew: System-Agnostic SQL-to-SQL Query Optimization Tobias Schmidt∗ Technische Universität München [email protected] Maximilian Reif∗ Technische Universität München [email protected] Altan Birler∗ Technische Universität München [email protected] Thomas Neumann Technische Universität München [email protected] ABSTRACT Efficient SQL execution heavily depends on the query optimizer’s ability to find efficient query plans. Existing query optimizers differ widely in their capabilities, leading to significant differences in exe- cution complexity across many queries. In this demonstration, we present QueryBrew, a system-agnostic query optimization service that decouples the optimizer from the database engine through SQL-to-SQL rewriting. QueryBrew takes arbitrary SQL queries, re- fines them using the state-of-the-art Umbra optimizer, and distills the optimized execution plan back into an operator-oriented SQL representation. This allows any target SQL database, such as Post- greSQL, DuckDB, ClickHouse, or SQL Server, to inherit advanced optimization techniques, such as general unnesting, simplification, and adaptive join ordering, without modification. The QueryBrew UI lets users explore and analyze the changes made by Umbra’s query optimizer and how they affect execution in the target system. SQL-to-SQL optimization can improve runtimes by more than one order of magnitude for correlated and many-join queries. PVLDB Reference Format: Tobias Schmidt, Maximilian Reif, Altan Birler, and Thomas Neumann. QueryBrew: System-Agnostic SQL-to-SQL Query Optimization. PVLDB, 19(12): 4494 - 4497, 2026. doi:10.14778/3827998.3828048 PVLDB Artifact Availability: The source code, data, and/or other artifacts have been made available at https://querybrew.db.cit.tum.de. 1 INTRODUCTION Databases have gotten significantly faster over the last decades. Vectorized execution in DuckDB and SQL Server and compiled execution in PostgreSQL significantly speed up analytical queries. High-bandwidth storage devices and massively parallel machines al- low processing multi-terabyte datasets on a single node. Distributed engines such as Snowflake, Databricks, BigQuery, and Redshift are widely deployed in the industry and support petabyte-scale ana- lytics. However, one particular component has lagged behind this pace of innovation: the query optimizer. ∗Authors contributed equally to this research. This work is licensed under the Creative Commons BY-NC-ND 4.0 International License. Visit https://creativecommons.org/licenses/by-nc-nd/4.0/ to view a copy of this license. For any use beyond those covered by this license, obtain permission by emailing [email protected]. Copyright is held by the owner/author(s). Publication rights licensed to the VLDB Endowment. Proceedings of the VLDB Endowment, Vol. 19, No. 12 ISSN 2150-8097. doi:10.14778/3827998.3828048ery Optimizer Any SQL Database System Target System: PostgreSQL | ClickHouse | DuckDB | SQL Server | … eryBrew Optimizer as a Service (QOaaS) Simplification General Unnesting (Outer) Join Ordering SQL Dialect Translation System Specific Optimization Physical Operator Selection Index Selection ery Execution SQLSQL Schema and Table Statistics ery Result Figure 1: QueryBrew’s SQL-to-SQL optimization approach. Queries are optimized by a state-of-the-art optimizer (e.g., Umbra) and transformed back to SQL. The optimized query benefits from simplification rules, general unnesting, and ad- vanced join ordering [5], and runs in existing SQL databases. The query optimizer is the component with the greatest potential to make or break queries. Its decisions not only affect runtime constants but can change a query’s runtime complexity entirely. It is a high-risk and high-reward component. Changes to the optimizer can yield immense benefits, but they are also likely to break some customers’ workloads in unexpected ways (e.g., by removing one of two mistakes that cancelled each other out). Thus, big players in the industry approach developments in the optimizer with a risk-averse, calculated approach. Unfortunately, this means that optimizers often lag behind the latest innovations [14]. Decoupling the optimizer from the database engine would accel- erate innovation and enable independent development. However, optimizers are deeply intertwined with the database system, its sta- tistics, and its operators; hence, extracting them is non-trivial. Even the many database solutions at Google, such as BigQuery, Span- ner, F1, BigTable, Dremel, and Procella, share the SQL frontend GoogleSQL but do not share a single optimizer [2]. There have been attempts to design a common intermediate representation for query plans such as Substrait [1]. CompoDB [7] exploits Substrait to build a modular data system with an exchange- able query optimizer and execution engine. They rely on DuckDB, DataFusion, Calcite, and Ibis for optimization. However, only a few engines (DuckDB, DataFusion, and Acero) are supported, as Substrait has not seen wide adoption due to the inherent difficulty of unifying the semantics of existing systems with their various id- iosyncrasies. Microsoft takes this idea one step further and proposes Query Optimizer as a Service (QOaaS). They make Fabric’s Unified Query Optimizer [6] available to other engines, such as Spark, via Substrait-to-Substrait optimization. The logical plan is given to the optimizer, which produces an optimized, physical plan [15]. 4494 select category, count(*) from item i where current_price > (select avg(current_price) from item where category = i.category or color = i.color) group by category order by count; Item Γ Item ⨝ Γ Γ with sort_8 as (…), group_by_7 as (…), join_inner_6 as (…), group_by_5 as (…), join_cross_4 as (…), scan_3 as (…), magic_2 as (…), scan_1 as (…), select * from sort_8 Input: Human-Wrien ery Output: Operator-Oriented eryOptimization Decorrelated ery Plan +Figure 2: QueryBrew in action: Correlated query on the TPC-DS item table. Umbra’s query optimizer decorrelates the query using general unnesting. We transform the optimized query plan into an operator-oriented representation using one CTE per operator that can canonically be executed by other database systems. We approach the decoupling of query optimizers in a practical way as shown in Figure 1. Our optimization service, QueryBrew, takes SQL as input, optimizes the query internally, and brews SQL as output. We found that rewriting queries in SQL can greatly improve performance across a wide range of queries. Because most relational databases use SQL as their standard interface, QueryBrew integrates easily with existing systems. In addition, the target system’s query optimizer can further apply system-specific optimizations and select the best physical operators, taking the system’s implementation details into account. We are not the first to explore SQL-to-SQL optimization. Both LLM-based [8] and human-centered [3] rewriting approaches have been proposed; however, these surface-level techniques operate on the query text or the abstract syntax tree. Since many sophisticated optimizations operate on relational algebra, the capabilities of such text-based techniques are inherently limited. OpenIVM [4] also operates on relational algebra, but it generates SQL for incremental view maintenance rather than for query optimization. QueryBrew demonstrates that complex, correlated queries can be optimized directly within SQL. By leveraging schema and sta- tistics, it simplifies, unnests, and utilizes cost-based reordering via adaptive optimizations and enumeration of join plans to find the best execution plan. Through a structured back-translation of the optimized query plan to SQL, target systems can benefit from these optimizations without reimplementing them. Additionally, an intu- itive interface lets users easily analyze SQL-to-SQL optimizations and benchmark queries across different systems. 2 SOLUTION OVERVIEW Approach. QueryBrew builds a refined SQL statement by passing an input query through Umbra’s [11] state-of-the-art optimizer and distilling the resulting optimized plan back into SQL. Umbra implements a wide range of (cost-based) optimization techniques, including operator simplification, general unnesting [10, 12], adap- tive join reordering [13], and common subtree elimination. These techniques exploit detailed statistics, data samples, type informa- tion, and functional dependencies available to the database to esti- mate cardinalities and simplify or eliminate operators. The resulting query plan is not a typical query tree but a directed acyclic graph (DAG) of relational operators, enabling more efficient execution. QueryBrew translates the optimized query plan back to SQL, retaining the optimizations made by Umbra. It preserves the ex- ecution order of the operators from the optimized plan, allowing other systems to benefit from Umbra’s join optimizer. We achieve this using Common Table Expressions (CTEs) in the exported SQL. Every operator in the optimized plan is represented as an individual CTE, and operators are connected by referencing the CTEs of their inputs. Consider the following query: select i_category, count(distinct i_color) from item group by i_category; It counts the number of distinct colors for each category in the item table from the TPC-DS benchmark. Umbra’s optimized query plan translated to SQL looks as follows: WITH scan_1 AS ( SELECT i_category AS v5, i_color AS v6 FROM item ), groupby_2 AS ( SELECT v5 AS v3, v6 AS v4 FROM scan_1 s GROUP BY v5, v6 ), groupby_3 AS ( SELECT v3 AS v1, count(v4) AS v2 FROM groupby_2 s GROUP BY v3) SELECT v1 AS i_category, v2 AS count FROM groupby_3; Every operator from the plan is given a unique name (e.g., scan_1, groupby_2, groupby_3). While the scan reads the two required columns from the item table, the two group by operators compute the distinct count. Here we can already observe one of the opti- mizations performed by Umbra: It splits the