Adaptive Sparse Indexing for Cloud Data Warehouse Query Optimization

Resolve Bottlenecks,
Find Innovative SolutionsGenerate Solutions

Solution Overview

Problem

Existing adaptive indexing techniques in database systems are inefficient in dynamically matching indexes to fluctuating workload profiles, leading to suboptimal query processing performance and requiring user intervention or data reordering.

Innovation Solution

The method involves deriving table-scans from queries, specifying candidate sparse indexes, evaluating their pruning-power measures, and converting effective candidates into actual sparse indexes, which are continually adapted based on query execution statistics to optimize index usage without user involvement or data reordering.

Engineering Contradictions & Design Principles

VSEngineering Contradiction Analysis

1Adaptability or versatility

If traditional adaptive indexing techniques are used, then indexes can be created to prune database tables, but the indexes cannot dynamically adapt to fluctuating workload profiles

Engineering Contradiction:

Improveadaptability to workload profilesVSAvoidquery processing performance

Core Design Contradiction:

Adaptability or versatilityVSProductivity

Solution Approach 1:

The patent implements dynamic index structures that automatically adapt to changing workload characteristics. The system continuously monitors query patterns and adjusts index configurations in real-time, transforming static indexes into dynamic structures that evolve with workload fluctuations. This resolves the contradiction by making indexes both adaptable to new patterns and immediately effective for current queries.

Inventive Principle:

Principle #15Dynamics

Solution Approach 2:

The system incorporates feedback mechanisms that monitor query execution performance and use this information to guide index creation and optimization decisions. By continuously measuring actual query patterns and feeding this information back into the index management process, the system creates indexes that are precisely matched to actual workload requirements, improving both adaptability and performance.

Inventive Principle:

Principle #23Feedback

2Manufacturing precision

If user intervention is required for index creation, then indexes can be tailored to specific needs, but the system complexity increases and user burden grows

Engineering Contradiction:

Improveindex matching accuracyVSAvoiduser involvement requirement

Core Design Contradiction:

Manufacturing precisionVSEase of operation

Solution Approach 1:

The patent implements self-service mechanisms where the database system automatically analyzes query patterns, identifies optimal index candidates, and creates indexes without user intervention. The system autonomously performs workload analysis, evaluates potential indexes, and executes index creation based on learned patterns, eliminating the need for user expertise while maintaining high precision in index matching.

Inventive Principle:

Principle #25Self-service

Solution Approach 2:

The system replaces manual user operations with automated machine learning algorithms and statistical analysis tools. Instead of requiring users to manually specify index parameters, the system uses computational methods to automatically determine optimal index configurations, substituting human judgment with algorithmic precision and eliminating the operational burden on users.

Inventive Principle:

Principle #28Mechanics substitution (Replace mechanical system)

3Productivity

If dense indexes are used to improve query performance, then more data can be pruned, but the storage space and computational overhead increase significantly

Engineering Contradiction:

Improvequery processing speedVSAvoidstorage space consumption

Core Design Contradiction:

ProductivityVSQuantity of substance

Solution Approach 1:

The patent applies local quality by creating sparse indexes that store information selectively only where needed, rather than uniformly across all data. The system identifies specific regions or patterns in the data that benefit from indexing and applies index structures locally to those areas, maintaining high query performance while minimizing overall storage consumption by leaving non-critical areas unindexed.

Inventive Principle:

Principle #3Local quality

Solution Approach 2:

The system dynamically adjusts index parameters such as density, granularity, and selectivity based on workload characteristics and data patterns. By changing these parameters adaptively, the system optimizes the balance between query performance and storage overhead, using denser indexes when performance is critical and sparser indexes when storage is constrained, thereby resolving the contradiction between speed and space.

Inventive Principle:

Principle #35Parameter changes

Data Source

PatentUS11762831B2Adaptive sparse indexing in cloud-based data warehouses

Publication Date: 2023.09.19 FIREBOLT ANALYTICS LTD

AI summary

method for querying a data storage includes receiving queries for execution in the data storage, and deriving from the queries one or more table-scans over one or more tables of the data storage. Candidate sparse indexes, for pruning the table-scans, are specified based on the queries. Pruning-power measures, indicative of as effectiveness of the candidate sparse indexes in pruning the table-scans, are evaluated. One or more of the candidate sparse indexes are converted into actual sparse indexes, depending on the pruning-power measures. The queries are executed by performing the table-scans, including pruning the table-scans using the actual sparse indexes.