Query Predicate Ordering Using Worst-Case Selectivity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query optimization methods struggle with performance degradation due to skewed data distributions, particularly when using worst-case compile-time selectivity estimates, leading to inefficient query execution plans.
Innovation Solution
A combined approach using worst-case selectivity compile-time engine and adaptive runtime engine to determine the order of predicate evaluation, optimizing query plans by considering both worst-case and estimated selectivities, and dynamically adapting query execution strategies based on actual parameter values.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If worst-case compile-time selectivity estimates are used to determine predicate evaluation order, then query performance is improved for skewed data distributions, but device complexity increases due to the need for both compile-time and runtime engines
Solution Approach 1:
The system is divided into two distinct engines: a compile-time engine that pre-calculates predicate evaluation orders using worst-case selectivity estimates, and a runtime engine that executes queries using the pre-determined orders. This segmentation allows each engine to specialize in its respective function, improving overall query performance while managing complexity through clear separation of concerns.
Solution Approach 2:
The compile-time engine performs preliminary analysis by calculating worst-case selectivity estimates and determining optimal predicate evaluation orders before actual query execution. These pre-computed results are stored and reused during runtime, avoiding the need to re-calculate selectivity estimates for each query and thereby improving query performance without proportionally increasing runtime complexity.
2Productivity
If predicate evaluation order is determined based on initial parameter values, then query execution efficiency is improved, but adaptability decreases when actual parameter values differ from initial values
Solution Approach 1:
The system uses worst-case selectivity estimates that are independent of specific parameter values, allowing the predicate evaluation order to remain optimal across different parameter scenarios. By changing the basis of optimization from specific parameter values to worst-case scenarios, the system maintains both execution efficiency and adaptability to varying parameters.
3Device complexity
If a single query plan is generated based on estimated selectivity, then device complexity is reduced, but query performance degrades due to poor performance on skewed data distributions
Solution Approach 1:
The compile-time engine creates a template or copy of the optimal predicate evaluation order based on worst-case selectivity estimates. This template is then reused across multiple query executions without modification, allowing the system to achieve high performance on skewed data distributions while maintaining low complexity through template reuse rather than generating unique plans for each query.
Data Source
Figure 1
Figure 2
Figure 3
AI summary
The present disclosure involves systems, software, and computer implemented methods for using worst-case predicate selectivity for database query plans. One example method includes identifying a query that includes an initial set of parameter values for a set of predicates. An order of predicate evaluation is determined for a query plan by determining a worst-case selectivity for each predicate and ordering the predicates based on the worst-case selectivities. A second instance of the query is received that includes a second set of parameter values. The second instance of the query is executed, to generate a query result, using the query plan and the second set of parameters by evaluating the set of predicates based on the second set of parameters and the order of predicate evaluation determined based on the worst-case selectivities. The query result is provided in response to the second instance of the query.