Index Query Execution

Index queries in Cassandra employ distinct execution strategies compared to primary key queries. The distributed coordination model varies significantly between index implementations: legacy Secondary Index (2i) and SASI use a scatter-gather pattern, while Storage Attached Index (SAI) uses adaptive range reading.

---

Primary Key Query vs Index Query

Queries containing the complete partition key identify replicas deterministically via token computation:

Queries lacking a partition key constraint cannot use token-based routing. Each node indexes only the partitions it owns, so distributed coordination across nodes is required to satisfy an index query. The design of that coordination — and its performance characteristics — differs substantially between Cassandra's three index implementations.

---

Index Types

Secondary Index (2i)

Secondary Index is Cassandra's original indexing mechanism, available since the earliest versions. Each indexed column is backed by a hidden table, replicated independently from the base table. The coordinator broadcasts the query to one replica per token range (scatter-gather) and aggregates all partial results before returning to the client.

Because index data lives in a separate table, compaction cycles are independent of the base table, which can cause temporary divergence between index entries and base table rows. The scatter-gather model means query latency is always bounded by the slowest node in the cluster and scales poorly as the cluster grows.

See Secondary Index (2i) for execution detail.

SASI (SSTable Attached Secondary Index)

SASI, introduced in Cassandra 3.4 (CASSANDRA-6661), attaches index structures directly to SSTables. The index is created, compacted, and removed in lockstep with the base data, eliminating the divergence problem of 2i. Multi-predicate queries benefit from single-pass intersection within each SSTable rather than separate lookups joined at the coordinator.

The distributed coordination model is unchanged from 2i: SASI still uses scatter-gather across all token ranges. A global SASI query contacts every node in the cluster, and latency remains bounded by the slowest respondent.

See SASI for execution detail.

SAI (Storage Attached Index)

SAI, introduced in Cassandra 5.0 (CEP-7, CASSANDRA-16052), replaces the scatter-gather model with adaptive range reading. The coordinator estimates a concurrency factor from local data statistics and the query LIMIT, dispatches requests to a bounded subset of token ranges, and iterates in additional rounds only if the limit is not yet satisfied. Queries that can be answered from an early subset of ranges never contact the full cluster.

SAI also propagates index status via gossip, allowing the coordinator to route around nodes whose index is not queryable. Multi-predicate queries are handled by the Token Flow framework, which merges index streams using Boolean logic rather than performing separate scans.

See SAI for execution detail.

---

Query Scope

Global Queries

Queries without a partition key constraint require coordination across all token ranges:

-- Global: all token ranges involved
SELECT * FROM users WHERE city = 'NYC';
SELECT * FROM events WHERE level = 'ERROR' LIMIT 1000;

Index Type	Execution Model
2i / SASI	Scatter to all nodes, gather all results
SAI	Adaptive range reading with concurrency factor

Partition-Restricted Queries

Including the partition key in a secondary index query limits node contact to the replica set for that partition:

-- Partition-restricted: RF nodes only
SELECT * FROM users WHERE user_id = ? AND city = 'NYC';
SELECT * FROM events WHERE device_id = ? AND level = 'ERROR';

Query Type	Nodes Contacted	Latency Characteristics	Cluster Scalability
Global (2i/SASI)	All token ranges	Bounded by slowest node	Degrades linearly
Global (SAI)	Adaptive subset	Bounded by concurrency factor	Improved via adaptation
Partition-restricted	RF nodes	Predictable, bounded	Constant

---

Performance Considerations

Factor	2i / SASI	SAI
Coordinator load	High (all results buffered)	Reduced (streaming, bounded)
Network utilization	Proportional to cluster size	Adaptive to result density
Memory pressure	Unbounded result accumulation	Bounded via concurrency factor
Index build visibility	Limited	Gossip-based status propagation
Failed index handling	Manual detection required	Automatic coordinator filtering

---

Query Design Guidelines

Pattern	Rationale
Include partition key when possible	Restricts query to RF nodes
Apply LIMIT clause	Enables SAI early termination; reduces scatter-gather result set
Prefer high-selectivity predicates	Reduces post-filtering overhead
Combine multiple SAI-indexed predicates	Leverages Token Flow intersection

Optimal: partition-restricted with index

SELECT * FROM orders
WHERE customer_id = ?      -- partition key constraint
  AND status = 'pending';  -- SAI index predicate

Acceptable: global query with limit

SELECT * FROM orders
WHERE status = 'pending'   -- SAI index predicate
  AND created_at > ?       -- SAI range predicate
LIMIT 100;                 -- enables early termination

Suboptimal: unbounded global query

SELECT * FROM orders
WHERE status = 'pending';  -- unbounded result set, all nodes contacted

---

Monitoring

# SAI-specific metrics
org.apache.cassandra.metrics:type=StorageAttachedIndex,name=*

# Per-table read latency
org.apache.cassandra.metrics:type=Table,keyspace=*,scope=*,name=ReadLatency

# Coordinator request metrics
org.apache.cassandra.metrics:type=ClientRequest,scope=Read,name=Latency

Symptom	Probable Cause	Remediation
Elevated P99 latency	Tail latency in scatter-gather	Migrate to SAI, add partition key
Query timeouts	Unbounded result sets	Apply LIMIT clause
Coordinator GC pressure	Result set accumulation	Pagination, query redesign
Index query failures	Non-queryable index state	Check gossip status, rebuild index

---

Related Documentation

• Secondary Index (2i) - Scatter-gather execution, hidden table storage
• SASI - Scatter-gather execution, SSTable-attached storage
• SAI - Adaptive range reading, Token Flow framework
• Index Overview - Index type selection criteria
• Consistency - Consistency level impact on index queries
• Partitioning - Token distribution and routing