The Challenge:
When executing a query in Firebolt, it is possible that multiple AIs will qualify to be used (based on the query aggregations and the columns used in the SELECT, WHERE, and GROUP BY clauses). How does Firebolt decide which one to use?
Solution:
Firebolt employs a selection process to choose the AI when multiple options are available. Here’s how it works:
- Primary Index Alignment: Initially, Firebolt selects the AI that includes the most columns listed in the table's Primary Index (PI). The placement of columns in the PI matters; columns listed earlier are considered more significant.
- Filtering Column Match: If multiple AIs still qualify, the choice narrows to the AI that most closely matches the query’s filtering columns. The significance of these matches is also measured by their placement in the AI.
- Minimal Aggregations: When options still remain, the AI with the fewest aggregation functions is chosen.
- Default Choice: If a tie persists after all other criteria are applied, Firebolt selects the first AI in its unordered list. This final step is arbitrary but necessary to resolve the selection process.
Example:
Suppose you have a table with AIs built around the transaction_date and customer_id columns, which are also early in the Primary Index. Your query involves these columns and aggregates sales figures. Firebolt will prioritize an AI that starts with transaction_date over one that starts with customer_id if transaction_date appears first in the PI.
Conclusion:
Understanding how Firebolt selects an AI can help you design both your AIs and queries more effectively. By aligning your AIs with the most significant columns of your Primary Index and simplifying aggregations, you can enhance query performance and ensure that Firebolt chooses the most appropriate AI for your data operations.