Multi-Attribute Index Selection for Columnar In-Memory Databases
Find Innovative SolutionsGenerate Solutions
Solution Overview
Problem
Existing database systems face challenges in efficiently selecting secondary indexes to balance memory consumption and performance gain, especially in large-scale systems with highly concurrent workloads, due to the complexity of index interactions and the need for mechanisms to handle NP-hard problems.
Innovation Solution
A method for index selection in columnar in-memory databases that uses a recursive, workload-driven approach to iteratively select and extend indexes, accounting for index interactions and reconfiguration costs, while minimizing the number of what-if optimizer calls.
Engineering Contradictions & Design Principles
Engineering Contradiction Analysis
1Speed
If indexes are selected to improve query performance, then runtime is reduced, but memory consumption increases
Solution Approach 1:
The patent changes the parameter of index evaluation from considering only individual index benefits to evaluating multi-attribute index combinations. The system transforms the selection criterion by incorporating interaction effects between index attributes, allowing it to identify configurations where multiple attributes work together to provide superior performance-per-byte ratios compared to single-attribute indexes.
Solution Approach 2:
The patent applies composite indexing by combining multiple attributes into multi-attribute indexes. Instead of selecting separate single-attribute indexes, the system creates composite indexes that integrate multiple attributes, achieving better performance with reduced total memory consumption by leveraging the synergistic effects of attribute combinations.
2Measurement precision
If the number of index candidates is increased to improve selection quality, then index configuration accuracy improves, but problem complexity increases
Solution Approach 1:
The patent segments the index selection problem into manageable components by evaluating attributes and their interactions systematically. The approach divides the complex selection task into iterative steps where multi-attribute combinations are evaluated based on their performance-per-byte contributions, making the NP-hard problem tractable through structured decomposition.
Solution Approach 2:
The patent introduces dynamics into the index selection process by iteratively refining the index configuration. The system dynamically adjusts the selection based on evaluated performance metrics, allowing the configuration to evolve toward optimality through repeated assessment and adjustment cycles rather than relying on static heuristics.
3Productivity
If index interactions are considered to improve selection accuracy, then performance optimization improves, but computational overhead increases
Solution Approach 1:
The patent performs preliminary evaluation of attribute interactions before final index selection. By pre-assessing how attributes interact and their combined performance-per-byte ratios, the system prepares the groundwork for efficient selection, avoiding costly computations during the actual selection phase and reducing overall computational overhead.
Solution Approach 2:
The patent replaces exhaustive mechanical evaluation of all possible index combinations with a smarter evaluation mechanism that focuses on multi-attribute interactions. The system substitutes brute-force computation with a targeted approach that assesses attribute combinations based on their interaction effects, significantly reducing computational time while maintaining selection accuracy.
Data Source
AI summary
The inventors have implemented in a columnar in-memory database and studied access patterns of a large production enterprise system. To obtain accurate cost estimates for a configuration, the inventors have used the what-if capabilities of modern query optimizers. What-if calls, however, are the major bottleneck for most index selection approaches. Hence, a major constraint is to limit the number of what-if optimizer calls. And even though the inventive approach does not limit the index candidate set, it decreases the number of what-if calls because in each iteration step the number of possible (index) extensions is comparably small which results in a limited number of what-if calls.