TLDR: This research paper introduces an innovative approach to train Learned Cost Models (LCMs) for database systems. Traditionally, LCMs require vast and diverse SQL query datasets, which are hard to acquire. The authors propose using modern synthetic data generation (SDG) techniques, inspired by generative AI and Large Language Models (LLMs), to create high-quality, realistic SQL queries. Their SDG pipeline generates diverse queries tailored to specific database schemas. Experiments show that training LCMs with these synthetic queries significantly improves predictive accuracy and query routing efficiency, even with 45% fewer training samples compared to traditional mechanical generation methods.
In the world of database management, optimizing how queries run is crucial for both performance and cost efficiency. Imagine a system that can predict the best way to execute a SQL query, saving time and resources. This is the promise of Learned Cost Models (LCMs), but their effectiveness hinges on one major challenge: getting enough diverse and realistic SQL queries to train them.
Traditional methods for obtaining these training datasets often fall short. Real-world workloads are hard to collect and might not be diverse enough, while standard benchmarks like TPC-H, TPC-DS, or IMDB, though useful, don’t always reflect the complexity of actual queries. Relying on historical queries assumes frequent repetition, which isn’t always the case, and “bootstrapping” a system by recording queries over a long period can lead to suboptimal performance and user dissatisfaction.
A recent research paper, “Bootstrapping Learned Cost Models with Synthetic SQL Queries,” by Michael Nidd and his colleagues at IBM Research Europe, introduces an innovative solution to this problem. Drawing inspiration from the advancements in generative AI and Large Language Models (LLMs), the authors propose using modern synthetic data generation (SDG) techniques to create high-quality, diverse SQL query datasets for training LCMs.
The core idea is to leverage LLMs’ ability to understand and generate code to produce SQL queries that are not only syntactically correct but also semantically meaningful and varied. This approach eliminates the need for manual annotation and allows for the creation of datasets tailored to a specific database schema.
The research outlines a comprehensive SDG pipeline designed to achieve this. It combines LLM-based generation with template-based and SQL-component-based generators to ensure a wide range of query types. A crucial part of this pipeline involves “validators” that check the generated queries for correctness, diversity, and consistency. This self-instructing setup allows the system to iteratively generate and refine data, breaking away from the limitations of initial seed examples.
The pipeline starts with preprocessing, extracting schema information from the database. It then creates targeted “subschemas” – smaller, connected subsets of tables – which help the LLM generate more precise and semantically relevant queries. The “DataBuilder” component then crafts “few-shot” prompts for the LLM, providing schema details and example queries to guide the generation process. These examples can even be biased to encourage specific SQL constructs, like GROUP BY or ORDER BY clauses, to fill coverage gaps.
After generation, the queries are filtered for syntax and relevance. A “Coverage” step, utilizing tools like Apache Calcite, analyzes the generated SQL statements to identify how often tables, columns, and operations are referenced. This ensures broad coverage and internal consistency, allowing the pipeline to be reconfigured to generate more queries if gaps are found.
The results of this methodology are promising. Using the TPC-H schema and the granite-3.3-8b-instruct model, the researchers evaluated the diversity and complexity of the generated queries. They found that using “3-shot” examples (providing three examples in the prompt) led to a strong increase in the number of functions used in queries, demonstrating richer functional expressions. Furthermore, prompts with specific biases, such as for GROUP BY or ORDER BY, successfully influenced the query structure.
Crucially, the study demonstrated that training a GNN-based LCM with these synthetically generated queries significantly improved its accuracy. When trained with just 2200 SDG-generated queries – 45% fewer than the 4000 mechanically generated queries used in the baseline – the LCM showed a reduction in prediction error. This improvement translated into better actual query routing, reducing the total execution time for a set of 1000 test queries from 165 minutes to 150 minutes, a 10% improvement.
Also Read:
- Unlocking Insights from Large Tables with Natural Language Query Plans
- Backprompting: Enhancing AI Guardrails for Health Advice with Synthetic Data
In conclusion, this research highlights a powerful new direction for optimizing database systems. By harnessing the capabilities of LLMs for synthetic data generation, it becomes possible to create high-quality, diverse SQL query datasets that effectively train Learned Cost Models. This not only improves the accuracy of query execution time predictions but also leads to more efficient query routing, even with fewer training samples. The full research paper can be found here.
