Database Partition Query Optimization via Performance Data Feedback
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Current database management systems lack tools to provide users with performance information for partitioned database tables, making it difficult for users to effectively tune query performance.
Innovation Solution
A method to determine and present performance data for query optimization on databases with multiple partitions, including options to create non-spanning indexes, which helps in identifying unexpanded partitions, runtime estimates, partition elimination, and unused indexes, thereby aiding in improving query performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If database tables are partitioned to improve query performance, then query execution speed is improved, but it becomes difficult to tune and optimize query performance without proper performance data
Solution Approach 1:
The system implements feedback by collecting actual query performance data from partitioned database operations and presenting it to users through a user interface. This feedback loop enables users to see the actual impact of partitioning on query performance, allowing them to make informed decisions about partitioning strategies and query optimization without requiring deep expertise in database internals.
Solution Approach 2:
The patent introduces an intermediary component that acts as a bridge between the complex partitioned database system and the user. This intermediary collects performance data, processes it, and presents it in a usable format through the user interface, along with providing recommendations. This intermediary shields users from the complexity of partitioned database optimization while still enabling effective query tuning.
2Ease of operation
If comprehensive performance data is collected to enable query optimization, then query tuning capability is improved, but system complexity increases
Solution Approach 1:
The system segments the complex task of query optimization into manageable components by collecting specific performance data points (such as partition access patterns, query execution times, and index usage) and presenting them separately through the user interface. This segmentation allows users to focus on individual aspects of query performance without being overwhelmed by the entire system's complexity.
Solution Approach 2:
The system implements self-service by automatically collecting performance data, analyzing it, and generating optimization recommendations without requiring users to manually instrument the database or perform complex analysis. The user interface automatically presents actionable insights and recommendations, allowing users to optimize queries without needing to understand the underlying system complexity.
Data Source
AI summary
A method, apparatus, system, and signal-bearing medium that, in an embodiment, determine performance data for query optimization of a query directed to a database table having multiple partitions. The performance data for the query optimization is presented in a user interface, and an option is provided in the user interface to create a non-spanning index for any of the partitions mentioned in the performance data. In various embodiments, determining the performance data may include: determining the partitions that were unexpanded by the query optimization, determining the partitions that had a runtime estimate that was different from an actual query runtime by a threshold, determining whether any of the partitions had expansion forced by a client request, determining whether the query performed partition elimination, determining whether any of the partitions has an associated index that is unused by the query and that also has a key that is not present in all of the partitions, and determining a constraint for partition elimination.