Query Plan Overrides for Multi-Tenant Database Cost Estimation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Query optimizers in database systems may not always select the most desirable execution plan due to incorrect cost metrics, such as stale or missing statistics, and users may have better insights into the data and queries, but current systems lack the ability to effectively incorporate this user insight to improve query performance.
Innovation Solution
Implementing query optimizer constraints that allow users to restrict and guide the selection of execution plans by providing specific directives, such as preferred scan types, join operations, and index usage, while still leveraging the optimizer's intelligence to choose between favorable options.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the query optimizer automatically selects execution plans based on cost metrics, then the system maintains high automation and productivity, but the accuracy of cost estimation deteriorates when statistics are stale or missing
Solution Approach 1:
The patent introduces query optimizer constraints as an intermediary mechanism between the user and the query optimizer. These constraints act as guiding directives that restrict the optimizer's search space to user-approved execution plan options, thereby improving cost estimation accuracy without completely removing automation. The constraints serve as a mediator that combines user knowledge with the optimizer's automated evaluation capabilities.
2Measurement precision
If users provide detailed directives to restrict execution plan options, then cost estimation accuracy improves, but the ease of operation deteriorates due to increased complexity
Solution Approach 1:
The patent applies partial action by allowing users to provide constraints only for specific portions of the query execution plan that require guidance, rather than requiring complete specification of all execution plan details. This partial constraint approach improves accuracy for critical operations while maintaining simplicity for other aspects, balancing user effort with performance improvement.
3Adaptability or versatility
If the query optimizer evaluates all possible execution plans, then the adaptability improves, but the productivity deteriorates due to increased evaluation time
Solution Approach 1:
The patent segments the execution plan evaluation process by dividing all possible plans into multiple categories or groups based on user-defined constraints. Instead of evaluating every possible execution plan, the optimizer evaluates segmented subsets that satisfy the constraints, thereby maintaining adaptability within each segment while significantly improving overall productivity through reduced evaluation scope.
Data Source
AI summary
Techniques are disclosed relating to database query optimizers. In some embodiments, a query parser of a database system receives a query requesting data from a multi-tenant database. The query includes a first constraint that restricts a query optimizer's selection of a set of execution plans available to implement the query. The database system determines whether an override has been specified for a particular tenant associated with the received query, the override indicating that the first constraint is to be replaced with a second constraint for the particular tenant. In response to determining that the override has been specified for the particular tenant, the database system replaces the first constraint in the query with the second constraint and provides the query with the second constraint to the query optimizer for selection of an execution plan in accordance with the second constraint.