Query rewriting is an effective technique for refining poorly written queries before they reach the query optimizer. However, manual rewriting is not scalable, as it is prone to errors and requires deep expertise. Traditional query rewriting algorithms fall short too: rule-based approaches fail to generalize to new query patterns, while synthesis-based methods struggle with complex queries. Fortunately, Large Language Models (LLMs) already possess broad knowledge and advanced reasoning capabilities, making them a promising solution for tackling these longstanding challenges. In this paper, we present GenRewrite, the first holistic system that leverages LLMs for query rewriting beyond traditional rules. We introduce the notion of Natural Language Rewrite Rules (NLR2s), which serve as hints for the LLM while also a means of knowledge transfer from rewriting one query to another, allowing GenRewrite to become smarter and more effective over time. We present a novel counterexample-guided technique that iteratively corrects the syntactic and semantic errors in the rewritten query, significantly reducing the LLM costs and the manual effort required for verification. Across the standard TPC-DS and JOB benchmarks and their SQLStorm-generated variants, GenRewrite consistently optimizes more queries at every speedup threshold than all baselines. At the >=2x threshold on TPC-DS, GenRewrite improves 25 queries-1.35x more than LLM-driven baselines and 2.6x more than LLM-enhanced rule-based baselines-and the gap widens further on TPC-DS (SQLStorm); on JOB and its SQLStorm variant, where queries are simpler, absolute gains are smaller but GenRewrite still leads by a notable margin.

翻译：查询重写是一种在查询到达优化器之前对编写不当的查询进行优化的有效技术。然而，手动重写难以扩展，因为它容易出错且需要深厚的专业知识。传统的查询重写算法也存在不足：基于规则的方法无法泛化到新的查询模式，而基于合成的方法则难以处理复杂查询。幸运的是，大型语言模型（LLMs）已具备广泛的知识和先进的推理能力，使其成为应对这些长期挑战的有前景的解决方案。本文提出GenRewrite，这是首个利用LLMs进行超越传统规则的查询重写的整体系统。我们引入了自然语言重写规则（NLR2s）的概念，这些规则既作为LLM的提示，又作为从一个查询到另一个查询的知识转移手段，使GenRewrite能够随时间变得更智能、更有效。我们提出了一种新颖的反例引导技术，迭代地纠正重写查询中的语法和语义错误，显著降低了LLM的成本和验证所需的手动工作量。在标准的TPC-DS和JOB基准测试及其SQLStorm生成的变体上，GenRewrite在每个加速阈值下持续优化的查询数量均超过所有基线。在TPC-DS的>=2x阈值上，GenRewrite优化了25个查询——比LLM驱动的基线多1.35倍，比LLM增强的基于规则的基线多2.6倍——而在TPC-DS（SQLStorm）上，差距进一步扩大；在JOB及其SQLStorm变体上，由于查询更简单，绝对增益较小，但GenRewrite仍以显著优势领先。