Hash Table Skewed Value Deduplication in Parallel Database Queries

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Massively parallel processing systems face performance bottlenecks due to data skew in DISTINCT and aggregation queries, where highly skewed values lead to repetitive redistribution of rows, causing system inefficiencies.

Innovation Solution

Each processing module in the system maintains a hash table of skewed attribute values, allowing it to discard or redistribute rows based on the presence of these values, thereby alleviating bottlenecks and improving query processing efficiency.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Reliability

If hash redistribution is applied to all rows in DISTINCT queries, then duplicate values are removed correctly, but system bottlenecks occur due to repetitive redistribution of highly skewed values

Engineering Contradiction:

Improvecorrectness of DISTINCT query resultsVSAvoidquery processing throughput

Core Design Contradiction:

ReliabilityVSProductivity

Solution Approach 1:

The system applies different processing strategies to different data categories: skewed values are handled through local hash table deduplication at each processing module, while non-skewed values undergo traditional hash redistribution. This local quality differentiation resolves the contradiction by optimizing the common case (skewed values) locally without compromising the correctness handling of all cases.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The system performs preliminary identification of skewed values using statistics or sampling before executing the DISTINCT query. This preliminary action allows the system to prepare hash tables in advance and avoid repetitive redistribution of known skewed values, thereby improving throughput while maintaining correctness.

Inventive Principle:

Principle #10Preliminary action

2Stability of the object's composition

If traditional hash redistribution is used for all attribute values, then data is evenly distributed across processing modules, but highly skewed values cause system bottlenecks due to repetitive redistribution

Engineering Contradiction:

Improveeven distribution of dataVSAvoidtime spent on repetitive redistribution

Core Design Contradiction:

Stability of the object's compositionVSLoss of time

Solution Approach 1:

The system applies different distribution strategies to different value types: skewed values are deduplicated locally at each processing module using hash tables, while non-skewed values maintain even distribution through traditional hash redistribution. This resolves the contradiction by preserving stability for non-skewed data while eliminating time loss for skewed data.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The system extracts highly skewed values from the general data stream through preliminary analysis and handles them separately through local hash table operations. This extraction removes the bottleneck-causing elements from the repetitive redistribution process, reducing time loss without affecting the even distribution of the remaining data.

Inventive Principle:

Principle #2Taking out (Extraction)

3Productivity

If skewed values are handled with special processing, then system bottlenecks are reduced, but complexity increases due to hash table management and skewed value identification

Engineering Contradiction:

Improvequery processing throughputVSAvoidcomplexity of query processing logic

Core Design Contradiction:

ProductivityVSDevice complexity

Solution Approach 1:

The system uses the data's own characteristics (frequency of skewed values) to identify and handle them automatically. By leveraging the inherent skew pattern in the data, the system self-identifies which values need special handling without requiring complex external analysis, thus improving throughput while limiting complexity increase.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system changes the processing parameter (from global hash redistribution to local hash table operations) specifically for skewed values based on their identified characteristics. This parameter change is driven by the data's own skew properties, allowing the system to improve throughput through targeted optimization rather than universal complexity.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS8234268B2System, method, and computer-readable medium for optimizing processing of distinct and aggregation queries on skewed data in a database system

Publication Date: 2012.07.31 TERADATA CORP

AI summary

A system, method, and computer-readable medium for optimization of query processing in a parallel processing system are provided. Skewed values and non-skewed values are treated differently to improve upon conventional DISTINCT and aggregation query processing. Skewed attribute values on which a DISTINCT selection or group by aggregation is applied are allocated entries in a hash table. In this manner, a processing module may consult the hash table to determine if a skewed attribute value has been encountered during the query processing in a manner that precludes repetitive redistribution of rows with highly skewed attribute values on which a DISTINCT selection or group by aggregation is applied.