Indexes
Composite Indexes
A composite index covers two or more fields simultaneously. When a query filters on all of those fields together in an AND block using equality operators, the engine resolves the entire filter in a single index lookup instead of intersecting two separate single-field index results.
Composite vs. Single-Field Indexes
Two separate single-field indexes on plan and country can already help — the engine uses the more selective one and then filters the rest in memory. But a composite index on [plan, country] is faster when both fields appear together frequently, because the index itself encodes the combination: the lookup key is the tuple (plan, country) and the result set is already the final intersection.
Use a composite index when the same pair (or triple) of fields appears together in the AND clause of the majority of your hot queries.
Creating a Composite Index
Use the fields array (plural) instead of the field string (singular) used for single-field indexes. The order of fields in the array matters — it must match the order the engine sees in the filter (see Activation Rules below).
POST /v1/agents/users/indexes
Authorization: Bearer spx_root_YOUR_ROOT_KEY
Content-Type: application/json
{
"name": "idx_plan_country",
"fields": ["plan", "country"]
}{
"name": "idx_plan_country",
"fields": ["plan", "country"],
"type": "composite",
"status": "building"
}Like single-field indexes, composite indexes build asynchronously. Check status with GET /v1/agents/users/indexes.
Querying with a Composite Index
The composite index activates automatically when the query filter matches all indexed fields with equality operators in an AND block.
POST /v1/agents/users/query
Authorization: Bearer spx_root_YOUR_ROOT_KEY
Content-Type: application/json
{
"type": "scan",
"limit": 50,
"filter": {
"AND": [
{ "field": "plan", "op": "eq", "value": "pro" },
{ "field": "country", "op": "eq", "value": "US" }
]
}
}Activation Rules
The engine applies strict rules before activating a composite index. All three conditions must be true:
| Rule | Detail |
|---|---|
| All fields present | Every field listed in the index fields array must appear in the filter. Missing even one field disqualifies the composite index. |
| AND grouping | The field filters must be inside an AND block at the same level. An OR block will not activate the index. |
| eq operator only | Every field in the composite index must use op: "eq". Using in, starts_with, or any other operator on a composite field falls back to single-field index or full scan. |
Field Order
The order of fields in the fields array during index creation determines the index key structure. The engine matches filter fields to the index in declaration order. For a two-field index on ["plan", "country"], the filter must present plan before country in the AND array for the engine to recognize the match. If you send them in reversed order, the engine falls back to a single-field index (if one exists) or a full scan.
["plan", "country"], always write the filter with plan first and country second in the AND array. A simple way to enforce this: put the more selective field first in both the index declaration and your query templates.Partial Match Behavior
If only a subset of the composite index fields appears in the filter, the engine does not use the composite index. Instead it falls back to the best available single-field index for one of the present fields, or to a full scan if no single-field index exists.
{
"type": "scan",
"limit": 50,
"filter": {
"field": "plan",
"op": "eq",
"value": "pro"
}
}
// idx_plan_country is NOT used here.
// If idx_plan (single-field) exists, that is used instead.This means it is safe — and often useful — to have both a composite index on ["plan", "country"] and a single-field index on plan at the same time. Queries filtering on both fields use the composite; queries filtering on plan alone use the single-field index.
Verifying with Explain
Run an explain query to confirm the composite index is being selected before sending to production.
POST /v1/agents/users/query
Authorization: Bearer spx_root_YOUR_ROOT_KEY
Content-Type: application/json
{
"type": "explain",
"filter": {
"AND": [
{ "field": "plan", "op": "eq", "value": "pro" },
{ "field": "country", "op": "eq", "value": "US" }
]
}
}{
"explain": {
"strategy": "index_scan",
"index": "idx_plan_country",
"hint": "Composite index on ['plan','country'] — expected O(k) where k = matching records"
}
}If you see strategy: "full_scan" or the engine picks a single-field index instead, check that your filter uses the correct field order and that all fields carry op: "eq".
Deleting a Composite Index
DELETE /v1/agents/users/indexes/idx_plan_country Authorization: Bearer spx_root_YOUR_ROOT_KEY
{
"deleted": "idx_plan_country"
}