Enabling Data Dependency-based Query Optimization

2024-06-11

arXiv.org

AI Summary

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.

Technology Category

• Search and Optimization: Distributed Search
• Constraint Satisfaction and Optimization: Distributed CSP/Optimization
• Data Mining & Knowledge Management: Intelligent Query Processing

Application Category

• Graph Algorithms and Modeling for the Web: Querying, indexing, and retrieval in Web-related graphs
• Search and Retrieval-Augmented AI: Efficiency and scalability of Web search engines
• Web Mining and Content Analysis: Topic discovery and tracking

Abstract

Data dependency-based query optimization techniques can considerably improve database system performance: we apply three such optimization techniques to five database management systems (DBMSs) and observe throughput improvements between 5 % and 33 %. We address two key challenges to achieve these results: (i) efficiently identifying and extracting relevant dependencies from the data, and (ii) making use of the dependencies through SQL rewrites or as transformation rules in the optimizer. First, the schema does not provide all relevant dependencies. We present a workload-driven dependency discovery approach to find additional dependencies within milliseconds. Second, the throughput improvement of a state-of-the-art DBMS is 13 % using only SQL rewrites, but 20 % when we integrate dependency-based optimization into the optimizer and execution engine, e. g., by employing dependency propagation and subquery handling. Using all relevant dependencies, the runtime of four standard benchmarks improves by up to 10 % compared to using only primary and foreign keys, and up to 22 % compared to not using dependencies. The dependency discovery overhead amortizes after a single workload execution.

Problem

• Automatically identifying and validating data dependencies for optimization
• Enhancing query performance using non-PK/FK dependencies
• Integrating dependencies without manual declaration or SQL rewrites

Innovation

• Automatically recognizes dependency candidates for optimization
• Efficiently validates applicability of dependencies
• Optimizes query plans using valid dependencies