Indexes

Database indexes are essential for optimizing query performance. This document explains how to add indexes to tables created by Spice for local data acceleration.

Example Spicepod:

datasets:
  - from: spice.ai/eth.recent_blocks
    name: eth.recent_blocks
    acceleration:
      enabled: true
      engine: sqlite
      indexes:
        number: enabled # Index the `number` column
        '(hash, timestamp)': unique # Add a unique index with a multicolumn key comprised of the `hash` and `timestamp` columns

Column References

Column references can be used to specify which columns to index. The column reference can be a single column name or a multicolumn key. The column reference must be enclosed in parentheses if it is a multicolumn key.

Examples

• number: Index the number column
• (hash, timestamp): Index the hash and timestamp columns

Index Types

There are two types of indexes that can be specified in a Spicepod:

• enabled: Creates a standard index on the specified column(s).
  • Similar to specifying CREATE INDEX my_index ON my_table (my_column).
• unique: Creates a unique index on the specified column(s). See Constraints for more information on working with unique constraints on locally accelerated tables.
  • Similar to specifying CREATE UNIQUE INDEX my_index ON my_table (my_column).

Limitations

Indexes are not supported for the in-memory Arrow or Cayenne acceleration engines. Use DuckDB, SQLite, or PostgreSQL as the acceleration engine to enable indexing.