Dynamic Index Selection for Database Query Optimization
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
As databases grow in size and complexity, existing indexing techniques struggle to efficiently access data, particularly when query patterns and data distributions change dynamically.
Innovation Solution
The technology dynamically selects an index from a set of learned and non-learned indexes based on query features and data characteristics, using a learned model to predict the most efficient index for data access.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Adaptability or versatility
If traditional indexing techniques are used, then database access is efficient for static data patterns, but performance degrades when query patterns and data distributions change dynamically
Solution Approach 1:
The patent implements dynamic index selection by training a machine learning model to predict optimal indexes based on current query patterns and data distributions. The system transitions from static index selection to dynamic adaptation, where the learned index adapts its structure and selection based on observed workload characteristics, resolving the contradiction between adaptability and complexity.
Solution Approach 2:
The system employs self-service mechanisms where the database automatically trains and updates the learned index based on observed query patterns without manual intervention. The model continuously learns from workload characteristics and automatically adjusts index selection, enabling the system to adapt to changing patterns while managing complexity through automation.
2Productivity
If multiple indexes are maintained for different access patterns, then query performance improves, but storage requirements and maintenance overhead increase
Solution Approach 1:
The patent implements a universal learned index that can serve multiple query patterns and access types through a single adaptive structure. Rather than maintaining separate traditional indexes for different access patterns, the learned index dynamically adapts its behavior based on the specific query characteristics, providing multi-functionality while reducing the quantity of index structures needed.
Solution Approach 2:
The system changes the fundamental parameters of index storage by using a learned model that compresses and represents multiple access patterns in a unified parameter space. The learned index stores learned relationships and transformations rather than redundant index structures, reducing storage requirements while maintaining efficient access for various query types.
3Reliability
If learned indexes are used, then adaptability to data distributions improves, but computational overhead for model training increases
Solution Approach 1:
The patent applies preliminary action by pre-training the learned index model during off-peak periods or using representative workload samples before actual query processing. The model is trained in advance on historical data patterns, so that during production use, the system benefits from the pre-learned knowledge without incurring continuous training overhead, balancing reliability with energy consumption.
Solution Approach 2:
The system maintains continuous useful action by implementing incremental or online learning mechanisms that update the learned index continuously based on incoming query patterns. Rather than periodic batch training that causes spikes in computational energy, the model continuously adapts in small steps, maintaining reliability while distributing the computational load evenly over time.
Data Source
AI summary
The described technology relates to systems and techniques for accessing a database by dynamically choosing an index from a plurality of indexes that includes at least one learned index and at least one non-learned index. The availability of learned and non-learned indexes for accessing the same database provides for flexibility in accessing the database, and the dynamic selection between learned indexes and non-learned indexes provide for choosing the index based on the underlying data in the database and the characteristics of the query. Certain example embodiments provide a learned model that accepts a set of features associated with the query as input, and outputs a set of evaluated weights for respective features, which are then processed according to a set of rules to predict the most efficient index to be used.