sql

To address the low execution efficiency of SQL correlated subqueries, this paper proposes a predicate separation optimization technique: predicates in the subquery that are independent of the outer query are precomputed and applied for early filtering, thereby significantly reducing the number of correlated subquery invocations. Building upon this rewrite, we design a novel execution architecture on a block-based Volcano model, integrating position-aware columnar storage with late materialization. Our core contribution is the first deep integration of predicate correlation analysis with storage- and execution-layer optimizations. Experimental evaluation on PosDB and PostgreSQL demonstrates up to 5× query speedup. Additionally, we introduce a lightweight cost model to accurately estimate the optimization benefits.

Traditional query optimizers—based on System R, Volcano, or Cascades—employ a monolithic, static, single-query architecture that exhibits performance instability, lacks global workload-level optimization, and suffers from architectural rigidity in cloud-native environments with massive-scale data and unified data platforms. To address these industrial challenges, this paper proposes three evolutionary directions: (1) extending optimization from individual queries to workload-aware collaborative optimization; (2) establishing an execution-feedback-driven closed-loop optimization framework; and (3) designing a composable, modular optimizer architecture enabling cross-engine reuse and agile iteration. The study distills three key trends—workload awareness, feedback closure, and architectural decoupling—providing a practical, implementable technical pathway and industrial paradigm for building next-generation query optimization systems that are dynamic, holistic, and self-adaptive.

SQL query rewriting faces a fundamental trade-off between performance optimization and interpretability, while remaining prone to semantic or syntactic errors. Method: This paper proposes an LLM-driven, database-aware rewriting framework featuring a novel token-probability-guided rewrite path selection mechanism; it integrates metadata-aware prompting, selectivity-aware rewriting rules, redundancy elimination, and dual verification—logical equivalence checking and statistical consistency validation. Contribution/Results: The framework bridges the gap between purely rule-based and purely LLM-based approaches, enabling robust end-to-end rewriting. Experiments on TPC-DS show that two-thirds of queries achieve >1.5× speedup; rewrite coverage reaches four times that of the state-of-the-art; and the geometric mean speedup improves by an order of magnitude. The framework has been integrated into the LITHE system and validated across mainstream database platforms.

This work addresses the challenges of implementing analytical logic in production-grade systems using imperative code, which often leads to code duplication, definition drift, and poor testability. To overcome these issues, the authors propose modeling analytical workflows as typed, declarative directed acyclic graphs (QDAGs), enabling, for the first time, a unified representation of complex analytical pipelines in large-scale production environments. The system efficiently orchestrates multi-step operations through on-demand execution, memoization, pruning, and parallel scheduling, supporting diverse primitives such as Apache Pinot queries, service invocations, SQLite joins, jq transformations, conditional logic, differentially private aggregations, and nested QDAG calls. Deployed across over 100 production use cases at LinkedIn on more than 500 hosts, the system achieves median orchestration overhead of approximately 10 milliseconds and 99th-percentile latency under 50 milliseconds, significantly enhancing reusability, consistency, and testability while meeting interactive latency requirements.

Traditional database query optimization relies solely on explicit schema constraints (e.g., primary and foreign keys), overlooking abundant implicit data dependencies—such as ordering or functional dependencies—that remain undetected and unexploited. Method: This paper introduces a workload-driven, lightweight dependency discovery technique that enables millisecond-scale automatic identification of such dependencies. It tightly integrates dependency-aware optimization into both the query optimizer and execution engine, supporting SQL rewriting, dependency propagation, and subquery optimization. Contribution/Results: The approach transcends conventional schema-only optimization by establishing an end-to-end dependency-aware framework. Evaluated across five mainstream DBMSs using standard benchmarks (e.g., TPC-C), it achieves up to 10% higher throughput versus primary/foreign-key–only optimization, 22% over no-dependency optimization, and overall improvements of 5%–33%. Discovery overhead is fully amortized after a single workload execution.

This work addresses the limitations of existing SQL rewriting approaches, which either rely on rigid rule-based systems lacking adaptability or depend on large language models (LLMs) that incur high computational costs and privacy concerns. To overcome the scarcity of high-quality domain-specific data for training small language models, the authors propose LASER, a novel framework that integrates Monte Carlo Tree Search (MCTS) with LLM-guided mutation to generate SQL-MCTS—a large-scale corpus of complex, slow queries. They further introduce SQL-GRPO, a group-relative policy optimization algorithm featuring anchored group advantages and a complexity-adaptive dynamic rollout mechanism. Experiments on the Qwen3 small model demonstrate that LASER achieves an exceptional trade-off among execution efficiency, zero-shot transfer capability, and inference overhead, significantly outperforming both rule-based systems and LLM-based alternatives.

This work addresses the scarcity of real-world SQL workloads and limitations of existing synthetic query generation methods—such as rigid templates, schema hallucination, and simplistic join structures—that hinder effective training of query optimizers. The authors propose SynQL, a rule-based, deterministic SQL synthesis framework that constructs abstract syntax trees by traversing foreign key graphs in database schemas, eschewing probabilistic generation to guarantee syntactic and schema-level validity. SynQL introduces a configuration vector to explicitly control join topology, analytical complexity, and predicate selectivity, while modeling core query components including multi-table joins, projections, aggregations, and range predicates. Workloads generated on TPC-H and IMDb achieve a topological entropy of 1.53 bits; tree-based models trained on this data attain R² ≥ 0.79 on test sets with sub-millisecond inference latency.

This work addresses the challenges in efficiently optimizing hybrid SQL and AI/ML queries, which stem from the black-box nature of ML models, complex data dependencies, and a vast optimization space, compounded by the absence of a unified evaluation framework. To bridge this gap, the authors present an interactive benchmarking platform built on DuckDB, featuring a unified execution backend and a web-based visualization interface. The system supports abstract logical plan representation, extensible rewrite rules, traceable optimization decisions, and comparative analysis of multiple optimization strategies. For the first time, this platform enables transparent and reproducible development of SQL+AI/ML co-optimizers alongside end-to-end benchmarking, allowing users to intuitively inspect the optimization process and quantitatively evaluate the performance of diverse optimization approaches.

This work addresses the challenge of efficiently expressing and solving constrained optimization problems over relational data, bridging the gap from descriptive to prescriptive analytics. To this end, the authors propose an extension to SQL that introduces two declarative constructs—CREATE CANDIDATES and DECIDE—enabling users to naturally specify candidate solutions, constraints, and optimization objectives, with the system automatically computing optimal decisions. This approach represents the first seamless integration of combinatorial optimization logic into the SQL framework, preserving relational algebra semantics while supporting canonical scenarios such as subset selection, resource allocation, and task scheduling. Furthermore, it provides extensible mechanisms for handling uncertainty-aware optimization and model scoring, thereby laying a foundation for the evolution of declarative decision-making languages.

Traditional database systems rely on static rewrite rules that struggle to adapt to diverse queries and system characteristics, while existing large language model (LLM)-based approaches suffer from an excessively large search space, unreliable validation, and insufficient use of metadata. This work proposes a plug-in optimization layer that integrates catalog and statistical metadata to generate templated rules guiding LLM-based SQL rewriting. Semantic correctness is verified using sampled data, and candidate plans are ranked to enhance performance. The method is compatible with PostgreSQL, MySQL, and DuckDB, achieving up to 16× speedup over native DBMS optimizers and 22× over current LLM-based methods across eight benchmarks, with individual queries accelerated by over 600×—significantly surpassing the limitations of both traditional rule-based and pure LLM-driven approaches.