Column Sketch Indexing for Fast Predicate Evaluation
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database indexing methods, such as B-trees and early pruning techniques, fail to significantly improve scan performance across various selectivity levels and data distributions, particularly due to inefficiencies in data access and predicate evaluation, especially when data does not exhibit clustering properties or has low selectivity.
Innovation Solution
The introduction of a 'column sketch' indexing scheme that applies lossy compression to map base data values to smaller code values in an auxiliary sketched column, allowing predicate evaluation primarily in the sketched column, thereby reducing data access and improving CPU efficiency and scan performance.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If traditional secondary indices such as B-trees are used to localize data access, then data access is improved for low selectivity predicates, but scan performance deteriorates and memory access patterns become inefficient due to gaps and non-contiguous data retrieval
Solution Approach 1:
The patent creates a copy of the base column data in a compressed format (dictionary encoding) that preserves the ability to evaluate predicates. This compressed copy allows efficient scanning without accessing the full base data, resolving the contradiction between fast data access and high scan performance.
Solution Approach 2:
The patent changes the parameter of data representation from full-precision base values to compressed code values. This parameter change enables both fast predicate evaluation (through code comparison) and efficient scanning (through contiguous memory access), while maintaining the ability to accurately evaluate predicates through the compression map.
2Productivity
If early pruning techniques such as Bit-Slicing are used to decompose predicates, then data skipping is improved for informative high order bits, but performance deteriorates when data exhibits skew and high order bits provide little pruning
Solution Approach 1:
The patent performs preliminary compression of the base column into a dictionary-encoded format before predicate evaluation. This preliminary action creates a compact representation that enables efficient scanning and predicate evaluation regardless of data distribution characteristics, making the performance consistent across different data scenarios.
Solution Approach 2:
The patent extracts only the essential information needed for predicate evaluation into a compressed format. By storing only the compression map and compressed column rather than the full base data, the system achieves fast evaluation without being affected by data skew issues that plague bit-slicing methods.
3Productivity
If lossy compression is applied to map base data to smaller code values, then data movement is reduced and CPU efficiency is improved, but some precision is lost in the compressed representation
Solution Approach 1:
The patent creates a compressed copy of the base column that preserves predicate evaluation accuracy. The compression map stores the mapping from code values to base values, allowing the system to work with compact code values during scanning while maintaining the ability to accurately evaluate predicates by referencing the compression map when needed.
Data Source
AI summary
Various approaches for accelerating data access to a computer memory and predicate evaluation includes storing, in the computer memory, (i) base data as multiple base columns, (ii) multiple sketched columns each corresponding to a base column in the base data and having smaller code values compared thereto, and (iii) a compression map for mapping one or more base columns to the corresponding sketched column; applying the compression map to a query having a predicate; determining data on the sketched column that satisfies the predicate; and evaluating the predicate based at least in part on the determined data on the sketched column without accessing the base column in the base data.