Compression-Aware Aggregation for Column Partitioned Queries
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems struggle to apply aggregate push-down logic effectively when queries involve multiple columns or multiple grouping fields in column-partitioned tables, especially when aggregates in the select list or grouping fields involve expressions, as traditional methods cannot leverage run-length encoding and result in inefficient query processing.
Innovation Solution
The method involves breaking down generated executable code into chunks for each expression, enabling push-down aggregation at the column partition level, and utilizing in-memory buffers to accelerate expression evaluation and run-length encoding, thereby improving query performance by leveraging compression information in column partitions.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Productivity
If aggregate push-down logic is applied to column-partitioned tables with expressions or multiple grouping fields, then query performance is improved and data traffic is reduced, but traditional methods cannot handle these complex cases
Solution Approach 1:
The patent segments the aggregate push-down logic into distinct handling paths: one for simple column references and another for complex expressions with multiple grouping fields. This segmentation allows the system to apply optimized push-down aggregation where applicable while maintaining support for complex query structures through alternative processing paths.
Solution Approach 2:
The patent extends aggregate push-down from traditional single-column grouping to multi-dimensional grouping scenarios involving multiple grouping fields and expressions. By adding dimensional complexity to the push-down capability, the system can now handle GROUP BY clauses with multiple columns and aggregate expressions that combine multiple columns, transforming the limitation into a capability.
2Quantity of substance
If run-length encoding is leveraged for compression in column partitions, then storage efficiency is improved, but query processing cannot utilize this compression when expressions are involved
Solution Approach 1:
The patent performs preliminary evaluation of aggregate expressions during the query planning phase to determine whether push-down aggregation can be applied. By pre-assessing the expression structure and grouping field complexity, the system can identify opportunities to leverage run-length encoding compression before query execution begins, thus maintaining both storage efficiency and query processing efficiency.
Solution Approach 2:
The patent changes the operational parameters of aggregate expression evaluation by moving the evaluation context from the query processing layer down to the storage layer where run-length encoding is native. This parameter change allows the system to evaluate expressions directly over compressed data, utilizing the compression structure rather than overcoming it, thereby maintaining productivity while benefiting from storage efficiency.
Data Source
AI summary
A system and method for extending compression-aware aggregation logic to column partitioned database sources when an SQL query involves simple or complex aggregate expressions. The logic can be applied when there are multiple fields specified in a Group By clause, when a Group By clause includes an expression involving multiple columns from a column partitioned table, or when there is no Group By clause in the query. This logic extends the benefits of push-down aggregation to complex aggregate queries to build partially aggregated rows that can be directly added into an intermediate cache. For cases where the fields within aggregate expressions are themselves compressed, the aggregation techniques leverage the compression information of the aggregate fields. This aggregation mechanism can be applicable to compression techniques including run-length encoding (RLE), value list compression (VLC) and Presence, Delta on Mean (PDM) on columnar source tables such as Column Partitioned (CP) or Parquet tables.