[Paper Review] GenRewrite: Query Rewriting via Large Language Models

Jie Liu, Barzan Mozafari|arXiv (Cornell University)|Mar 14, 2024

Data Quality and Management5 citations

TL;DR

GenRewrite introduces a holistic system that uses large language models (LLMs) with natural language rewrite rules (NLR2s) and a counterexample-guided loop to rewrite SQL queries for better performance, achieving substantial coverage and speedups on TPC benchmarks.

ABSTRACT

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.

Motivation & Objective

• Motivate the need for scalable automatic query rewriting beyond pattern-based rules and manual efforts.
• Propose a holistic GenRewrite system that uses LLMs with natural language rewrite rules (NLR2s) to generate, correct, and evaluate rewrites.
• Introduce a counterexample-guided iterative correction method to fix syntactic and semantic errors in rewritten queries.
• Enable knowledge transfer across queries via an NLR2 repository and a utility scoring mechanism to prioritize hints.

Proposed method

• Define Natural Language Rewrite Rules (NLR2s) as human-readable hints produced by the LLM to guide rewrites and enable knowledge transfer.
• Maintain an NLR2 repository and use a utility score to select only relevant hints for a given query.
• Apply a three-phase loop: suggest rewrites, correct for equivalence, and evaluate for equivalence and performance.
• Use counterexample-guided refinement to iteratively fix syntactic/semantic errors in rewrites based on feedback.
• Estimate performance via actual execution or a database cost model and update NLR2 utilities accordingly.
• Operate under a user-specified or default time budget (30 seconds per query) to optimize recurrent workloads.

Experimental results

Research questions

• RQ1 Can LLMs be effectively used for query rewriting beyond traditional rule-based or synthesis-based approaches?
• RQ2 How can we transfer rewrite knowledge across queries to improve coverage over time?
• RQ3 Does a counterexample-guided iterative refinement reduce incorrect rewrites and LLM costs while preserving equivalence and speedups?
• RQ4 What is the impact of NLR2-guided hints on rewrite quality and overall performance on complex benchmarks like TPC-DS?

Key findings

• GenRewrite rewrites 22 of 99 TPC-DS queries to achieve more than 2x speedups.
• The approach provides 2.5x–3.2x higher coverage than state-of-the-art traditional rewriting and 2.1x higher than out-of-the-box LLM performance.
• NLR2s enable knowledge transfer and better hint selection, reducing unnecessary or conflicting guidance for the LLM.
• A counterexample-guided technique substantially reduces semantic and syntactic errors in rewritten queries.
• The system emphasizes human-readable explanations of rewrites to aid verification and understanding.
• GenRewrite’s framework supports reuse of rewrites across workloads by focusing on general, schema-agnostic NLR2s.