Bao: Making Learned Query Optimization Practical

Marcus, Ryan; Negi, Parimarjan; Mao, Hongzi; Tatbul, Nesime; Alizadeh, Mohammad; Kraska, Tim

doi:10.1145/3448016.3452838

Cited by 68 publications

(45 citation statements)

References 30 publications

Supporting

Mentioning

Contrasting

Order By: Relevance

“…This also allows Bao to be used as an advisor for a database administrator. In [76], we showed that Bao can offer both reduced costs and better performance compared with commercial systems deployed in the cloud. We later informally extended the evaluation in [74] to include Vertica, Azure Synapse, and Redshift in the evaluation, showing cost reductions of up to 25%.…”

Section: Learned Query Optimizationmentioning

confidence: 99%

See 1 more Smart Citation

Towards instance-optimized data systems

Kraska

2021

Proc. VLDB Endow.

Self Cite

View full text Add to dashboard Cite

In recent years, we have seen increased interest in applying machine learning to system problems. For example, there has been work on applying machine learning to improve query optimization, indexing, storage layouts, scheduling, log-structured merge trees, sorting, compression, and sketches, among many other data management tasks. Arguably, the ideas behind these techniques are similar: machine learning is used to model the data and/or workload in order to derive a more efficient algorithm or data structure. Ultimately, these techniques will allow us to build "instance-optimized" systems: that is, systems that self-adjust to a given workload and data distribution to provide unprecedented performance without the need for tuning by an administrator. While many of these techniques promise orders-of-magnitude better performance in lab settings, there is still general skepticism about how practical the current techniques really are. The following is intended as a progress report on ML for Systems and its readiness for real-world deployments, with a focus on our projects done as part of the Data Systems and AI Lab (DSAIL) at MIT By no means is it a comprehensive overview of all existing work, which has been steadily growing over the past several years not only in the database community but also in the systems, networking, theory, PL, and many other adjacent communities.

show abstract

Section: Learned Query Optimizationmentioning

confidence: 99%

“…Motivated by these difficulties, we more recently introduced Bao (the Bandit optimizer) [76]. Bao takes advantage of the wisdom built into existing query optimizers by providing per-query optimization hints.…”

Section: Learned Query Optimizationmentioning

confidence: 99%

Towards instance-optimized data systems

Kraska

2021

Proc. VLDB Endow.

Self Cite

View full text Add to dashboard Cite

show abstract

“…There are also randomized algorithms proposed based on Simulated Annealing and Iterative Improvement [15], Genetic Algorithms [5,13], Random Sampling [35]. Some recent work such as [19,21] use machine learning techniques for query optimization. Primary issue with these approaches are that either they do not scale well for large join queries considered in this work, or produce low quality solutions [26].…”

Section: Related Workmentioning

confidence: 99%

Efficient Massively Parallel Join Optimization for Large Queries

Mancini¹,

Karthik²,

Chandra³

et al. 2022

Preprint

View full text Add to dashboard Cite

Modern data analytical workloads often need to run queries over a large number of tables. An optimal query plan for such queries is crucial for being able to run these queries within acceptable time bounds. However, with queries involving many tables, finding the optimal join order becomes a bottleneck in query optimization. Due to the exponential nature of join order optimization, optimizers resort to heuristic solutions after a threshold number of tables. Our objective is two fold: (a) reduce the optimization time for generating optimal plans; and (b) improve the quality of the heuristic solution.In this paper, we propose a new massively parallel algorithm, MPDP, that can efficiently prune the large search space (via a novel plan enumeration technique) while leveraging the massive parallelism offered by modern hardware (Eg: GPUs). When evaluated on real-world benchmark queries with PostgreSQL, MPDP is at least an order of magnitude faster compared to state-of-the-art techniques for large analytical queries. As a result, we are able to increase the heuristic-fall-back limit from 12 relations to 25 relations with same time budget in PostgreSQL. Also, in order to handle queries with even larger number of tables, we augment MPDP to a well known heuristic, IDP 2 (iterative DP version 2) and a novel heuristic UnionDP. By systematically exploring a much larger search space, these heuristics provides query plans that are up to 7 times cheaper as compared to the state-of-the-art techniques while being faster to compute.

show abstract

“…Learned DBMS components and Design Advisors Machine learning has been applied more broadly to optimize DBMS systems by replacing traditional approaches for tasks such as query optimization [13,[19][20][21] or query scheduling [18,26]. In addition, it was applied to knob tuning [34], materialized view selection [7,17], index selection [14] or partitioning [9].…”

Section: Related Workmentioning

confidence: 99%

Zero-Shot Cost Models for Out-of-the-box Learned Cost Prediction

Hilprecht¹,

Binnig²

2022

Preprint

View full text Add to dashboard Cite

In this paper, we introduce zero-shot cost models which enable learned cost estimation that generalizes to unseen databases. In contrast to state-of-the-art workload-driven approaches which require to execute a large set of training queries on every new database, zero-shot cost models thus allow to instantiate a learned cost model out-of-the-box without expensive training data collection. To enable such zero-shot cost models, we suggest a new learning paradigm based on pre-trained cost models. As core contributions to support the transfer of such a pre-trained cost model to unseen databases, we introduce a new model architecture and representation technique for encoding query workloads as input to those models. As we will show in our evaluation, zero-shot cost estimation can provide more accurate cost estimates than state-of-the-art models for a wide range of (real-world) databases without requiring any query executions on unseen databases. Furthermore, we show that zero-shot cost models can be used in a few-shot mode that further improves their quality by retraining them just with a small number of additional training queries on the unseen database.

show abstract

Bao: Making Learned Query Optimization Practical

Cited by 68 publications

References 30 publications

Towards instance-optimized data systems

Towards instance-optimized data systems

Efficient Massively Parallel Join Optimization for Large Queries

Zero-Shot Cost Models for Out-of-the-box Learned Cost Prediction

Contact Info

Product

Resources

About