Optimize Aggregating Queries

Queries that include aggregation functions can require significant CPU and memory to process, as the engine needs to retrieve the rows and then perform the aggregations over the results. There are a couple of strategies that can be used to improve performance and efficiency in Firebolt.

Deep filters

Reducing the number of rows to be selected/aggregated will always result in better performance. The How to optimize your queries using deep filters article in the Help Center describes methods for reducing the size of the result set.

Aggregating indexes

Aggregating indexes can provide significant improvements to aggregating queries. Once defined, they are updated as data is added/updated/deleted from the table, and a table can have multiple aggregating indexes defined at the same time. They are used by query analyzer instead of scanning the table to calculate results, like a materialized view but integrated with F3 format.

Allocate more memory/CPU

Calculating aggregations is CPU intensive. Adding more nodes of the existing size or increasing the size of the existing nodes can result in aggregation performance improvement.