Logo
ScienceStack
Table of Contents
Fetching ...
Sign In

Low Rank Learning for Offline Query Optimization

Zixuan Yi, Yao Tian, Zachary G. Ives, Ryan Marcus

TL;DR

This work tackles offline query optimization by modeling a repetitive workload as a partially observed, low-rank matrix of plan latencies and steering the optimizer through hints using low-overhead linear methods. It introduces LimeQO, an offline exploration framework built on ALS-based matrix completion and an optional transductive TCNN neural variant, both designed to minimize offline cost while avoiding regressions. Key contributions include formalizing offline exploration as an active learning problem, handling censored observations from timeouts, and demonstrating substantial workload-latency reductions across multiple benchmarks with minimal offline overhead. The approach is demonstrated to be effective, robust to data shifts, and DBMS-agnostic, offering a practical alternative to heavyweight neural-based learned optimizers with strong real-world applicability.

Abstract

Recent deployments of learned query optimizers use expensive neural networks and ad-hoc search policies. To address these issues, we introduce \textsc{LimeQO}, a framework for offline query optimization leveraging low-rank learning to efficiently explore alternative query plans with minimal resource usage. By modeling the workload as a partially observed, low-rank matrix, we predict unobserved query plan latencies using purely linear methods, significantly reducing computational overhead compared to neural networks. We formalize offline exploration as an active learning problem, and present simple heuristics that reduces a 3-hour workload to 1.5 hours after just 1.5 hours of exploration. Additionally, we propose a transductive Tree Convolutional Neural Network (TCNN) that, despite higher computational costs, achieves the same workload reduction with only 0.5 hours of exploration. Unlike previous approaches that place expensive neural networks directly in the query processing ``hot'' path, our approach offers a low-overhead solution and a no-regressions guarantee, all without making assumptions about the underlying DBMS. The code is available in \href{https://github.com/zixy17/LimeQO}{https://github.com/zixy17/LimeQO}.

Low Rank Learning for Offline Query Optimization

TL;DR

This work tackles offline query optimization by modeling a repetitive workload as a partially observed, low-rank matrix of plan latencies and steering the optimizer through hints using low-overhead linear methods. It introduces LimeQO, an offline exploration framework built on ALS-based matrix completion and an optional transductive TCNN neural variant, both designed to minimize offline cost while avoiding regressions. Key contributions include formalizing offline exploration as an active learning problem, handling censored observations from timeouts, and demonstrating substantial workload-latency reductions across multiple benchmarks with minimal offline overhead. The approach is demonstrated to be effective, robust to data shifts, and DBMS-agnostic, offering a practical alternative to heavyweight neural-based learned optimizers with strong real-world applicability.

Abstract

Recent deployments of learned query optimizers use expensive neural networks and ad-hoc search policies. To address these issues, we introduce \textsc{LimeQO}, a framework for offline query optimization leveraging low-rank learning to efficiently explore alternative query plans with minimal resource usage. By modeling the workload as a partially observed, low-rank matrix, we predict unobserved query plan latencies using purely linear methods, significantly reducing computational overhead compared to neural networks. We formalize offline exploration as an active learning problem, and present simple heuristics that reduces a 3-hour workload to 1.5 hours after just 1.5 hours of exploration. Additionally, we propose a transductive Tree Convolutional Neural Network (TCNN) that, despite higher computational costs, achieves the same workload reduction with only 0.5 hours of exploration. Unlike previous approaches that place expensive neural networks directly in the query processing ``hot'' path, our approach offers a low-overhead solution and a no-regressions guarantee, all without making assumptions about the underlying DBMS. The code is available in \href{https://github.com/zixy17/LimeQO}{https://github.com/zixy17/LimeQO}.

Paper Structure

This paper contains 22 sections, 9 equations, 17 figures, 1 table, 2 algorithms.

Table of Contents

  1. Introduction
  2. Related Work
  3. System model
  4. Offline Exploration
  5. Problem Definition
  6. Active Learning on a Low-Rank Matrix
  7. Predictive Model
  8. Linear Method
  9. Neural Method
  10. Experiments
  11. Performance Improvements
  12. Overhead
  13. Workload Shift
  14. Data Shift
  15. Ablation Study
  16. ...and 7 more sections

Figures (17)

  • Figure 1: LimeQO system model
  • Figure 2: Linear Method
  • Figure 3: Neural Method
  • Figure 4: Performance Improvements measured on four different workloads: CEB, JOB, Stack, and DSB. Goal is to achieve lower total latency with less offline exploration time. Offline exploration times chosen on the X-axis correspond to [1/4, 1/2, 1, 2, 4] $\times$ default workload time in each workload.
  • Figure 5: Total Latency vs. Offline Exploration Time on CEB workload. LimeQO and LimeQO+ outperform other techniques.
  • ...and 12 more figures