:::warning[Experimental] Hash index is an experimental feature available in Spice v1.11.0-rc.2 and later. :::
The hash index is an optional, high-performance indexing feature for Arrow-accelerated datasets. It provides O(1) point lookups on primary key and secondary index columns, dramatically improving query performance for equality predicates.
Hash indexing activates automatically on Arrow-accelerated datasets when a primary_key or secondary index (opens in a new tab) is configured. No additional parameter is required.
The hash index activates whenever:
engine is arrow or partitioned_arrow,acceleration.enabled is true,indexes is set, or primary_key is set with a non-caching refresh_mode.Secondary indexes can be added on non-primary-key columns to accelerate equality lookups on those columns. Define them using the indexes (opens in a new tab) field in the acceleration configuration:
Index types:
unique — Enforces uniqueness and enables O(1) indexed lookups.enabled — Permits duplicates. The index is built and maintained but does not currently accelerate queries (queries fall back to a full scan).Compound secondary indexes can be defined with a multicolumn key in parentheses, e.g. '(col1, col2)': unique, but are not yet used for query optimization.
:::note
Only single-column unique secondary indexes currently accelerate queries. Non-unique and compound secondary indexes are maintained for future use.
:::
| Parameter | Type | Required | Default | Description |
|---|---|---|---|---|
primary_key | string or list | Yes (unless indexes is set) | None | Column(s) for the primary key index |
indexes | YAML map | No | None | Secondary indexes (see indexes (opens in a new tab)) |
:::note hash_index parameter is ignored
The legacy hash_index: enabled parameter is accepted but no longer activates indexing on its own. When set, the runtime logs a warning and falls back to the automatic rules above. Remove hash_index from params to clear the warning.
:::
The hash index supports the following primary key column types:
Int8, Int16, Int32, Int64UInt8, UInt16, UInt32, UInt64Utf8, LargeUtf8Binary, LargeBinaryThe hash index automatically accelerates queries with equality predicates on indexed columns.
When a primary key lookup is combined with additional filters (e.g. WHERE id = 123 AND status = 'active'), the index is used for the primary key lookup and the remaining filters are applied afterward by DataFusion.
There is no minimum row count. Whenever the activation rules (opens in a new tab) are met, the index is built for the dataset regardless of size — including for an empty table, which is indexed at load and rebuilt as rows arrive. The row count is used only to pre-size the hash table and bloom filter, not to decide whether to index at all, so budget the memory (opens in a new tab) below for every indexed dataset.
The built-in bloom filter provides:
| Component | Memory per Entry |
|---|---|
| Hash slot | 16 bytes (8-byte hash + 8-byte location) |
| Bloom filter | ~1.25 bytes |
| Total | ~17.25 bytes per indexed row |
For a 10 million row dataset:
The index uses 256 independent shards to minimize lock contention:
Shard Selection: Uses XOR-folded hash bits: ((hash >> 56) ^ (hash >> 48) ^ hash) & 0xFF
Each indexed key maps to a RowLocation:
Uses XXH3_64 with a fixed seed (0x5370_6963_6541_4920 = "SpiceAI ") for:
engine: arrow and engine: partitioned_arrow accelerationunique secondary indexes accelerate queries. Non-unique and compound secondary indexes are built and maintained but do not yet optimize queriesCause: A direct point lookup was issued against an indexed table that has no primary key index — primary_key is not set, so only the configured secondary indexes exist.
Solution: Set primary_key on the acceleration to build the primary key index. Dataset size is not a factor: the index is built at any row count once the activation rules (opens in a new tab) are met.
Cause: hash_index: enabled is set in params but no longer activates indexing on its own.
Solution: Remove hash_index from params. Hash indexing activates automatically when primary_key or indexes is configured on an Arrow-accelerated dataset (see Configuration (opens in a new tab)).
primary_key being setCause: refresh_mode: caching drops the primary-key constraint before the table is created, so a primary_key on its own activates nothing. It does not disable hash indexing as such — a secondary indexes entry is read from a different place and survives the drop.
Solution: Either configure indexes (opens in a new tab) on the looked-up columns, which activates hash indexing under caching as it does under any other mode, or use a non-caching refresh_mode (e.g. full, append, changes) if you need the primary-key index specifically. Note that under caching the primary-key index stays absent even when primary_key and indexes are both set — only the secondary index is built.
Cause: Index consumes ~17 bytes per row.
Solution:
primary_key for datasets where point lookups are rare (hash indexing stops being applied)