Query Operator Flow Reordering for Filtering on Derived Columns
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face limitations in processing speed due to hardware constraints, data storage methods, and restricted co-processing options, leading to inefficiencies in handling large volumes of data.
Innovation Solution
A database system architecture that employs parallelized data input, storage, and query processing systems, utilizing a network of computing devices and nodes with independent processing core resources to optimize query execution and data handling, including segmenting, restructuring, and optimizing query plans for efficient data storage and retrieval.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If traditional sequential processing methods are used in database systems, then hardware resource utilization is simplified, but processing speed and efficiency deteriorate when handling large volumes of data
Solution Approach 1:
The patent divides the database system into multiple independent computing devices (first computing device, second computing device, etc.), each with its own processing core resources. Data is segmented and distributed across these devices, allowing parallel processing of different data portions simultaneously, thereby increasing overall processing speed without requiring a single complex monolithic system
Solution Approach 2:
The patent transitions from traditional single-device sequential processing to a multi-device distributed architecture, adding a spatial dimension to processing. Multiple computing devices operate concurrently on different data segments, transforming the processing model from one-dimensional sequential execution to multi-dimensional parallel execution across the network
2Productivity
If data is stored in traditional formats and structures, then storage implementation is straightforward, but query processing efficiency deteriorates when filtering based on new or frequently accessed columns
Solution Approach 1:
The patent implements columnar storage where data is organized by columns rather than rows, and further segments columns into micro-partitions. Frequently queried columns are stored separately and can be processed independently. When a query filters on a specific column, only the relevant micro-partitions containing that column are accessed, dramatically improving query efficiency without requiring complete table scans
Solution Approach 2:
The system pre-processes and pre-organizes data into columnar micro-partitions during data ingestion and loading operations. This preliminary organization creates an optimized storage structure where data is already grouped by column and partitioned by value ranges, so that subsequent query operations can immediately exploit this structure without requiring complex runtime reorganization
3Loss of time
If filtering operations are performed on all data blocks, then complete data coverage is achieved, but processing time increases significantly when only specific column values are needed
Solution Approach 1:
The patent implements selective filtering where the system evaluates and applies filters only to relevant micro-partitions that contain the requested column values, rather than processing all data blocks uniformly. The query optimizer identifies which micro-partitions can satisfy the filter condition and processes only those, performing partial action on the data set while maintaining complete coverage of relevant data
Solution Approach 2:
Data is segmented into multiple micro-partitions organized by column and value ranges. When a filter operation is applied, the system segments the processing task by identifying and processing only the specific micro-partitions that contain matching values, rather than applying the filter uniformly across all data blocks. This segmentation enables parallel processing of filter operations on different partitions simultaneously
4Productivity
If hardware resources are constrained, then system cost is reduced, but processing capability and throughput deteriorate when handling massive datasets
Solution Approach 1:
The patent divides the data processing workload into multiple segments distributed across available computing devices. Each device processes a portion of the data in parallel, increasing overall throughput without requiring any single device to have excessive hardware resources. The segmentation allows the system to scale horizontally by adding more modest-capacity devices rather than requiring one powerful machine
Solution Approach 2:
The system automatically optimizes query execution plans by selecting appropriate filtering strategies based on the specific query characteristics and data distribution. The query optimizer self-adjusts to use columnar micro-partition filtering when beneficial, automatically exploiting the stored data structure to reduce processing requirements without needing additional hardware resources or manual intervention
Data Source
AI summary
A database system is operable to generate an initial query operator execution flow for a corresponding query for execution that includes an extend operation serially before a column-based filtering operation indicating filtering by new column values of a new column generated via the extend operation as a function of an existing column. The initial query operator execution flow is converted into an updated query operator execution based on pushing the column-based filtering operation serially before the extend operation, and updating the column-based filtering operation to filter by existing column values of the existing column via applying an inverse function of the function. The corresponding query is executed by applying the updated query operator execution flow based on executing the column-based filtering operation upon a plurality of rows to generate a filtered subset of the plurality of rows, and executing the extend operation upon only rows in the filtered subset.