Interactive Query Plan Visualization for Database Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database query systems lack transparency and efficiency due to users' unfamiliarity with query execution flow, leading to inefficient query plans that consume excessive resources and cause delays, as users cannot visualize or optimize queries effectively.
Innovation Solution
A computer system that allows users to interactively formulate, visualize, and modify query plans using decision trees with selectable nodes, mapping estimated cost and execution time using a rectangle metaphor, enabling the exploration of alternative plans and optimizing query execution.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Ease of operation
If the query optimizer automatically constructs query execution plans, then the querying process is simplified for users, but the query plans may be inefficient due to lack of user knowledge about database statistics
Solution Approach 1:
The patent introduces an intermediary visualization system between the user and the database optimizer. This system displays the query execution plan in a visual format (such as a graph or tree structure) that allows users to understand and manipulate the execution flow without needing deep knowledge of database statistics and optimization algorithms. The visualization acts as a mediator that translates complex optimizer decisions into comprehensible visual representations.
Solution Approach 2:
The system provides feedback to users by visualizing the query execution plan and allowing users to see the impact of their query modifications. Users can observe how changes to their SQL queries affect the execution plan structure and performance estimates, enabling them to iteratively refine their queries for better efficiency while maintaining ease of use.
2Productivity
If the system provides detailed transparency into query execution plans, then users can optimize queries effectively, but the user interface complexity increases
Solution Approach 1:
The patent segments the query execution plan into discrete visual elements that represent different operations (scans, joins, filters, etc.). Each element can be individually examined and manipulated, allowing users to understand complex execution plans by breaking them down into manageable components rather than presenting a monolithic view of the entire plan.
Solution Approach 2:
The system transforms the one-dimensional textual representation of query plans into a two-dimensional visual layout. This dimensional change allows users to spatially organize and comprehend the execution flow, making it easier to identify optimization opportunities without increasing interface complexity. The visual dimension provides intuitive spatial relationships that text alone cannot convey.
3Productivity
If users manually optimize query execution plans, then query efficiency improves, but the time and effort required for query formulation increases
Solution Approach 1:
The system performs preliminary actions by automatically generating an initial query execution plan and visualizing it for the user. This pre-computed plan serves as a starting point that users can review and modify if needed, rather than requiring users to manually construct optimization strategies from scratch. The preliminary visualization saves time by providing immediate feedback on the automatic optimizer's choices.
Solution Approach 2:
The system allows users to perform partial optimization by enabling selective manipulation of specific nodes in the visualized execution plan. Users can choose to optimize only the portions of the query that are problematic, rather than requiring complete manual re-optimization of the entire query. This partial action approach reduces the time investment required while still achieving efficiency improvements.
Data Source
AI summary
A computer system affords a user the ability to interactively formulate, visualize, and modify query plans that are to be posed to a database. The computer system provides query visualization in the form of decision trees having nodes selectable/deselectable to search for alternative query plans with lower or minimum execution time. The estimated cost and/or the execution time of a subtree of a query plan may be mapped according to a rectangle area metaphore, with the area of a rectangle enclosed by a larger rectangle representing a parent node in the plan tree. Query plans may be saved by the system in order to facilitate their visualization, optimization, and/or modification at a later date.