Cost Models for Big Data Query Processing: Learning, Retrofitting, and Our Findings
Learning ensemble-based cost models with resource-aware optimization improves accuracy by 2-3 orders of magnitude, achieving high correlation and plan improvements.
Key Findings
Methodology
This paper introduces a workload-pattern-based multi-model learning framework integrated into an extended Cascades optimizer. It trains specialized models (elastic net, random forest, neural networks) for common subexpressions, then combines predictions via model fusion to enhance robustness. The system employs periodic retraining and resource-aware plan exploration, leveraging feedback loops for continuous improvement. The core innovation is resource-sensitive cost prediction, enabling dynamic resource allocation and plan optimization in cloud environments. Experiments on production workloads and TPC-H benchmarks demonstrate significant accuracy gains, with correlation increasing from 0.09 to 0.75 and median error dropping to 14%. The approach addresses the complexity and variability of cloud workloads, providing a scalable, adaptive solution for industrial query optimization.
Key Results
- The learned cost models outperform traditional heuristics by 2-3 orders of magnitude in accuracy, with correlation metrics rising from 0.09 to 0.75, and median errors reduced to 14%.
- 70% of query plan modifications driven by these models lead to substantial latency reductions (~20%) and resource savings (~30%), validated across production and benchmark datasets.
- The system maintains stability over long periods through model fusion, covering over 70% workload diversity, and adapts dynamically to workload fluctuations and system changes.
Significance
This work significantly advances the state-of-the-art in big data query optimization by integrating machine learning models into industrial systems. It effectively tackles the longstanding challenge of accurate cost estimation in complex, dynamic cloud environments, enabling more efficient resource utilization and faster query response times. The methodology bridges the gap between research and practice, offering a scalable, maintainable framework that can be adopted across diverse cloud data platforms. Its impact extends to reducing operational costs and improving service reliability, thus fostering more intelligent, autonomous data management systems.
Technical Contribution
The paper's key technical contributions include the development of specialized subexpression cost models, a robust model fusion strategy, and an extension of the Cascades optimizer to incorporate resource-aware planning. It introduces a systematic feature selection process using elastic net, and employs multiple machine learning algorithms to train accurate, interpretable models. The integration of periodic retraining and feedback mechanisms ensures long-term stability. These innovations collectively enable high-accuracy, resource-efficient query planning in large-scale cloud systems, representing a significant leap from traditional heuristic-based methods.
Novelty
This is the first industrial-scale implementation integrating multiple specialized machine learning models with resource-aware plan exploration within a Cascades-based optimizer. Unlike prior work that relied on single global models or static heuristics, this approach dynamically learns and combines models tailored to recurring subexpressions, addressing the accuracy-coverage trade-off. Its resource-sensitive optimization mechanism is novel, enabling adaptive resource allocation based on learned cost predictions, a significant departure from existing static or heuristic methods.
Limitations
- The system relies heavily on historical workload data, which may limit its effectiveness in rapidly changing or novel environments. Continuous data collection and model retraining are necessary.
- In extreme cases of workload spikes or anomalies, the models may produce inaccurate predictions, affecting scheduling decisions.
- The added complexity of model training, fusion, and resource exploration increases system overhead, potentially impacting real-time query responsiveness.
Future Work
Future directions include developing online learning techniques for real-time adaptation, extending models to multi-tenant and heterogeneous hardware environments, and improving interpretability for debugging. Exploring reinforcement learning for end-to-end resource and plan optimization, and integrating anomaly detection to handle workload spikes, are promising avenues. Additionally, scaling the framework to support broader cloud platforms and diverse data workloads will be crucial for industrial adoption.
AI Executive Summary
In the era of cloud-based big data analytics, accurate query cost estimation remains a fundamental challenge. Traditional models, relying on heuristics and fixed assumptions, often fall short in dynamic environments, leading to inefficient resource utilization and increased latency. Recognizing this, the authors propose a novel framework that leverages machine learning to learn specialized cost models from workload patterns. These models, trained on recurring subexpressions, are fused into a robust ensemble that maintains high accuracy over long periods. The system, integrated into an extended Cascades optimizer, incorporates resource-awareness, enabling dynamic adjustment of container counts and resource allocation during query planning.
The core innovation lies in combining workload-driven model specialization with resource-sensitive optimization, addressing the accuracy-coverage trade-off inherent in cost modeling. Extensive experiments in Microsoft’s production environment and on TPC-H benchmarks demonstrate the system’s effectiveness, with cost prediction correlation improving from 0.09 to 0.75 and median errors dropping to 14%. Moreover, 70% of query plan modifications resulted in substantial improvements in latency and resource efficiency.
This work significantly advances industrial query optimization, bridging the gap between research and real-world deployment. It offers a scalable, adaptive approach that can handle the complexity and variability of cloud workloads, reducing operational costs and improving performance. Future work will focus on online learning, multi-tenant support, and further automation, paving the way for fully autonomous big data systems that optimize themselves in real-time.
Deep Dive
Applications
What is the real-world impact?
Limitations & Outlook
What gaps remain?
Abstract
Query processing over big data is ubiquitous in modern clouds, where the system takes care of picking both the physical query execution plans and the resources needed to run those plans, using a cost-based query optimizer. A good cost model, therefore, is akin to better resource efficiency and lower operational costs. Unfortunately, the production workloads at Microsoft show that costs are very complex to model for big data systems. In this work, we investigate two key questions: (i) can we learn accurate cost models for big data systems, and (ii) can we integrate the learned models within the query optimizer. To answer these, we make three core contributions. First, we exploit workload patterns to learn a large number of individual cost models and combine them to achieve high accuracy and coverage over a long period. Second, we propose extensions to Cascades framework to pick optimal resources, i.e, number of containers, during query planning. And third, we integrate the learned cost models within the Cascade-style query optimizer of SCOPE at Microsoft. We evaluate the resulting system, Cleo, in a production environment using both production and TPC-H workloads. Our results show that the learned cost models are 2 to 3 orders of magnitude more accurate, and 20X more correlated with the actual runtimes, with a large majority (70%) of the plan changes leading to substantial improvements in latency as well as resource usage.