Runtime Query Plan Adaptation for Skewed Predicate Selectivity
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database query systems struggle to efficiently handle queries with skewed data distributions, where existing approaches fail to adaptively modify query plans based on actual parameter values at runtime, leading to suboptimal performance and resource wastage.
Innovation Solution
Implement a dynamic query plan adaptation system that combines compile-time worst-case selectivity estimation with adaptive runtime techniques, modifying predicate evaluation order and strategy based on actual parameter values and matching value counts to optimize query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If a fixed query plan is used for all queries, then query execution is simple and fast, but query performance deteriorates when data distributions are skewed or change over time
Solution Approach 1:
The query plan is transformed from a static, fixed structure to a dynamic one that can adapt at runtime. The system evaluates predicates in a determined order and can switch between index lookup and data vector scan strategies based on runtime conditions, allowing the query plan to dynamically adjust to actual data distributions and query patterns.
Solution Approach 2:
The system changes execution parameters dynamically by selecting different predicate evaluation strategies (index lookup vs. data vector scan) based on runtime estimated selectivities and matching value counts. This parameter change allows the same query plan template to adapt to different data distributions without requiring plan rewriting.
2Measurement precision
If runtime estimated selectivities and matching value counts are calculated for every predicate, then query plan accuracy improves, but computing overhead and time consumption increase
Solution Approach 1:
The system performs preliminary actions by pre-calculating and storing frequency statistic metadata during data loading or maintenance operations. This pre-computed metadata is then used at runtime to quickly determine selectivity estimates without performing expensive real-time calculations, thus reducing runtime overhead while maintaining accuracy.
Solution Approach 2:
The system uses frequency statistic metadata as a copy or approximation of the actual data distribution. Instead of performing exhaustive runtime analysis, the system queries pre-computed statistical summaries that replicate the essential selectivity information, significantly reducing computation time while preserving estimation accuracy.
3Reliability
If multiple index lookups are performed for different predicate values, then query completeness is maintained, but computing resources and execution time are wasted
Solution Approach 1:
The system skips unnecessary index lookups by first evaluating predicates in an optimized order and using runtime estimated selectivities to determine which predicates are most likely to reduce the result set. By rushing through the most selective predicates first, the system can skip less effective index lookups that would otherwise be performed, reducing overall computing resource consumption while maintaining result completeness.
Data Source
AI summary
The present disclosure involves systems, software, and computer implemented methods for dynamically adapting query plans for database queries. One example method includes receiving a query that includes a set of parameter values for a set of predicates. A query plan includes a predicate evaluation order for the set of predicates and a predicate evaluation strategy for each predicate. Runtime estimated selectivities of predicates in the set of predicates are determined based on respective parameter values. Matching value counts of predicates are determined that each indicate a count of distinct values that match a respective predicate. The query plan is modified with respect to at least one predicate based on at least one of the runtime estimated selectivities or the matching value counts, to generate a modified query plan. The query is then executed according to the modified query plan.