Pruning Index for Regular Expression Query Processing
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Processing queries on large tables is challenging due to the significant time and computing resources required to scan the entire table, necessitating a method to reduce the scan set and enhance query efficiency.
Innovation Solution
A pruning index is created to identify a subset of micro-partitions to scan by generating fingerprints for N-grams of searchable values, using a probabilistic data structure like a blocked bloom filter, which allows for efficient filtering of non-pertinent data and reduces indexing and storage costs through prefix compression.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Reliability
If a full table scan is performed to execute queries on large tables, then all data can be examined to satisfy query predicates, but significant time and computing resources are consumed
Solution Approach 1:
A pruning index is pre-computed and stored, containing filtered representations of the table data organized by micro-partition. When a query arrives, this pre-computed index allows the system to quickly identify which micro-partitions may contain matching data without scanning the entire table, thus resolving the contradiction between complete data examination and reduced query execution time
Solution Approach 2:
The pruning index acts as an intermediary data structure between the full table and the query processing logic. It provides a compressed, organized view of the data that enables efficient predicate evaluation while maintaining the ability to identify all potentially matching rows, thus reducing time consumption without sacrificing result completeness
2Loss of time
If a pruning index is created to reduce the scan set, then query execution time is reduced, but additional indexing and storage costs are incurred
Solution Approach 1:
The pruning index is organized by micro-partitions, allowing different portions of the index to have different characteristics optimized for their specific data. This local organization enables efficient storage where only the necessary index information for each micro-partition is maintained, reducing overall storage requirements while still enabling fast query execution
Solution Approach 2:
The pruning index uses compressed representations of the data with optimized parameter choices for storage efficiency. By carefully managing the balance between index detail and compression, the system achieves significant query speedups while keeping the index size manageable and not excessively increasing storage costs
3Ease of manufacture
If conventional scanning methods are used, then implementation is simple, but query processing efficiency is low
Solution Approach 1:
The pruning index is pre-computed and stored, allowing query processing to benefit from optimized data organization without requiring complex real-time computations. This preliminary preparation enables efficient query execution while keeping the implementation approach relatively straightforward by building on existing indexing concepts
Solution Approach 2:
The pruning index serves as an intermediary layer that maintains compatibility with existing query processing architectures. It integrates with the database system's existing predicate evaluation and micro-partition management, providing efficiency improvements without requiring complete redesign of the query processing pipeline
Data Source
AI summary
A query directed at a source table organized into a set of batch units is received. The query comprises a regular expression search pattern. The regular expression search pattern is converted to a pruning index predicate comprising a set of substring literals extracted from the regular expression search pattern. A set of N-grams is generated based on the set of substring literals extracted from the regular expression search pattern. A pruning index associated with the source table is accessed. The pruning index indexes distinct N-grams in each column of the source table. A subset of batch units to scan for data matching the query are identified based on the pruning index and the set of N-grams. The query is processed by scanning the subset of batch units.