Query Processing and Optimisation

Query processing and optimisation form the backbone of relational database management, transforming high‐level declarative requests into efficient execution strategies. The pipeline begins with parsing and rewriting the SQL statement into a logical plan composed of algebraic operations such as selection, projection and join. A cost‐based optimiser then explores equivalent logical expressions—guided by metadata like cardinality estimates, histograms and data dependencies—to select physical algorithms (for example, nested‐loop joins, hash joins or index‐based scans) that minimise resource consumption. Plan enumeration balances exhaustive search and heuristic pruning to navigate a combinatorial search space, while adaptive techniques and runtime feedback challenge the optimiser to refine cost models and cardinality estimates dynamically. Recent advances integrate statistical profiling, sampling and machine‐learning that enhance selectivity prediction, exploit workload patterns and support self‐tuning optimisers. Together, these developments yield responsive, scalable query execution across workloads ranging from short interactive transactions to long‐running analytical tasks, ensuring that modern data‐driven applications meet stringent performance and resource‐efficiency requirements.

Research from Nature Portfolio

No recent Nature Portfolio content available.

Research from all publishers

Emerging work has demonstrated how richer metadata beyond basic statistics can reshape plan generation. Surveys of advanced metadata highlight the incorporation of functional, inclusion and order dependencies into the optimiser’s pruning logic, reducing join‐order search spaces and improving plan quality. Complementary research proposes scalable algorithms for discovering functional dependencies in distributed big data, leveraging sampling and prefix‐tree structures to validate candidate rules in parallel and feed semantic relationships back into cost models. These methods accelerate dependency discovery without sacrificing accuracy, thereby enabling more precise cardinality estimates and join choices in large‐scale systems.

Parallel lines of inquiry have applied machine‐learning to multiple layers of query processing. Systematic reviews of deep learning approaches classify neural models that refine index structures, enhance selectivity estimation and automate parameter tuning, reporting measurable gains in join ordering and access‐method selection. In the realm of join ordering, reinforcement‐learning frameworks model plan enumeration as a Markov decision process, employing double‐deep Q‐networks with progressive exploration strategies to mitigate overestimation and to generalise across unseen query shapes. Finally, analytical algorithms for inequality‐join selectivity estimation have been devised to achieve sub‐percent error across diverse data distributions, rectifying gaps in open‐source systems and realigning optimisers with their theoretical cost foundations.

Query Processing and Optimisation publication trend

The graph below shows the total number of articles in query processing and optimisation across all publications each year (not limited to Nature Index journals).

Technical terms

Cardinality estimation: Prediction of the number of rows produced by a query operator, crucial for cost calculation.

Cost model: A mathematical framework that translates cardinality and resource metrics into scalar costs for comparing execution plans.

Join order: The sequence in which relations are combined during query execution, significantly affecting intermediate result sizes and overall cost.

Functional dependency: A semantic rule stating that the value of one attribute uniquely determines the value of another, used to refine selectivity estimates and prune plan search.

Reinforcement learning: A machine‐learning paradigm where an agent iteratively learns plan selection policies via feedback based on execution performance, applied to join ordering and plan enumeration.

References

1. An Efficient and Scalable Algorithm to Mine Functional Dependencies from Distributed Big Data. Sensors (2022).
2. Data dependencies for query optimization: a survey. The VLDB Journal (2021).
3. Selectivity Estimation of Inequality Joins in Databases. Mathematics (2023).
4. A Survey on Advancing the DBMS Query Optimizer: Cardinality Estimation, Cost Model, and Plan Enumeration. Data Science and Engineering (2021).
5. A systematic review of deep learning applications in database query execution. Journal of Big Data (2024).
6. Query Join Order Optimization Method Based on Dynamic Double Deep Q-Network. Electronics (2023).
7. Query Processing.