Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Towards Query Optimizer as a Service (QOaaS) in a Unified LakeHouse Ecosystem: Can One QO Rule Them All?

Rana Alotaibi, Yuanyuan Tian, Stefan Grafberger, Jesús Camacho-Rodríguez, Nicolas Bruno, Brian Kroth, Sergiy Matusevych, Ashvin Agrawal, Mahesh Behera, Ashit Gosalia, Cesar Galindo-Legaria, Milind Joshi, Milan Potocnik, Beysim Sezgin, Xiaoyu Li, Carlo Curino

TL;DR

This paper weighs the pros and cons of a drastic change in direction: moving from bespoke QOs or library-sharing to rewriting the QO stack and fully embracing Query Optimizer as a Service (QOaaS) and reports on some early successes and stumbles.

Abstract

Customer demand, regulatory pressure, and engineering efficiency are the driving forces behind the industry-wide trend of moving from siloed engines and services that are optimized in isolation to highly integrated solutions. This is confirmed by the wide adoption of open formats, shared component libraries, and the meteoric success of integrated data lake experiences such as Microsoft Fabric. In this paper, we study the implications of this trend to Query Optimizer (QO) and discuss our experience of building Calcite and extending Cascades into QO components of Microsoft SQL Server, Fabric Data Warehouse (DW), and SCOPE. We weigh the pros and cons of a drastic change in direction: moving from bespoke QOs or library-sharing (à la Calcite) to rewriting the QO stack and fully embracing Query Optimizer as a Service (QOaaS). We report on some early successes and stumbles as we explore these ideas with prototypes compatible with Fabric DW and Spark. The benefits include centralized workload-level optimizations, multi-engine federation, and accelerated feature creation, but the challenges are equally daunting. We plan to engage CIDR audience in a debate on this exciting topic.

Towards Query Optimizer as a Service (QOaaS) in a Unified LakeHouse Ecosystem: Can One QO Rule Them All?

TL;DR

This paper weighs the pros and cons of a drastic change in direction: moving from bespoke QOs or library-sharing to rewriting the QO stack and fully embracing Query Optimizer as a Service (QOaaS) and reports on some early successes and stumbles.

Abstract

Customer demand, regulatory pressure, and engineering efficiency are the driving forces behind the industry-wide trend of moving from siloed engines and services that are optimized in isolation to highly integrated solutions. This is confirmed by the wide adoption of open formats, shared component libraries, and the meteoric success of integrated data lake experiences such as Microsoft Fabric. In this paper, we study the implications of this trend to Query Optimizer (QO) and discuss our experience of building Calcite and extending Cascades into QO components of Microsoft SQL Server, Fabric Data Warehouse (DW), and SCOPE. We weigh the pros and cons of a drastic change in direction: moving from bespoke QOs or library-sharing (à la Calcite) to rewriting the QO stack and fully embracing Query Optimizer as a Service (QOaaS). We report on some early successes and stumbles as we explore these ideas with prototypes compatible with Fabric DW and Spark. The benefits include centralized workload-level optimizations, multi-engine federation, and accelerated feature creation, but the challenges are equally daunting. We plan to engage CIDR audience in a debate on this exciting topic.

Paper Structure

This paper contains 14 sections, 6 figures, 1 table.

Table of Contents

  1. Introduction
  2. Reinventing wheels in the QO space
  3. Steps towards QOaaS
  4. Standardizing Plan Specification
  5. Optimizing Spark Queries with UQO
  6. Recalibrating and Tuning the Cost Model
  7. Key Lessons Learned
  8. The QOaaS Proposal
  9. Overview
  10. Core Components
  11. New Components
  12. Examples of Extensions to QOaaS
  13. Challenges and Risks
  14. Open Discussion and Debate

Figures (6)

  • Figure 1: A unified LakeHouse ecosystem with QOaaS
  • Figure 2: Optimizing Spark queries with UQO
  • Figure 3: Execution time of representative MSSales queries on Spark runtime with different QOs
  • Figure 4: Runtime performance with default vs tuned parameters on TPC-H for three scale factors
  • Figure 5: Runtime performance with default vs tuned parameters on a subset of MSSales data
  • ...and 1 more figures