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:
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.
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.
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.
- For in-depth information about index internals, see the Firebolt Cloud Data Warehouse Whitepaper sections on Indexes and Query Optimization.
- To see examples of the application of indexes, see the Firebolt blog post, Firebolt indexes in action.