Quick Tip: Warming Up Aggregating Indexes in Firebolt

1

The Challenge:

Aggregating Indexes are a powerful tool for enhancing query performance through the pre-aggregation of data.
To ensure that queries utilizing these indexes operate at peak efficiency, you can warm up the aggregating indexes prior to query execution.
This process prepares the database for optimal performance in query execution.

Solution:

Warming up an aggregating index involves executing the following query:

SELECT  CHECKSUM (*)FROM  (    SELECT      {AI_Definition}    FROM      {Original_Table}    GROUP BY      ALL  ) t;

Executing this query ensures that the aggregating index is immediately operational, optimizing query performance from the first use.

Example:

Consider a fact table orders with this schema:

CREATE FACT TABLE orders(    order_id TEXT ,    customer_id TEXT,    product_id TEXT,    quantity INT,    order_price FLOAT,    order_timestamp TIMESTAMP,    payment_method TEXT,    shipping_method TEXT);

And an aggregating index defined as:

CREATE AGGREGATING INDEX customer_ship_qty ON orders (customer_id, shipping_method, SUM(quantity));

To warm up this aggregating index, execute the following query:

SELECT  CHECKSUM (*)FROM  (    SELECT      customer_id,      shipping_method,      SUM(quantity) AS sum_qty    FROM      orders    GROUP BY      ALL  ) t;

This query calculates a checksum over the results delivered by the aggregating index, loading the aggregating index into cache without producing a large volume of output.

Conclusion:

Warming up your aggregating indexes is a simple yet effective strategy to enhance query performance in Firebolt.
By executing a carefully crafted query, you can ensure that your indexes are pre-loaded and ready to serve data quickly, thus optimizing your database's response times for critical operations.