Link Search Menu Expand Document

Using Firebolt indexes

Firebolt offers unique technologies that accelerate query performance. Indexes are among the most important. Although indexes are a familiar concept in databases and SQL engines, Firebolt indexes are different because they work together with the Firebolt File Format (F3). Firebolt indexes allow your queries to scan very small ranges of cached data rather than scanning much larger ranges as other systems do.

Efficient indexes not only accelerate query performance, they allow you to use compute engines more efficiently and reduce cost.

Firebolt offers three types of indexes:

You can combine indexes

Each table has only one primary index, which is mandatory for fact tables and optional for dimension tables. You can have as many aggregating indexes and join indexes as your workloads demand. Indexes are highly compressed. The cost of storing them is small when compared to the potential savings in engine runtime, number of nodes, and the engine spec.

Firebolt maintains indexes automatically

After you define an index for a table, Firebolt updates the index on the general purpose engine that you use to ingest data (the engine that runs the INSERT INTO statement). You don’t need to manually maintain or recreate indexes as you incrementally ingest data.

Test and validate indexes before deploying to production

We recommend that you experiment with index configurations before you go to production. Create indexes on tables, create analytics queries that are comparable to your production queries, run the queries, and then compare performance results using query statistics, query history, and explain plans.

Additional resources