Top K Query Runtime Pruning for Reduced Table Scanning
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Conventional database systems face challenges in optimizing query performance, particularly for large datasets, as they require significant time and computing resources to scan entire tables, struggling to provide optimal performance for both transactional and analytical workloads simultaneously.
Innovation Solution
Implementing runtime pruning mechanisms that dynamically calculate and apply boundaries during query execution, reducing the amount of data scanned and processed by utilizing both key and non-key columns, and integrating read-version pruning for efficient data management in hybrid tables.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Measurement precision
If the entire table is scanned to identify data satisfying the query, then complete query results are obtained, but significant time and computing resources are required
Solution Approach 1:
The system performs preliminary actions by maintaining sorted data structures and pre-calculating boundary values during data insertion and update operations. This allows the query execution to start with pre-organized data and pre-determined boundaries, avoiding the need to scan the entire table while ensuring complete query results are obtained.
Solution Approach 2:
The invention extracts only the necessary portion of data that satisfies the query by using boundary values to define a limited scan range. Instead of scanning the entire table, the system extracts and processes only the data within the calculated boundaries, significantly reducing the amount of data scanned while maintaining result completeness.
2Measurement precision
If the entire table is scanned to identify data satisfying the query, then complete query results are obtained, but significant computing resources are required
Solution Approach 1:
The system performs preliminary actions by maintaining sorted data structures and pre-calculating boundary values during data insertion and update operations. This allows the query execution to start with pre-organized data and pre-determined boundaries, avoiding the need to scan the entire table while ensuring complete query results are obtained.
Solution Approach 2:
The invention extracts only the necessary portion of data that satisfies the query by using boundary values to define a limited scan range. Instead of scanning the entire table, the system extracts and processes only the data within the calculated boundaries, significantly reducing the amount of data scanned while maintaining result completeness.
3Productivity
If runtime pruning is applied to reduce data scanned, then query performance is improved, but system complexity increases
Solution Approach 1:
The system performs preliminary actions by maintaining sorted data structures and pre-calculating boundary values during data insertion and update operations. This allows the query execution to start with pre-organized data and pre-determined boundaries, avoiding the need to scan the entire table while ensuring complete query results are obtained.
Solution Approach 2:
The data structure maintains itself in a sorted state automatically during insertion and update operations, and automatically calculates boundary values based on query parameters. This self-organizing behavior reduces the need for complex external pruning logic while still achieving significant query performance improvements.
Data Source
AI summary
A top K query directed at a table is received. Run-time pruning is performed during execution of the top K query on the table. The run-time pruning comprises determining, by a top K node, a current boundary based on a set of values identified by a table scan node in scanning the table and applying, by the table scan node, the current boundary to prune data during the scanning of the table. The applying of the current boundary comprises reducing scanning ranges of the table scan node based on the top K column being a key column of the table and filtering values scanned by the table scan node based on the top K column being a non-key column of the table. The result set is returned responsive to the top K query based on the run-time pruning performed during execution of the top K query on the table.