GenJoin: Conditional Generative Plan-to-Plan Query Optimizer that Learns from Subplan Hints

2024-11-07

arXiv.org

AI Summary

Traditional query optimizers rely on brittle heuristics when constrained to limited search spaces, while state-of-the-art learned optimizers suffer from high training-data requirements, slow inference, and poor generalization across workloads. This paper introduces a generative “plan-to-plan” optimization paradigm, framing physical plan generation as a conditional sequence generation task. It enables end-to-end synthesis of high-quality execution plans directly from randomly sampled subplans as prompts—bypassing exhaustive enumeration of the full search space. Key innovations include differentiable plan embeddings, subplan-prompt-driven training, and strong cross-workload generalization under sparse supervision. Evaluated on TPC-H and JOB benchmarks, our approach consistently outperforms PostgreSQL and all prior learned optimizers: it achieves 3.2× faster inference and reduces estimated query cost by 18.7%.

Technology Category

• Search and Optimization: Learning to Search
• Planning, Routing, and Scheduling: Learning for Planning and Scheduling
• Natural Language Processing: Code Generation / Program Synthesis from Natural Language

Application Category

• Search and Retrieval-Augmented AI: Web learning to rank, online learning, and counterfactual learning for ranking
• Economics, Online Markets and Human Computation: Cost models of using LLMs in production systems
• Semantics and Knowledge: Methods to enhance, augment, integrate or synergize semantic models such as knowledge graphs and LLMs

Abstract

Query optimization has become a research area where classical algorithms are being challenged by machine learning algorithms. At the same time, recent trends in learned query optimizers have shown that it is prudent to take advantage of decades of database research and augment classical query optimizers by shrinking the plan search space through different types of hints (e.g. by specifying the join type, scan type or the order of joins) rather than completely replacing the classical query optimizer with machine learning models. It is especially relevant for cases when classical optimizers cannot fully enumerate all logical and physical plans and, as an alternative, need to rely on less robust approaches like genetic algorithms. However, even symbiotically learned query optimizers are hampered by the need for vast amounts of training data, slow plan generation during inference and unstable results across various workload conditions. In this paper, we present GenJoin - a novel learned query optimizer that considers the query optimization problem as a generative task and is capable of learning from a random set of subplan hints to produce query plans that outperform the classical optimizer. GenJoin is the first learned query optimizer that significantly and consistently outperforms PostgreSQL as well as state-of-the-art methods on two well-known real-world benchmarks across a variety of workloads using rigorous machine learning evaluations.

Problem

• Improving query optimization using machine learning and subplan hints
• Reducing training data needs and slow plan generation in learned optimizers
• Outperforming classical optimizers like PostgreSQL across diverse workloads

Innovation

• Generative task approach for query optimization
• Learning from random subplan hints
• Outperforming classical and state-of-the-art optimizers